psql Techniques

Some techniques with using psql.

Supplying Password Non-interactively

To supply a password in a non-interactive way, use the PGPASSWORD environment variable:

PGPASSWORD=xxx psql -U username database

Source: Stack Overflow

Describing Customer Types

These are the enum types created using CREATE TYPE my_enum AS ENUM constructs. To get the list of those types, use the \dT meta-command:

postgres=> \dT
              List of data types
 Schema |      Name       | Description 
--------+-----------------+-------------
 public | my_enum         | 
 public | another_enum    | 
 public | even_more_enums | 
(3 rows)

To actually list the enum elements, use \dT+:

postgres=> \dT+ my_enum
                                            List of data types
 Schema |   Name  | Internal name | Size | Elements |      Owner       | Access privileges | Description 
--------+---------+---------------+------+----------+------------------+-------------------+-------------
 public | my_enum | my_enum       | 4    | alpha   +| hCFhngwITRzKShyk |                   | 
        |         |               |      | bravo   +|                  |                   | 
        |         |               |      | charlie +|                  |                   | 
        |         |               |      | delta   +|                  |                   | 
        |         |               |      | echo     |                  |                   | 

(I have no idea what the + after the elements are)

Displaying Records Vertically

MySQL has the \g option, which allows you to display rows “vertically” as in key/value pairs, rather than a table. The equivalent for psql is \x.

postgres=> \x
Expanded display is on
postgres=> SELECT 2 as x;
-[ RECORD 1 ]
x | 2

The only downside of this is that it’s modal, unlike MySQL which allows you to apply this to a single query. To turn it off, just type \x again.

Source: Server Fault

## Exporting A Query As A CSV File

If you’re using PostgreSQL, and you want to export the results of a query as a CSV file, probably the simplest way to do that is to connect to the database using psql and use the \copy command:

\copy (select * from table) to 'out.csv' with (format csv, header);

This will write the results of a query to a CSV file, with column headers, on your local machine.

It works a lot like the COPY statement, except that COPY without the backslash stores the CSV file on the database server. I rarely want that myself, so it’s good to see psql support this.

Source: this Stack Overflow answer.