0

For some reason the code below breaks in psql as supplied with Greenplum at the \copy stage:

\set tmp1 public.tmp1

DROP TABLE IF EXISTS :tmp1;
CREATE TABLE :tmp1 (new_id varchar(255), old_id BIGINT) DISTRIBUTED BY (old_id);
\echo :tmp1
\copy :tmp1 FROM 'file1.csv' WITH DELIMITER '|' HEADER CSV;
ERROR:  syntax error at or near ":"
LINE 1: COPY  :tmp1 FROM STDIN WITH DELIMITER '|' HEADER CSV;

How can you use a variable table name with the copy command in psql?

Bell
  • 17,907
  • 4
  • 23
  • 25
mgoldwasser
  • 14,558
  • 15
  • 79
  • 103
  • 2
    I don't get that error on 9.2. But `DISTRIBUTED BY` is not valid for postgresql - what database are you actually using here? – harmic Sep 09 '15 at 00:10
  • 2
    Is this really Redshift or Greenplum? If so please *always say so in the question*. These products are based on very old versions of PostgreSQL, heavily modified and not particularly compatible. – Craig Ringer Sep 09 '15 at 00:33
  • Sorry, yes, this is using greenplum on the backend. – mgoldwasser Sep 09 '15 at 13:05
  • 1
    The issue is not that the backend database is Greenplum - the :variable substitution takes place (or is failing to take place) in the psql client application. The problem is that the build of psql shipped with Greenplum is based on a historic fork. Easy route around the problem would be to use a current build of psql from Postgres or your Linux distro. I will protest on connection but will work fine. – Bell Sep 10 '15 at 12:22

1 Answers1

0

I don't think this has to do with greenplum or an old PostgreSQL version. The \copy meta command does not expand variables as documented here:

Unlike most other meta-commands, the entire remainder of the line is always taken to be the arguments of \copy, and neither variable interpolation nor backquote expansion are performed in the arguments.

To workaround you can build, store and execute the command in multiple steps. Replace your \copy ... line with

\set cmd '\\copy ' :tmp1 ' FROM ''file1.csv'' WITH DELIMITER ''|'' HEADER CSV'
:cmd

With this, you need to double (escape) the \ and ' within the embedded meta command. Keep in mind that \set concatenates all further arguments to the second one, so you need to quote spaces between the arguments.

tbussmann
  • 593
  • 7
  • 11