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'; |
Support PHPRO.ORG
Search
PHPRO.ORG Poll
Warning: Participation in PHPRO.ORG polls may incorrectly lead you to believe your opinions matter.
RSS Feed
