How to use Variables in a psql Script
How to use Variables in a psql Script
April 6, 2025
I found this at Stack Overflow.
This shows that I could use variables to use for the file location.
postgres=# \set var1 AAAA
postgres=# \set var2 BBBB
postgres=# \echo :var1:var2
AAAABBBB
postgres=# \echo :var1 :var2
AAAA BBBB
postgres-# \echo :var1'\\':var2
AAAA\BBBB
postgres=# \set mypath '/tmp'
postgres=# \set mypathx :mypath/x.csv
postgres=# \echo :mypathx
/tmp/x.csv
postgres=# copy fo from :'mypathx';
COPY 1
postgres=# \set mypathy :mypath/y.csv
postgres=# copy fo from :'mypathy';
COPY 1Another example with Windows using $$ quoting:
\set path 'c:\\server\\data\\'
\set paymentMethodsPath :path 'paymentMethods.csv'
\set priceLevelsPath :path 'priceLevels.csv'
COPY paymentMethods (name,regexString) FROM $$:paymentMethodsPath$$ WITH (FORMAT csv, HEADER true);
COPY priceLevels (name) FROM $$:priceLevels$$ WITH (FORMAT csv, HEADER false);or with ' quotes:
\set path 'c:\\server\\data\\'
\set paymentMethodsPath 'E''':path'paymentMethods.csv'''
\set priceLevelsPath 'E''':path'priceLevels.csv'''
COPY paymentMethods (name,regexString) FROM :paymentMethodsPath WITH (FORMAT csv, HEADER true);
COPY priceLevels (name) FROM :priceLevels WITH (FORMAT csv, HEADER false);