PostgreSQL guide

Connection Commands

  1. psql

    • Definition: The PostgreSQL interactive terminal for connecting to a PostgreSQL database.

    • Usage:

      1

      psql -h hostname -p port -U username -d database

  2. \connect (or \c)

    • Definition: Connects to a new database and/or under a different user.

    • Usage:

      1

      \\c database_name [username]

  3. \password

    • Definition: Changes the password for the currently connected user.

    • Usage:

      1

      \\password [username]

  4. \conninfo

    • Definition: Displays information about the current database connection.

    • Usage:

      1

      \\conninfo

  5. \encoding

    • Definition: Shows or sets the client encoding.

    • Usage:

      1

      \\encoding [encoding_name]

  6. \quit (or \q)

    • Definition: Exits the psql program.

    • Usage:

      1

      \\q

Database Management

  1. CREATE DATABASE

    • Definition: Creates a new PostgreSQL database.

    • Usage:

      1
2
3
4
5
6
7
8

      CREATE DATABASE database_name
WITH
  OWNER = role_name
  ENCODING = 'UTF8'
  LC_COLLATE = 'en_US.UTF-8'
  LC_CTYPE = 'en_US.UTF-8'
  TEMPLATE = template0
  CONNECTION LIMIT = -1;

  2. DROP DATABASE

    • Definition: Removes a database from the PostgreSQL server.

    • Usage:

      1

      DROP DATABASE [IF EXISTS] database_name;

  3. ALTER DATABASE

    • Definition: Changes the attributes of a database.

    • Usage:

      1
2
3

      ALTER DATABASE database_name RENAME TO new_name;
ALTER DATABASE database_name OWNER TO new_owner;
ALTER DATABASE database_name SET parameter TO value;

  4. \list (or \l)

    • Definition: Lists all databases in the PostgreSQL server.

    • Usage:

      1

      \\l

  5. \db+

    • Definition: Lists all tablespaces with additional information.

    • Usage:

      1

      \\db+

  6. CREATE TABLESPACE

    • Definition: Defines a new tablespace for the database cluster.

    • Usage:

      1
2
3

      CREATE TABLESPACE tablespace_name
OWNER role_name
LOCATION 'directory_path';

  7. pg_dump

    • Definition: Extracts a PostgreSQL database into a script file or other archive file.

    • Usage:

      1

      pg_dump -h hostname -p port -U username -F format -f output_file database_name

  8. pg_restore

    • Definition: Restores a PostgreSQL database from an archive file created by pg_dump.

    • Usage:

      1

      pg_restore -h hostname -p port -U username -d database_name archive_file

  9. \copy

    • Definition: Performs a frontend (client) copy operation to import or export data.

    • Usage:

      1
2

      \\copy table_name FROM 'filename' WITH (FORMAT csv, HEADER true);
\\copy table_name TO 'filename' WITH (FORMAT csv, HEADER true);

  10. VACUUM

    • Definition: Garbage collection and optional analysis of a database.

    • Usage:

      1

      VACUUM [FULL] [FREEZE] [VERBOSE] [ANALYZE] [table_name [(column_name [, ...])]]