Postgres equivalent to MySQL's \G?

Question

Does anyone know if Postgres has a way to display query results "prettily", like how MySQL does when ending a query with \G on the command line? For instance, "select * from sometable\G" as opposed to "select * from sometable;"

Many thanks!

Answer 1

I'm not familiar enough with MySQL to know what the \G option does, but based on the documentation it looks like the psql \x option might do what you want.

It's a toggle, though, so you do it before you submit the query.

\x
select * from sometable;

Answer 2

Actually, you can enable \G mysql style formatting on a per-query basis in psql by putting the following in ~/.psqlrc:

\set G '\\set QUIET 1\\x\\g\\x\\set QUIET 0'

You can then use :G at the end of a query in the psql client the same as you would \G at the end of a query in the mysql client:

pagila=# select * from foo limit 1;
 id | uniq | val |       val_ts        
----+------+-----+---------------------
  1 |    1 |   3 | 2007-07-03 00:00:00
(1 row)

Time: 11.481 ms
pagila=# select * from foo limit 1 :G
-[ RECORD 1 ]---------------
id     | 1
uniq   | 1
val    | 3
val_ts | 2007-07-03 00:00:00

Answer 3

Borrowing from this answer:

You can append \x\g\x to the query just as you would with MySQL's \G. For example:

select * from users \x\g\x

Note the lack of ; in the above line, this is intentional.

This has the effect of enabling expanded display, running the expressed query, and disabling expanded display, all in one statement.

Answer 4

Since PostgreSQL 10, psql has \gx which is the exact equivalent of mysql's \G

select * from sometable \gx

Documentation:

\gx [ filename ]
\gx [ |command ]
\gx is equivalent to \g, but forces expanded output mode for this query. See \x.

The sequence \x\g\x does the same, but only when \x (=expanded display) is off. Otherwise it does the opposite! By contrast \gx always displays with the expanded output independently of the current regime of \x.

Answer 5

You can toggle psql into extended mode with \x before you run a command, but you can't do it on a command by command basis like you can in mysql.