Databases

Run the following command:

alter table TABLE_NAME alter column COLUMN_NAME type character varying(120);

This will extend the character varying column field size to 120.
Remember to change TABLE_NAME to the relevant table name and COLUMN_NAME to the relevant column name.

If you need to copy data from one table (table_a) into another table (table_b) using pgsql. There are 2 methods on how to do this. Method 1 NOTE: If you are using this method table_b should not yet exist. If you want an exact duplicate without indexing or relying on objects use this. SELECT * INTO table_b FROM table_a Method 2 NOTE: This method will work if table_b already exist. This method uses...

How to copy a columns data from one table into a column in another table using PostgreSQL? Eg. name columns data from the users table into namecolumn in the new_users table. Execute the query below with different column names and table names and all the data for a specific column will be copied over to the selected column in another table. INSERT INTO n_owners(owner_name) SELECT owner_name FROM...

Run the following query: SELECT version(); The result will look as below:                                                version ————————————————————————————————— PostgreSQL 8.3.7 on i486-pc-linux-gnu...

How to create a backup of a PostgreSQL database to a sql file? This sql file then can be used to restore the database on a later stage. Follow the steps below in order to create a backup Open a Terminal Window if not yet in command line Login as user postgres. eg. sudo su postgres  Type in the following command pg_dump db_name -CdiOv > /tmp/db_name_backup.sql pg_dump = Utility to backup a...

Use the DISTINCT ON keyword in your query
To do a case-insensitive search do the following query
SELECT DISTINCT ON(upper(surname)) surname FROM employees ORDER BY upper(surname)
This will return non-duplicate surnames.

Type in the following command in postgres using the command line:
copy products from ‘/home/user/products.csv’ using delimiters ‘,’

products = (Database Table)
‘/home/user/products.csv‘ = (Location of the csv file)
You need to have insert privileges on the table you need to insert the data to

How to export data to csv file from postgres database?

Type in the following command in postgres using the command line: copy products to ‘/home/user/products.csv’ delimiters ‘,’ CSV HEADER products = (Database Table) ‘/home/user/products.csv‘ = (Location where to save the csv file) You need to have write permissions to write the file to the filesystem with the postgres user CSV HEADER – This is the feature...

Type in the following command in postgres using the command line:
copy products to ‘/home/user/products.csv’ delimiters ‘,’

products = (Database Table)
‘/home/user/products.csv‘ = (Location where to save the csv file)
You need to have write permissions to write the file to the filesystem with the postgres user

How to import a CSV file into a postgresql?

Editing the pg_hba.conf file on a postgresql database server so that a specific (other) server will be able to connect to the postgresql database server. Example: Say I have a server where all of my postgresql databases are running and I have an application on another server that needs to access a database from the database server. To make this possible I need to edit the pg_hba.conf file to...

Use the following statement

ALTER TABLE my_table DROP COLUMN my_column;

Replace my_table with the your table name and my_column with the column that you need to drop/remove.

Type in the following command in the CLI (Command Line Interface)

psql -l

This command will list all of the databases available

In my case I had a .sql file with hundreds of insert statements and I wanted to run all of them, I was left with two options namely manually entering every command via copy and paste or executing the file. I went for the second option and it worked. I used Postgres 8.4 on the command line on a Ubuntu machine. Answer: Follow these steps Connect to the relevant database eg. sudo -u postgres psql...

Log into the PostgreSQL interface on the command line, you should see a similar screen like the one below Welcome to psql 8.1.21, the PostgreSQL interactive terminal.   Type:  \copyright for distribution terms        \h for help with SQL commands        \? for help with psql commands        \g or terminate with semicolon to execute query        \q to quit   database=# Type the following command...

Run the following command

alter table TABLE_NAME alter column COLUMN_NAME type character varying(120);

This will extend the character varying column field size to 120.
Remember to change TABLE_NAME to the relevant table name and COLUMN_NAME to the relevant column name.

Use the currval() sequence function

SELECT currval('my_sequence');

Replace my_sequence with your sequence, remember currval returns the latest value that was obtained from your sequence in your session.

This can be accomplished by using the dropdb and createdb command line wrappers The syntax to create a new database on the command line is createdb [ options ] dbname   Example: createdb -O user  new_database - E UTF8 This will create a new database called new_database and assign the user user to it and use the encoding UTF8   To drop a database on the command line the syntax dropdb [...

To do this login to the command line and switch to the main Postgres user. eg su postgres The type the psql -l command this will produce a list of databases with there encodings                   List of databases             Name          |   Owner    | Encoding     ------------------------+------------+-----------    DB1                    | postgres   | UTF8    DB2                    |...

Open a new Terminal Window and then install the latest PostgreSQL Server sudo yum install postgresql-server Now we need to initialize the Database Cluster sudo service postgresql initdb Now start the server service postgresql start Now login with the postgres user sudo su -l postgres This user automatically gets created when the PostgresSQL server gets installed Now we create a new PostgreSQL...