Here is my psql script, which does not work:
\set path '''c:\\server\\data\\''';
COPY paymentMethods (name,regexString) FROM :path+'paymentMethods.csv' WITH (FORMAT csv, HEADER true);
COPY priceLevels (name) FROM :path+'priceLevels.csv' WITH (FORMAT csv, HEADER false);
psql complains about the syntax error at the +
How can I change it so that it works, while having the actual path string mentioned just once?
First of all, you are trying to concatenate two strings with + operator, but the SQL operator for concatenation is ||, with that information, you could think the expected result would be (won't work):
\set path '''c:\\server\\data\\'''
COPY paymentMethods (name,regexString) FROM :path || 'paymentMethods.csv' WITH (FORMAT csv, HEADER true);
COPY priceLevels (name) FROM :path || 'priceLevels.csv' WITH (FORMAT csv, HEADER false);
But! The COPY command expect a literal string for the path, not an expression, so you really should give the path. Notice it would work for commands like SELECT, INSERT, UPDATE, etc.
With that information, you can only use psql variables, as Pavel pointed, and concatenate the strings into a psql's variable. A good solution is to use the psql's :'var' syntax, that insert the variable as a string into the SQL expression:
\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);
Which will generate (will send to the PostgreSQL's server):
COPY paymentMethods (name,regexString) FROM E'c:\\server\\data\\paymentMethods.csv' WITH (FORMAT csv, HEADER true);
COPY priceLevels (name) FROM E'c:\\server\\data\\priceLevels.csv' WITH (FORMAT csv, HEADER false);
This :'var' syntax will not work on all psql versions (I don't recall now which one this was introduced), but for old versions you can easily use dollar-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 escape into single-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);
That is it.
psql has no operators. You can only place a variable (somewhere)
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 1
