Insert date in table using json_populate_recordset

Question

I'm trying insert data in table using this query

INSERT INTO table (
url,
v_count,
v_date)
SELECT
url,
v_count,
v_date FROM json_populate_recorset(null::record,
'[{"url_site":"test.com","visit_count":1,"visit_date":"2022-08-31"},
{"url_site":"dev.com","visit_count":2,"visit_date":"2022-08-31"}]'::json) 
AS ("url" varchar(700), "v_count" integer, "v_date" date)

And I'm getting this error: null value in column "v_date" of relation table violates not null constraint

Since my json could be hundreds of entries at some times, how should I send the date in my json ? There is another (efficient) way to insert this data in the table ?

Edit: in postico 1.5.20 my example above works as long as I have the json key named the same as the table columns, how can I reference differents names in my json keys?

json_populate_recordset with record as the type looks like a bad idea, since it'll basically ignore the property names — Bergi
– Bergi, Commented Sep 1, 2022 at 7:47
If I use the name of the table, the "as" part is not necessary, thanks. With my original example how can I reference my json keys in my select? — DanielEsp
– DanielEsp, Commented Sep 1, 2022 at 18:29
I'd recommend to just use json_array_elements(…), then el->>'url_site', (el->> 'visit_count')::int, (el->>'visit_date')::date. — Bergi
– Bergi, Commented Sep 1, 2022 at 21:20

richyen · Accepted Answer · 2022-09-01 04:54:18Z

1

Since v_date can resolve to null, you'll need to either skip them or provide a value when null appears.

To skip the null values, you may want to add a WHERE v_date NOTNULL clause to your SELECT statement.

Otherwise, you can use COALESCE() to assign a value when v_date is null. For example ... SELECT url, v_count, COALESCE(v_date,now()) FROM json_populate_recordset...

answered Sep 1, 2022 at 4:54

richyen

10.3k4 gold badges18 silver badges36 bronze badges

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

Collectives™ on Stack Overflow

Insert date in table using json_populate_recordset

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