PGSQL Cheat Sheet

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 postresql	psql or psql db_name
Create a user	CREATE USER kevin WITH PASSWORD 'my_password';
Create a user from command line	createuser kevin
Remove a user from command line	dropuser kevin
Select database	\c db_name
Show databases	select 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 database	Create database db_name
Create database from command line	createdb dbname -U user-name -W
Create schema	CREATE SCHEMA my_schema
Drop database	DROP DATABASE dbname;
Drop database from command line	dropdb dbname
Drop Schema	DROP SCHEMA my_schema CASCADE
Backup ALL databases	pg_dumpall > backup.sql
Backup database	pg_dump -U username dbname > outfile.sql
Backup Schema Only	pg_dump --schema-only -U username dbname > outfile.sql
Rename database	ALTER DATABASE name RENAME TO newname
Auto Increment	my_id SERIAL PRIMARY KEY
GRANT update prviliges	GRANT UPDATE ON table-name to kevin
GRANT select prviliges	GRANT SELECT ON table-name to kevin
GRANT insert prviliges	GRANT INSERT ON table-name to kevin
GRANT delete prviliges	GRANT DELETE ON table-name to kevin
GRANT rule prviliges	GRANT RULE ON table-name to kevin
Add Foreign Key	ALTER TABLE my_table ADD FOREIGN KEY(some_id) REFERENCES other_table(some_id) ON DELETE CASCADE
Vacuum	vacuumdb --verbose --analyze --all
Import a dump file	psql -U username dbname < dbname.sql
Get Version	SELECT 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 Column	ALTER TABLE my_table RENAME COLUMN old_name TO new_name
Rename Table	ALTER TABLE my_table RENAME TO new_table_name
Change Columns Type	ALTER TABLE my_table ALTER COLUMN my_col TYPE numeric(10,2)
Change Default Value	ALTER TABLE my_table ALTER COLUMN my_col SET DEFAULT 22
Change Database Owner	ALTER DATABASE db_name OWNER TO new_owner_name
Change Password	ALTER USER postgres WITH PASSWORD 'new_password'
Rename Field	ALTER TABLE my_table RENAME old_name TO new_name;
Drop Trigger	DROP TRIGGER trigger _name ON table_name
Reset Sequence	SELECT setval('sequence_name', 0)
Read From File	COPY my_table FROM'/path/to/file'
Write To File	COPY (SELECT field1, field2 FROM my_table) TO '/path/to/file.txt';