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?

json_populate_record needs to be called with the _elem as the parameter. format() returns a string value, not a JSON — user330315
– user330315, Commented Jan 12, 2023 at 12:14

user330315 · Accepted Answer · 2023-01-12 12:22:11Z

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);
$$
;

answered Jan 12, 2023 at 12:22

user330315

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

Collectives™ on Stack Overflow

Insert json as a record to PostgreSQL table

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