Commands

Quit

\q.

Login                -U <user>

Password        -P <password>

Connect to db        \c <db>

use <db_name>

Import

1. Create Table.

2. \copy <Table_Name> from <path> Delimiter ‘<delimiter-char>’ [CSV] [Header]

Console.

Null \N or.

NULL as <‘null string’>

For example, csv WITH NULL AS 'null'.

Export                \copy (SQL_Query) to <path> delimiter ‘<delimiter-char>’

List.

db

\list.

tables

\dt.

Relations

\d.

Insert {\n}

E’ABC\nXYZ’.

Create DB        Create database <name>

Describe        \d+ <table-name>

Select column_name from INFORMATION_SCHEMA.COLUMNS where table_name = 'table';.

Index

Creates index on Auth_RoleRules (Role);.

Must be standalone statement.

Truncate        truncate <table>

Drop

Drop dropdb <DB-NAME>

Drop all.

Drop schema public cascade;.

Creates schema public;.

Dump database

Dump database pg_dump -h<IP> -U<User> <DB> > <Path>

Push database

Push database psql <DB> < <Path>

Push database psql -f <Path> <User>

Push database \i <Path>

Password change

Password change alter user <user> UNENCRYPTED password '<password>';

Add user

Add user Create role <name> WITH PASSWORD '<password>’

INHERIT attribute - for Role and Users (like the SQL standard).

http://www.postgresql.org/docs/8.1/static/sql-createrole.html

Set Null

Set Null set <Column> = NULL

Delete duplicates

Delete duplicates DELETE FROM <Table> WHERE ctid NOT IN

Delete duplicates (SELECT max(ctid) FROM <Table>  GROUP BY <Table>.*) ;