Basic Postgres Commands

PostgreSQL commands can be broadly divided into command-line tools and SQL commands or functions you’d use within the psql interface. I’ll provide an overview of both:

Command-Line Tools #

1. psql: The PostgreSQL command-line client.

   • Connect to a database: psql -h [hostname] -U [username] -d [database_name]
   • List all databases: psql -l

2. createdb: Create a new PostgreSQL database.

   • Create a database: createdb [database_name]

3. dropdb: Remove a PostgreSQL database.

   • Drop a database: dropdb [database_name]

4. createuser: Create a new PostgreSQL role/user.

   • Create a new user: createuser [username]

5. dropuser: Remove a PostgreSQL role/user.

   • Drop a user: dropuser [username]

6. pg_dump: Create a backup of a PostgreSQL database.

   • Create a backup: pg_dump [database_name] > backup.sql

7. pg_restore: Restore a PostgreSQL database from a custom-format backup.

   • Create a custom-format backup: pg_dump -Fc [database_name] > backup.dump
   • Restore it: pg_restore -d [database_name] backup.dump
   • A plain .sql dump is restored with psql -d [database_name] -f backup.sql instead.

psql Commands #

When you’re inside the psql interface, you can use the following commands:

1. \l or \list: List all databases.

2. \c [database_name]: Connect to another database.

3. \dt: List all tables in the current database.

4. \du: List all users/roles.

5. \d [table_name]: Describe a table (shows columns, types, etc.).

6. \h: Get help on SQL commands.

7. \?: List all psql commands.

8. \x: Toggle expanded display. Useful when the rows have a lot of data and don’t fit the screen width.

9. \a: Toggle between aligned and unaligned output format.

10. \timing: Toggle timing of commands to see how long they take.

11. \i [filename]: Execute SQL commands from a file.

12. \o [filename]: Send query results to a file.

13. \q: Quit psql.

SQL Commands #

1. Data Retrieval

   • SELECT * FROM [table_name];: Select all records from a table.
   • SELECT column1, column2 FROM [table_name] WHERE condition;: Select specific columns based on a condition.

2. Data Modification

   • INSERT INTO [table_name] (column1, column2) VALUES (value1, value2);: Insert a new record.
   • UPDATE [table_name] SET column1 = value1 WHERE condition;: Update records.
   • DELETE FROM [table_name] WHERE condition;: Delete records.

3. Table Operations

   • CREATE TABLE [table_name] (...);: Create a new table.
   • DROP TABLE [table_name];: Delete a table.
   • ALTER TABLE [table_name] ...;: Modify an existing table (e.g., add/remove columns).

4. Database Operations

   • CREATE DATABASE [database_name];: Create a new database.
   • DROP DATABASE [database_name];: Delete a database.

5. Role/User Operations

   • CREATE USER [username] WITH PASSWORD '[password]';: Create a new user.
   • DROP USER [username];: Delete a user.
   • GRANT ... ON ... TO [username];: Grant privileges to a user.

This is just a basic overview. PostgreSQL has a vast set of functionalities, and the commands and functionalities you use will depend on your specific tasks and requirements.