psql variables – quoting and more
psql, postgreSQL powerful command line interface, has it's variables system. I do use it sometimes but... how many single quotes should I put there?
I have puzzled over this so many times, that I decided to put it here as a memo.
Proper quoting of text variables
Variable declaration:
\set d1 '''2008-01-01 00:00:00.000'''
\set d2 '''2008-12-31 23:59:59.999'''
Variable usage:
SELECT something FROM somewhere
WHERE now() BETWEEN :d1 AND :d2;
To make this post a bit more useful, here are some other
examples of psql variables usage
A shortcut for frequently used SQL phrase:
\set cnt 'SELECT COUNT(*) FROM'
:cnt users;
Another one:
\set csn 'SELECT * FROM DBA.CLIENTS WHERE name LIKE'
\set csni 'SELECT * FROM DBA.CLIENTS WHERE name ILIKE'
:csni 'kowalski'\g all_kowalskis.txt
This one if for pretty-ascii-report-printout lovers:
\set hr **************************************
\set inform '\\echo :hr \\echo :msg \\echo :hr'
Usage of the above:
\set msg 'starting mass update\nat ' `date`
:inform
**************************************
starting mass update
at Fri Dec 19 21:15:49 CET 2008
**************************************
Nice, eh?
You can put such "macros" into your .psqlrc file to save some keystrokes.
You can even create your very own "library" of psql macros and include it in your scripts with \i.
Have fun!
Note: these are not variables in SQL stored procedures sense. This is just plain text substitution.
A bit similar to Host Variables (note :colon usage), but not so powerful.
