Postgres copy data & evaluate expression

Question

Is it possible a copy command to evaluate expressions upon insertion?

For example consider the following table

create table test1  ( a int, b int)

and we have a file to import

5  , case when b = 1 then 100 else 101
25 , case when b = 1 then 100 else 101
145, case when b = 1 then 100 else 101

The following command fill fail

COPY test1 FROM 'file' USING DELIMITERS ',';

with the following error

ERROR: invalid input syntax for integer

which means that it can not evaluate the case expression. Is there any workaround?

Did you build that file yourself? – Clodoaldo Neto Dec 28 '15 at 12:39 — Clodoaldo Neto, Dec 28 '15 at 12:39
Yes, it is the output of a migration script – dimcookies Dec 28 '15 at 14:01 — dimcookies, Dec 28 '15 at 14:01

score 1 · Answer 1 · answered Dec 28 '15 at 12:38

1

The command COPY only copies data (obviously) and does not evaluate SQL code, as explained in the documentation: http://www.postgresql.org/docs/9.3/static/sql-copy.html

As far as I know there is not workarounds to making COPY evaluating sql code.

You must preprocess your csv file and convert it to a standard sql script with INSERT statements in this form:

INSERT INTO your_table VALUES(145, CASE WHEN 1 = 1 THEN 100 ELSE 101 END);

Then execute the sql script with the client you are using. I.e. with psql you would use the -f option:

psql -d your_database -f your_sql_script

answered Dec 28 '15 at 12:38

Tom-db

Yes, i know that it works with insert statements, i checked the copy command for performance reasons – dimcookies Dec 28 '15 at 14:03
Well, the short answer is 'No, there is not a workaround' (AFAIK) – Tom-db Dec 28 '15 at 14:19

1 Answers1