ERROR: missing data for column when using COPY FROM PROGRAM in psql

Question

i'm trying to import the data from a cURL with the next command in psql:

COPY testtable FROM PROGRAM 'curl https://.....'

This is the data in the URL:

[{"date":"20201006T120000Z","uri":"secret","val":"1765.756"},{"date":"20201006T120500Z","uri":"secret","val":"2015.09258"},{"date":"20201006T121000Z","uri":"secret","val":"2283.0885"}]

But psql returns

ERROR: missing data for column "uri"

I've tried copying it on tables with the columns as text and json format. Also tried adding (DELIMITER ',') but that returns

ERROR: extra data after last expected column

i feel like the problem could be caused from the "[]" in the start and the end of the data, but i im not sure.

These are the definitions of the tables that i used.

Table "public.test_table"

Column	Type	Modifiers
date	text	not null
uri	text
val	text

Indexes: "test_table_pkey" PRIMARY KEY, btree (date)

Table "public.test_table2"

Column	Type	Modifiers
date	json
uri	json
val	json

@jjanes i just edited the post and added the definitions of tables used. — Lautaro Aguilera
– Lautaro Aguilera, Commented Feb 13, 2021 at 1:45

jjanes · Accepted Answer · 2021-02-13 16:58:24Z

1

COPY only supports csv, text, and binary formats. It does not support JSON. It will import or export data into or out of json fields as a whole, but will not assemble or parse them.

You could use a staging table with one row and one column.

create temp table stage(x jsonb);

COPY stage FROM PROGRAM 'curl https://.....';

insert into test_table select f.* from stage,
   jsonb_populate_recordset(null::test_table, x) f;

If PostgreSQL offered a pg_read_program() function, you could use that directly rather than creating a stage table. But it doesn't (but you could make one in C, or plpythonu or plperlu)

answered Feb 13, 2021 at 16:58

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ERROR: missing data for column when using COPY FROM PROGRAM in psql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related