Cheatography
https://cheatography.com
This is a postgresql cheat sheet.
Database
\l |
list databases |
\l+ |
list databases with more details |
\c <db_name> |
Connects to a given database |
\h CREATE DATABASE |
gives help on creating a database |
CREATE DATABASE mytest; |
Creates a database called mytest |
Table
\d <TABLE_NAME> |
Shows information on the given table_name |
\d+ <TABLE_NAME> |
Gives more detailed info on a given table |
\dt |
List tables from a given schema |
\dt . |
List tables from all schemas |
\dt <name-of-schema>.* |
List tables for a given schema |
\copy (SELECT * FROM <my_table>) TO 'file_path_and_name.csv' WITH CSV |
Copies data from a table to a CSV file |
ANALYZE [__table__] |
Shows statistics about a table |
SELECT * FROM pg_indexes WHERE tablename='__table_name__' AND schemaname='__schema_name__'; |
Show indexes on a table |
Comment on table employee is 'Stores employee records'; |
Add a comment about a table |
Comment on column employee.ssn is 'Employee Social Security Number'; |
Add a comment about a column in a table |
|
|
Command line
psql [OPTION]... [DBNAME [USERNAME]] |
-d, --dbname=DBNAME |
database name to connect to (default: "family") |
-f, --file=FILENAME |
execute commands from file, then exit |
-c, --command=COMMAND |
run only single command (SQL or internal) and exit |
-h, --host=HOSTNAME |
database server host |
-p, --port=PORT |
database server port (default: "5432") |
-U, --username=USERNAME |
database user name (default: "family") |
-H, --html |
HTML table output mode |
Configuration
show all |
Display configuration parameters |
select * from pg_settings; |
Display configuration parameters using sql |
show config_file; |
Show location of config file |
User Commands
\password <username> |
Change the password for given username |
select * from pg_user; |
Show all users |
DROP USER <username> |
Removes a given user |
CREATE USER name [ [ WITH ] option [ ... ] ] |
Cre |
Backup and Restore
$ pg_dump mydb > mydb.bak.sql
// Create a backup for a database “mydb” in plain-text SQL Script file (mydb.bak.sql) (pg_dump)
$ pg_dump -c -C -F p -f mydb.bak.sql mydb
// Creates a backup for a database “mydb” in plain text format with drop & create database commands included in output file mydb.bak.sql (pg_dump)
psql -U username -f filename.sql
// restores from a set of SQL commands as given above |
|
Created By
Metadata
Comments
No comments yet. Add yours below!
Add a Comment
Related Cheat Sheets