PHPRO.ORG

This table provides a simple ready reference to some common commands used in PostgreSQL. Ideal for those more accustomed to MySQL or other databases.

Connect to postresqlpsql or psql db_name
Create a userCREATE USER kevin WITH PASSWORD 'my_password';
Create a user from command linecreateuser kevin
Remove a user from command linedropuser kevin
Select database\c db_name
Show databasesselect datname from pg_database;
List databases\l
Show Schemas\dn
Show tables\d
Show tables in schema\dt my_schema.*
Show all sequences\ds
Show all views\dv
Show table definition\d table_name
Show privilieges\dp
Show functions\df
Show data types\dT
Describe table\d table_name
Create databaseCreate database db_name
Create database from command linecreatedb dbname -U user-name -W
Create schemaCREATE SCHEMA my_schema
Drop databaseDROP DATABASE dbname;
Drop database from command linedropdb dbname
Drop SchemaDROP SCHEMA my_schema CASCADE
Backup ALL databasespg_dumpall > backup.sql
Backup databasepg_dump -U username dbname > outfile.sql
Backup Schema Onlypg_dump --schema-only -U username dbname > outfile.sql
Rename databaseALTER DATABASE name RENAME TO newname
Auto Incrementmy_id SERIAL PRIMARY KEY
GRANT update prviligesGRANT UPDATE ON table-name to kevin
GRANT select prviligesGRANT SELECT ON table-name to kevin
GRANT insert prviligesGRANT INSERT ON table-name to kevin
GRANT delete prviligesGRANT DELETE ON table-name to kevin
GRANT rule prviligesGRANT RULE ON table-name to kevin
Add Foreign KeyALTER TABLE my_table ADD FOREIGN KEY(some_id) REFERENCES other_table(some_id) ON DELETE CASCADE
Vacuumvacuumdb --verbose --analyze --all
Import a dump filepsql -U username dbname < dbname.sql
Get VersionSELECT version();
Quit psql\q ( For aka- who could not work it out )
Dump Functions pg_dump -U postgres -s dbname | awk '/CREATE FUNCTION/,/LANGUAGE/ { print $0;}' > func.txt
Rename ColumnALTER TABLE my_table RENAME COLUMN old_name TO new_name
Rename TableALTER TABLE my_table RENAME TO new_table_name
Change Columns TypeALTER TABLE my_table ALTER COLUMN my_col TYPE numeric(10,2)
Change Default ValueALTER TABLE my_table ALTER COLUMN my_col SET DEFAULT 22
Change Database OwnerALTER DATABASE db_name OWNER TO new_owner_name
Change PasswordALTER USER postgres WITH PASSWORD 'new_password'
Rename FieldALTER TABLE my_table RENAME old_name TO new_name;
Drop TriggerDROP TRIGGER trigger _name ON table_name
Reset SequenceSELECT setval('sequence_name', 0)
Read From FileCOPY my_table FROM'/path/to/file'
Write To FileCOPY (SELECT field1, field2 FROM my_table) TO '/path/to/file.txt';