Dynamically-generated table-name in PostgreSQL COPY command

Question

This PostgreSQL COPY command works:

copy tablename from E'c:\\abc\\a.txt';

but I want the tablename to be dynamically generated. How can I do this?

John Powell · Accepted Answer · 2018-01-08 12:05:44Z

16

You need to build a string, concatenating in the dynamic table name, and then use execute. Note that you escape the ' by ''. This also includes a dynamic name to save the file too. You need to replace savedir with the actual directory you are using.

CREATE OR REPLACE FUNCTION dynamicCopy(tablename text, outname text) RETURNS VOID AS $$

DECLARE STATEMENT TEXT;

BEGIN

  STATEMENT := 'COPY (select * from ' || quote_ident(tablename) || ') to ''savedir' || outname ||'.txt''';

  EXECUTE STATEMENT;

END;

$$ LANGUAGE 'plpgsql';

EDIT:

Since I first wrote this, I have discovered the format function, which I think is generally easier to read than SQL generated with the concatenation operator || and more flexible.

CREATE OR REPLACE FUNCTION dynamicCopy(tablename text, outname text) RETURNS VOID AS 
$BODY$
BEGIN
  EXECUTE FORMAT('COPY (SELECT * FROM %s) TO ''savedir%s.csv''',
                  tablename, 
                  outname);
END
$BODY$ 
LANGUAGE plpgsql;

See the official docs for a full discussion: https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

edited Jan 8, 2018 at 12:05

answered Mar 12, 2011 at 9:50

John Powell

12.6k6 gold badges64 silver badges69 bronze badges

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

3 Comments

ruakh Over a year ago

Your example-code actually demonstrates a dynamically-generated file-name rather than a dynamically-generated table-name. Also, you don't mention that your example-code specifies comma-delimited output, rather than the default tab-delimited output. (Did you copy your example-code from somewhere else?)

John Powell Over a year ago

Ruakh, thank you for pointing out the error. Now edited with dyanmic table name and file name. Yes, it is a greatly simplified version of something I use at work -- I will be more careful in future to answer the question properly.

Vadim Samokhin Over a year ago

Jesus Christ, it took me some time to figure out the typo in quot_ident! It is quotE_ident actually! Now this one does make sense: "pg_execute(): Query failed: ERROR: 42883: function quot_ident(text) does not exist"

jian · Accepted Answer · 2021-12-15 14:51:22Z

You can copy from multi csv files to an table via shell script.

Make an script file: vim csvtotable
write csvtotable script. Here is my example:

#!/bin/sh DBNAME=postgres files=$1 for file in ${files}; do psql -d ${DBNAME} -c "\copy parent_tree(parent_id, some_text) FROM '${file}' delimiters ',' csv header" done
Execute the script.

./csv2table "$(ls *.out.csv)"

Obviously, Local CSV file should be match with table. Then It will import from csv to database tables if csv file name ending with .out.csv. I am not sure csv name match is global or just at present directory match. Reference link:
-c command reference https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-PATTERNS chomd command: https://linuxize.com/post/chmod-command-in-linux/
bash shanebang: https://linuxize.com/post/bash-shebang/

Collectives™ on Stack Overflow

Dynamically-generated table-name in PostgreSQL COPY command

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related