- The 1st row of the CSV file has column names of defined format.
PROGRAM clause of
GET DIAGNOSTICS after
COPY require Postgres 9.3+.
format() requires Postgres 9.1+
- This works with pure standard Postgres - except for the
head command that the shell is expected to provide. For Windows versions consider:
This function copies any table structure completely dynamically:
CREATE OR REPLACE FUNCTION f_dynamic_copy(_file text
, _tbl text = 'tmp1'
, _delim varchar(1) = E'\t'
, _nodelim varchar(1) = '')
RETURNS void AS
-- create staging table for 1st row as single text column
CREATE TEMP TABLE tmp0(cols text) ON COMMIT DROP;
-- fetch 1st row
EXECUTE format($$COPY tmp0 FROM PROGRAM 'head -n1 %I' WITH (DELIMITER %L)$$
, _file, _delim);
-- create actual temp table with all columns text
SELECT format('CREATE TEMP TABLE %I(', _tbl)
|| string_agg(quote_ident(col) || ' text', ',')
FROM (SELECT cols FROM tmp0 LIMIT 1) t
, unnest(string_to_array(t.cols, E'\t')) col
-- Import data
EXECUTE format($$COPY %I FROM %L WITH (FORMAT csv, HEADER, NULL '\N', DELIMITER %L)$$
, _tbl, _file, _delim);
GET DIAGNOSTICS row_ct = ROW_COUNT;
RETURN format('%I with %s rows.', _tbl, row_ct);
$func$ LANGUAGE plpgsql;
SELECT f_dynamic_copy('/path/to/file2.csv', 'tmp_file2');
tmp_file2 with 10 rows.
Before the main
COPY, run a preliminary
COPY ... TO tmp0 to fetch the first row with column names, which are expected to be unquoted, case-sensitive strings like
COPY ... TO ... (FORMAT csv, HEADER) would export them.
The structure of the actual target table is derived from it, all columns with data type
text. The default name of the resulting table is
tmp1 - unless you provide a table name as 2nd parameter to the function.
COPY is executed. The default delimiter is a tab character - or provide your delimiter as 3rd parameter to the function.
Use any single-byte character for the non-delimiter
_nodelim which does not appear in the first line of your CSV file. I am arbitrarily picking the control character "Delete" (ASCII 127), assuming that won't pop up - or provide your non-delimiter as 4th parameter to the function.
The function returns table name and number of imported rows.
Remember, a temporary table dies with the end of the session.
Executing a command with
PROGRAM might be restricted by the operating
system's access control mechanisms, such as SELinux.
Related answer on SO:
That version is too old, I am not going to back-port that far.
GET DIAGNOSTICS is an optional feature. You can just leave it away or replace it with a full count on the table
A primitive (expensive) alternative for the
PROGRAM clause of
COPY in pg 9.3 would be to import the complete table instead:
EXECUTE format($$COPY tmp0 FROM %L WITH (DELIMITER %L)$$, _file, _delim);
Or you prepare a second input file, or you can make it work by piping from the shell:
COPY tablename FROM STDIN is available in pg 8.4.
format() can be replaced with plain string concatenation. Be wary of SQL injection though!