Basic Postgres Commands

Sun, Jan 21, 2024 2-minute read

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 backup.

    • Restore a backup: pg_restore -d [database_name] backup.sql

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.