How to query PostgreSQL as list of column name and value instead of table

Question

in MySQL I could display query result as a list, for example:

   id : 123
 name : 'test'
 foo  : 'bar'

instead of standard result:

+----+------+-----+
| id | name | foo |
+----+------+-----+
| 123|test  |bar  |
+----+------+-----+

using select * from tablename \G in mysql command line.

How to do this in PostgreSQL's psql?

You can check you this post: http://stackoverflow.com/questions/22407860/display-select-results-vertically-in-psql-as-is-done-by-mysqls-g — Phoenix, Apr 10 '15 at 06:22
See the examples in the manual: http://www.postgresql.org/docs/current/static/app-psql.html#APP-PSQL-EXAMPLES — , Apr 10 '15 at 06:23

Craig Ringer · Accepted Answer · 2015-04-10T06:40:29.733

I think you might be looking for the expanded output \x.

regress=> \x
Expanded display is on.
regress=> select * from posts;
-[ RECORD 1 ]---------
id    | 1
title | bork bork bork
-[ RECORD 2 ]---------
id    | 2
title | honkey tonk

or maybe \x\t\a (extended, tuples-only, unaligned):

craig=> \x\t\a
Expanded display is on.
Tuples only is on.
Output format is unaligned.

craig=> select * from posts;
id|1
title|bork bork bork

id|2
title|honkey tonk

You may want \f : too:

craig=> \x\t\a\f :
Expanded display is on.
Tuples only is on.
Output format is unaligned.
Field separator is ":".

craig=> select * from posts;
id:1
title:bork bork bork

id:2
title:honkey tonk

When I'm invoking psql from a script, I tend to use:

psql -qAt

often with the -0 option, too, so it emits null bytes as record separators so fields with embedded newlines are more easily handled.

How to query PostgreSQL as list of column name and value instead of table

1 Answers1