2 it depends and one absolutely always do this!

Ryan Booz asked the community on Twitter to contribute to a challenge called “PGSQL Phriday” where we publish posts according to a theme. The theme for PGSQL Phriday #001 is: “Two truths and a lie”. Of course, it’s difficult for me to call something a “truth”… So I was granted the right to use a “2 it depends and one absolutely always do this” instead!" My blog will be about psql command line options.

If you’re trying to explore the Postgres catalog, use the “-E” option 🔗

This is my first “it depends” point and I understand this can be controversial, but please bear with me while I explain my point of view.

The psql -E option will display the actual query generated by backslash commands. (See Tip #0004 for an example.)

People always point that out to learn how the Postgres catalog works and how to query it, but I do think that should you know some SQL, you would only need the Postgres documentation to understand how the catalog works! I guess it really depends on how your brain learns something new. Being a bookworm, I learn more by reading than by doing. I guess we’re all different.

Combine different options in psql to achieve what you want 🔗

This is my second “it depends” point and again, some people might not understand right why this is an “it depends” point. So, let’s take an example!

You’d like to export the result of a query in a CSV file, with a semi-colon as a separator and without a header. A way to achieve this is to combine the following options:

• --csv option to get the output as a CSV file
• -t option to get tupples only and no header
• --pset csv_fieldsep=';' option to get the CSV field separator as a semi-colon

And it works:

laetitia:~|⇒  psql --csv --pset csv_fieldsep=';' -c 'select * from test'
id;value
1;bla
2;bla
3;bla
4;bla
5;bla
6;bla

So, why do I say that “it depends”? Because I think that sometimes you should use \ in an SQL file instead, so that everyone can replay your query and get the same result than you.

Here’s the SQL file and how to play it:

laetitia:~|⇒  cat /tmp/test.sql    
\pset format csv
\pset tuples_only
\pset csv_fieldsep ';'

select * from test;

Now, let’s feed that to psql:

laetitia:~|⇒  psql -f /tmp/test.sql
Output format is csv.
Tuples only is on.
Field separator for CSV is ";".
1;bla
2;bla
3;bla
4;bla
5;bla
6;bla

And there’s no way someone can come back to you asking how to change the field separator to ‘;’!

You absolutely need to use -W to be sure to have a password prompt 🔗

The option -W forces psql to ask for your password without checking in the server-side if you need a password to connect. That way you will save a network roundtrip by asking psql a prompt to give the password before even knowing if a password is needed to connect!

Ok, now let’s see the problem with the -W flag. Suppose you used the -W flag, so psql asks for a password but in your specific connection case, you don’t need a password prompt (because you don’t use a password-based authentication method or because your password is already in the PGPASSWORD variable or you’re using a .pgpass file), then it will be confusing that it’s asking you for a password. Worse than that, you can give a wrong password and it will still be able to connect you, without any warning because the password given is not even tested!

The argument to save a network roundtrip is not even valid anymore because network bandwidth and speed are way better than during the 90s. You could object that you want to type in the password right now so that you can go grab a coffee instead of waiting for Postgres to figure out your authentication method. Again, network is not that slow nowadays and should you want not to wait in front of your computer, you would be more efficient by using a password file.

Conclusion 🔗

So, that was my “2 it depends and one you absolutely need to do this” blog post. Just to make sure I am clear enough, you shouldn’t use the -W flag with psql. But the -w flag is perfectly fine to use, in particular for scripts.