COPY command in Postgresql Inquiry

Question

Good day!

I would like to inquire about COPY command in Postgresql. I have this table:

CREATE TABLE pm_monitor_temporary
(
  date timestamp NOT NULL,
  targetid varchar(128) NOT NULL,
  value1 float8 NOT NULL,
  value2 float8 NOT NULL,
  value3 float8 NOT NULL,
  value4 float8 NOT NULL,
  value5 float8 NOT NULL,
  value6 float8 NOT NULL,
  datastatus1 varchar(2) NOT NULL,
  datastatus2 varchar(2) NOT NULL,
  datastatus3 varchar(2) NOT NULL,
  datastatus4 varchar(2) NOT NULL,
  datastatus5 varchar(2) NOT NULL,
  datastatus6 varchar(2) NOT NULL,
  granularity int4 NOT NULL,
  neid varchar(16) NOT NULL,
  CONSTRAINT pm_monitor_pkey PRIMARY KEY (date, targetid, granularity, neid)
);

I did create a function that will copy csv contents into the table given the filePath(absolute path of the file). There seems to be a problem with my sql function.:

Create Or Replace Function copycsv_pm_monitor_temp(filePath  varchar(1025))
Returns void As
$BODY$
DECLARE
  sql varchar(1025);
BEGIN
    sql := 'COPY  pm_monitor_temporary FROM {' || filePath ||
      '| stdin} using DELIMITERS ',' ' || ';' ;
    EXECUTE sql;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

This will compile, however this will eventually result to error when i run my java code. Did i miss something?

Caused by: org.postgresql.util.PSQLException: ERROR: query "SELECT  'COPY                  pm_monitor_temporary FROM {' ||  $1  || '| stdin} using DELIMITERS ',' ' || ';'" returned     2 columns

do you think you question is answered? It is a good practice to accept answers or provide comments why they're not acceptable. — vyegorov
– vyegorov, Commented Apr 14, 2012 at 19:59

kgrittn · Accepted Answer · 2012-04-11 12:29:10Z

3

You didn't handle your quoting properly, and accidentally got a statement in valid syntax which didn't have the right number of result columns. You've got some other problems in there, too.

This:

    sql := 'COPY  pm_monitor_temporary FROM {' || filePath ||
      '| stdin} using DELIMITERS ',' ' || ';' ;

should be something like this:

    sql := 'COPY pm_monitor_temporary FROM ''' || filePath ||
      ''' WITH (FORMAT csv, DELIMITER '','')';

answered Apr 11, 2012 at 12:29

kgrittn

19.8k4 gold badges41 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

COPY command in Postgresql Inquiry

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related