psql non-select: how to remove formatting and show only certain columns?

Question

I'm looking to remove all line drawing characters from:

PGPASSWORD="..." psql -d postgres -h "1.2.3.4" -p 9432 -c 'show pool_nodes' -U owner
 node_id |   hostname    | port | status | lb_weight |  role   
---------+---------------+------+--------+-----------+---------
 0       | 10.20.30.40   | 5432 | 2      | 0.500000  | primary
 1       | 10.20.30.41   | 5432 | 2      | 0.500000  | standby
(2 rows)

Adding the -t option gets rid of the header and footer, but the vertical bars are still present:

PGPASSWORD="..." psql -t -d postgres -h "1.2.3.4" -p 9432 -c 'show pool_nodes' -U owner
 0       | 10.20.30.40   | 5432 | 2      | 0.500000  | primary
 1       | 10.20.30.41   | 5432 | 2      | 0.500000  | standby

Note that this question is specific to show pool_nodes and other similar non-select SQL statements.

My present workaround is to involve the Linux cut command:

<previous command> | cut -d '|' -f 4

The question has two parts:

1. How using psql only can the vertical bars above be removed?
2. How using psql only can only a specific column (for example, status) or columns be shown? For example, the result might be just two lines, each showing the number 2.

I'm using psql version psql (PostgreSQL) 9.2.18 on a CentOS 7 server.

Answer 1

For scripting psql use psql -qAtX:

• quiet
• tuples-only
• unAligned output
• do not read .psqlrc (X)

To filter columns you must name them in the SELECT list. psql always outputs the full result set it gets from the server. E.g. SELECT status FROM pool_nodes.

Or you can cut to extract ordinal column numbers e.g.

psql -qAtX -c 'whatever' | cut -d '|' -f 1,2-4

(I have no idea how show pool_nodes can produce the output you show here, since SHOW returns a single scalar value...)

To change the delimiter from a pipe | to something else, use -F e.g. -F ','. But be warned, the delimiter is not escaped when it appears in output, this isn't CSV. You might want to consider a tab as a useful option; you have to enter a quoted literal tab to do this. (If doing it in an interactive shell, search for "how to enter literal tab in bash" when you get stuck).

Example showing all the above, given dummy data:

CREATE TABLE dummy_table (
   a integer,
   b integer,
   c text,
   d text
);

INSERT INTO dummy_table
VALUES
(1,1,'chicken','turkey'),
(2,2,'goat','cow'),
(3,3,'mantis','cricket');

query, with single space as the column delimiter (so you'd better not have spaces in your data!):

psql -qAtX -F ' ' -c 'SELECT a, b, d FROM dummy_table'

If for some reason you cannot generate a column-list for SELECT you can instead filter by column-ordinal with cut:

psql -qAtX -F '^' -c 'TABLE dummy_table' | cut -d '^' -f 1-2,4