Basic Commands
| Command | Description |
|---|---|
psql [OPTION]… [DBNAME [USERNAME]]psql -h <host> DBNAME USERNAME | Start the PostgreSQL command-line interface |
createdb dbname | Create a new database |
use dbname | Switch to use dbname |
dropdb dbname | Delete a database |
createuser username | Create a new user |
dropuser username | Delete a user |
pg_dump dbname > filename.sql | Backup a database |
psql -d dbname -f filename.sql | Restore a database |
Tables
| Command | Description |
|---|---|
CREATE TABLE tablename (column1 datatype1, column2 datatype2, ...) | Create a new table |
ALTER TABLE tablename ADD COLUMN column datatype | Add a new column to a table |
ALTER TABLE tablename DROP COLUMN column | Remove a column from a table |
DROP TABLE tablename | Delete a table |
SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE'; | List all tables in a database |
| SELECT table_name FROM information_schema.tables WHERE table_type=’BASE TABLE’; | List all tables in all schemas, you can remove the “table_schema=’public'” condition |
Data Manipulation
| Command | Description |
|---|---|
INSERT INTO tablename (column1, column2, ...) VALUES (value1, value2, ...) | Insert a new row into a table |
UPDATE tablename SET column = value WHERE condition | Update one or more rows in a table |
DELETE FROM tablename WHERE condition | Delete one or more rows from a table |
SELECT column1, column2, ... FROM tablename WHERE condition | Query data from a table |
Querying Data
| Command | Description |
|---|---|
SELECT * FROM tablename | Retrieve all rows and columns from a table |
SELECT column1, column2, ... FROM tablename | Retrieve specific columns from a table |
SELECT column1, column2, ... FROM tablename WHERE condition | Retrieve specific rows based on a condition |
SELECT column1, COUNT(*) FROM tablename GROUP BY column1 | Group rows based on a column and count the number of rows in each group |
SELECT column1, AVG(column2) FROM tablename GROUP BY column1 | Group rows based on a column and calculate the average value of another column in each group |
Joins
| Command | Description |
|---|---|
SELECT * FROM table1 JOIN table2 ON table1.column = table2.column | Join two tables based on a common column |
SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column | Left join two tables based on a common column |
SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column | Right join two tables based on a common column |
SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column | Full outer join two tables based on a common column |
Other Commands
| Command | Description |
| SELECT datname FROM pg_database; | list all the databases in a PostgreSQL server |
| GRANT SELECT ON mytable TO myuser; | grant SELECT privileges on a table called `mytable` to a user named `myuser` |
| GRANT SELECT, INSERT, UPDATE ON mytable TO myuser; | grant multiple privileges at once |
| GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO myuser; | grant privileges on all tables in a schema, you can use the ALL TABLES keyword: |
| GRANT USAGE ON SCHEMA myschema TO myuser; | grant privileges on a schema itself |
| SELECT table_name, grantee, privilege_type FROM information_schema.table_privileges WHERE table_schema = ‘myschema’; | to view the privileges granted on all tables in a schema |
Shortcut Commands
| Command | Description |
| \l | list all the databases in a PostgreSQL server |
| \du+ myuser | view the privileges granted to a specific user or role |
| \dp mytable | view the privileges granted on a specific object, such as a table or view |