perlpostgresqldbd-pg

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

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 ?

Solution

  • 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.