Insert unique values from array of objects in postgres

Question

I have been trying to input an array of unique codes into postgres from an array of objects. This is what I have so far but it keeps on failing near INTO. I have tried a loop version before but it never ended up insert anything but would not create an error either. The below would be ideal, bulk-inserting without the need for any FOR loops.

CREATE OR REPLACE FUNCTION "InsertCodes" (
  "@codes"                  JSONB,
  "@type"                   TEXT
)
RETURNS void AS
$func$
DECLARE "id"                UUID;
BEGIN
  "id" = uuid_generate_v4();
  SELECT * FROM (
    SELECT *
    FROM jsonb_array_elements("@codes")
  ) AS c (
-- fails below
    INSERT INTO
      "codes" (
        "id",
        "code",
        "name",
        "type"
      )
    VALUES (
      "id",
      c."code",
      c."name",
      "@type"
    );
    ON CONSTRAINT ("code")
    DO NOTHING
    RETURNING 1;
  );
END;
$func$ LANGUAGE PLPGSQL;

@codes looks like the below:

[
  {
    "code": 1234,
    "name": "numeric"
  },
  {
    "code": "1k1l2k",
    "name": "alphanumeric"
  }
]

klin · Accepted Answer · 2018-08-21 22:13:05Z

3

The query should look like this:

"id" = uuid_generate_v4();
INSERT INTO "codes" ("id", "code", "name", "type")
SELECT "id", item->>'code', item->>'name', "@type"
FROM jsonb_array_elements("@codes") AS item
ON CONFLICT ("code") DO NOTHING;

Note that the same "id" will be inserted for all codes passed as jsonb array. If it is a primary key you should rather generate the "id" for each code separately:

INSERT INTO "codes" ("id", "code", "name", "type")
SELECT uuid_generate_v4(), item->>'code', item->>'name', "@type"
FROM jsonb_array_elements("@codes") AS item
ON CONFLICT ("code") DO NOTHING;

answered Aug 21, 2018 at 22:13

klin

123k15 gold badges241 silver badges263 bronze badges

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

Comments

Cetin Basoz · Accepted Answer · 2018-08-21 22:27:23Z

0

It is hard to understand what you are trying to do. Maybe this:

CREATE OR REPLACE FUNCTION "InsertCodes" (
  "@codes"                  JSONB,
  "@type"                   TEXT
)
RETURNS void AS
$func$
DECLARE "id"                UUID;
BEGIN
  "id" = uuid_generate_v4();

  with c("code", "name") as
  (
  SELECT codes->>'code', codes->>'name'
  FROM jsonb_array_elements("@codes") t(codes)
  ) 
-- fails below
    INSERT INTO 
      "codes" (
        "id",
        "code",
        "name",
        "type"
      )
    select 
      "id",
      c."code",
      c."name",
      "@type"
    from c
    ON CONFLICT ON CONSTRAINT "code"
    DO NOTHING;
END;
$func$ LANGUAGE PLPGSQL;

answered Aug 21, 2018 at 22:27

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

Collectives™ on Stack Overflow

Insert unique values from array of objects in postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related