Insert json as a record to PostgreSQL table

Question

I have a table in Postgres that looks something like:

TABLE Product (
 id serial,
 name varchar(512),
 price numeric,
 ...
)

I am trying to create a procedure that would take an array of json objects and insert each element as a row into the table.

I am doing something like this:

CREATE OR REPLACE PROCEDURE insert_into_product(entries json[]) 
LANGUAGE plpgsql AS $$
 DECLARE _elem json;
 BEGIN
 FOREACH _elem IN ARRAY entries
 LOOP 
 INSERT INTO product
 SELECT id, name, price
 FROM json_populate_record (NULL::product,
 format('{
 "id": "%d",
 "name": "%s",
 "price": "%f"
 }', _elem->id, _elem->name, _elem->price)::json
 );
 END LOOP;
END;
$$

And I'm getting an error "column "id" does not exist"

Is the problem here that id is serial, and in my json object it's an int? What's the right way to insert row from a json object here?

• user330315: json_populate_record needs to be called with the _elem as the parameter. format() returns a string value, not a JSON

Answer 1

Your _elem variable is already a JSON value, so you can pass that directly to the json_populate_record() function:

CREATE OR REPLACE PROCEDURE insert_into_product(entries json[]) 
LANGUAGE plpgsql 
AS $$
DECLARE 
 _elem json;
BEGIN
 FOREACH _elem IN ARRAY entries
 LOOP 
 INSERT INTO product
 SELECT id, name, price
 FROM json_populate_record (NULL::product, _elem);
 END LOOP;
END;
$$
;

But you don't need a LOOP or even PL/pgSQL for this:

CREATE OR REPLACE PROCEDURE insert_into_product(entries json[]) 
LANGUAGE sql
AS $$
 INSERT INTO product
 SELECT (json_populate_record(null::product, u.entry)).*
 FROM unnest(entries) as u(entry);
$$
;