How to insert null values using pg_putcopydata in DBD::Pg?

Question

I am populating a postgres table with millions of records. In order to speed up the process, I am making use of the pg_putcopydata in DBD:Pg. I am following this example :

$dbh->do("COPY mytable(flavor, slices) FROM STDIN WITH DELIMITER '~'");
$dbh->pg_putcopydata("Pepperoni~123\n");
$dbh->pg_putcopydata("Mushroom~314\n");
$dbh->pg_putcopydata("Anchovies~6\n");
$dbh->pg_putcopyend();

There are records where one of the fields may be emtpy. In order to insert empty values, I tried using "undef", "Null", "null", "", "NULL" inside pg_putcopydata. None of them worked.

How do I insert NULL values using pg_putcopydata ?

See the maual for `COPY` for details on the format. You'll want something like `WITH (DELIMITER '~', NULL '\N')` — Craig Ringer, Nov 25 '14 at 12:27

score 4 · Accepted Answer · edited May 23 '17 at 12:23

To include NULLs, you must use in-text insertion, identifying null with a text pattern, e.g. \N. This can be specified in the COPY command syntax, e.g.

COPY ... FROM ... WITH (DELIMITER '~', NULL '\N')

then

$dbh->pg_putcopydata("NoSuchFood~\\N\n");

(Doubling the backslash to produce a literal backslash sent to Pg).

This looks like the USDA database . If so, here's an answer I wrote earlier.

How to insert null values using pg_putcopydata in DBD::Pg?

1 Answers1