PostgreSQL psql terminal command

Question

I'm trying to get psql to format nicely and am following the docs here. Right now, whenever I do a query on tables with lots of columns, no matter how big I make my screen each line overflows into the next line and producing a whole screen of unreadable junk.

The docs (link is above) say there's a way to align columns nicely for more readable output.

Normally, to start psql, I just type:

psql

and hit Enter. Now I'm trying:

psql \pset format aligned

And getting an error:

could not change directory to "/root"
psql: warning: extra command-line argument "aligned" ingored
psql: FATAL: Indent authentication failed for user "format"

Any ideas as to how I could get these command-line args to work for me?

On Ubuntu 13/10: sudo -u postgres psql . Then \c in the interactive shell results in "You are now connected to database "postgres" as user "postgres". — Timo
– Timo, Commented Jul 9, 2014 at 9:02

Szymon Lipiński · Accepted Answer · 2012-01-24 16:30:24Z

91

These are not command line args. Run psql. Manage to log into database (so pass the hostname, port, user and database if needed). And then write it in the psql program.

Example (below are two commands, write the first one, press enter, wait for psql to login, write the second):

psql -h host -p 5900 -U username database
\pset format aligned

answered Jan 24, 2012 at 16:30

Szymon Lipiński

28.8k17 gold badges81 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mani Over a year ago

note the arrangement of keywords , try to write it in the same order and it works :) - Thanks dear

Evan Over a year ago

For my installation of PostgreSQL 9.6.10, I have to use psql -h host -p port -d database -U user -W, with the settings being case sensitive.

YuriBro · Accepted Answer · 2012-01-24 18:49:33Z

Use \x Example from postgres manual:

    postgres=# \x
    postgres=# SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 3;
    -[ RECORD 1 ]------------------------------------------------------------
    userid     | 10
    dbid       | 63781
    query      | UPDATE branches SET bbalance = bbalance + $1 WHERE bid = $2;
    calls      | 3000
    total_time | 20.716706
    rows       | 3000
    -[ RECORD 2 ]------------------------------------------------------------
    userid     | 10
    dbid       | 63781
    query      | UPDATE tellers SET tbalance = tbalance + $1 WHERE tid = $2;
    calls      | 3000
    total_time | 17.1107649999999
    rows       | 3000
    -[ RECORD 3 ]------------------------------------------------------------
    userid     | 10
    dbid       | 63781
    query      | UPDATE accounts SET abalance = abalance + $1 WHERE aid = $2;
    calls      | 3000
    total_time | 0.645601
    rows       | 3000

Nowaker · Accepted Answer · 2012-07-18 19:58:16Z

4

psql --pset=format=FORMAT

Great for executing queries from command line, e.g.

psql --pset=format=unaligned -c "select bandanavalue from bandana where bandanakey = 'atlassian.confluence.settings';"

answered Jul 18, 2012 at 19:58

Nowaker

12.4k4 gold badges58 silver badges64 bronze badges

Collectives™ on Stack Overflow

PostgreSQL psql terminal command

3 Answers 3

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Related