Postgres Copy from Variable with CSV Data

Postgres Copy from Variable with CSV data

That's not possible with the SQL COPY command. COPY only copies from a file or STDIN.

You can either write the content of the variable to a file or pipe it via STDIN. Only makes sense for more than a couple of rows.

---

I think I misunderstood your question before the update, you probably don't need this:

The file path can not be exchanged like other data items, and you can't use a prepared statement for that. Build the whole statement before executing or resort to dynamic SQL with a server-side function like:

CREATE OR REPLACE FUNCTION f_cp(_file text)
  RETURNS void AS
$BODY$
BEGIN
EXECUTE format($$COPY zip_codes FROM %L DELIMITER ',' CSV$$, $1);
END
$BODY$
  LANGUAGE plpgsql;

Call:

SELECT f_cp('/var/lib/postgres/sync/myfile.csv')

format() requires Postgres 9.1 or later.

Is it possible to use variables in a postgresql copy from program URL?

Parameters can only be used with SELECT, INSERT, UPDATE and DELETE in PostgreSQL.

If you need a parameterized COPY statement, you have to construct an SQL string using the parameter and send that. How exactly you construct a query string depends on the programming language you are using.

In PL/pgSQL, the standard stored procedure language of PostgreSQL, it would for example look like

EXECUTE format(
           'COPY test FROM PROGRAM ''curl https://example.com/events/123&start_date=%s''',
           '20210331T191500Z'
        );

How can I use a variable to specify a COPY TO destination in a function?

You cannot use anything else than a literal for the path. But you can build the command from the variables and then EXECUTE it.

...
EXECUTE 'COPY (' || adhoc_query || ') TO ''' || fileName || ''' CSV;';
...

Variable substitution in psql \copy

Dynamically build the \copy command and store it in a file. Then execute it with \i

First set tuples only output

\t

Set the output to a file

\o 'C:/users/user/desktop/copy_command.txt'

Build the \copy command

select format(
    $$\copy (select * from the_table) To 'C:/users/user/desktop/table_%s.csv' WITH DELIMITER AS ';' CSV HEADER$$
    , current_date
);

Restore the output to stdout

\o

Execute the generated command from the file

\i 'C:/users/user/desktop/copy_command.txt'

COPY FROM CSV with static fields on Postgres

Use a temp table to import into. This allows you to:

• add/remove/update columns
• add extra literal data
• delete or ignore records (such as duplicates)

, before inserting the new records into the actual table.

---

       -- target table
CREATE TABLE data
    ( id SERIAL PRIMARY KEY
    , batch_name varchar NOT NULL
    , remote_key varchar NOT NULL
    , payload varchar
        , UNIQUE (batch_name, remote_key)
        -- or::
        -- , UNIQUE (remote_key)
    );
        -- temp table
CREATE TEMP TABLE temp_data
    ( remote_key varchar -- PRIMARY KEY
    , payload varchar
    );

COPY temp_data(remote_key,payload)
FROM '/tmp/Account-005'
    ;

        -- The actual insert
        -- (you could also filter out or handle duplicates here)
INSERT INTO data(batch_name, remote_key, payload)
SELECT 'Account-005', t.remote_key, t.payload
FROM temp_data t
    ;

---

BTW It is possible to automate the above: put it into a function (or maybe a prepared statement), using the filename/literal as argument.

Use function variable in dynamic COPY statement

Yes, it is possible to COPY from any query, whether or not it refers to a table.

However, COPY is a non-plannable statement, a utility statement. It doesn't support query parameters - and query parameters are how PL/PgSQL implements the insertion of variables into statements.

So you can't use PL/PgSQL variables with COPY.

You must instead use dynamic SQL with EXECUTE. See the Pl/PgSQL documentation for examples. There are lots of examples here on Stack Overflow and on https://dba.stackexchange.com/ too.

Something like:

EXECUTE format('
   COPY (
       select %L
   )
   TO ''c:/temp/out.csv'';
', my_var);

The same applies if you want the file path to be dynamic - you'd use:

EXECUTE format('
   COPY (
       select %L
   )
   TO %L;
', my_var, 'file_name.csv');

It also works for dynamic column names but you would use %I (for identifier, like "my_name") instead of %L for literal like 'my_value'. For details on %I and %L, see the documentation for format.

---

Related Topics