How can I insert multi-entry array using [libpg] "COPY FROM STDIN" method in PostgreSQL?

Question

I am using libpq to insert bulk data from a C program.

The bulk data contain data type that is double array, which in PostgreSQL is float8[].

My platform is Windows 10, PostgreSQL 11.

The tested table is structured as follows:

create table TestTable(
    a  int4,
    b  float8,
    c  float8[]
)

The C code is as follow:

void OutputRes(int i)
{
    int         Status;
    char        sql[SQLLEN]= "COPY TestTable FROM STDIN with(delimiter ',');";
    PGconn     *conn = NULL;
    PGresult   *res;
    char buffer[] = "1,7.4,'{1.5}'\n";

    conn = PQconnectdb(CONNSTR);
    if (PQstatus(conn) != CONNECTION_OK) {
         printf("Connection failed: %s", PQerrorMessage(conn));
    }

    res = PQexec(conn, "COPY TestTable FROM STDIN with(delimiter ',');");
    if (PQresultStatus(res) != PGRES_COPY_IN) {
        printf("Not in COPY_IN mode\n");
    }
    PQclear(res);
    Status = PQputCopyData(conn, buffer, strlen(buffer));
    Status = PQputCopyData(conn, buffer, strlen(buffer));
    Status = PQputCopyEnd(conn, NULL);

    res = PQexec(conn, "COMMIT;");
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        printf("BEGIN command failed: %s", PQerrorMessage(conn));
    }
    PQclear(res);
    PQfinish(conn);
}

While "buffer" variable contains the data to be inserted.

The insertion succeedes if it is structured as follow:

buffer[]="1,7.4,{1.5}\n"

But if I would try to insert multiple entries in the array, the insertion doesn't work and no error message.

buffer[]="1,7.4,{1.5,2.3}\n"

Furthermore I tried the following, both failed.

buffer[]="1,7.4,\"{1.5,2.3}\"\n" 
buffer[]="1,7.4,'{1.5,2.3}'\n"

Can someone help me on this?

score 1 · Accepted Answer · answered Dec 26 '18 at 10:39

See the PostgreSQL docs. The default format for COPY is not CSV but text. If you change to CSV, then you can bracket a cell with quotes, the default quote being ".

Hence, try

COPY TestTable FROM STDIN with delimiter ',' CSV

with double quotes around columns with ,:

buffer[]="1,7.4,\"{1.5,2.3}\"\n"

How can I insert multi-entry array using [libpg] "COPY FROM STDIN" method in PostgreSQL?

1 Answers1