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.
psql -E option will display the actual query generated by backslash
commands. (See Tip #0004 for an
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:
--csvoption to get the output as a CSV file
-toption 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
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
-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
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.
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.
-w flag is perfectly fine to use, in particular for scripts.