3

I am trying to insert a series of values from an array of jsonb into postgres. However, I get the error error: invalid input syntax for uuid:.

My data looks like so

[
  {
    code: "dwfwradsa",
    purpose: "description",
    id: uuid (real uuid, this is just a placeholder)
  },
  {repeat},
  {repeat}
]

And the relevant part of my function is as follows. @codes is a parameter passed into the function.

  INSERT INTO
    "codes" (
      "code",
      "purpose",
      "id"
    )
  SELECT
    codes->>'code',
    codes->>'purpose',
    codes->>'id'::UUID -- this is a foreign key from another table
  FROM jsonb_array_elements("@codes") codes
  ON CONFLICT ON CONSTRAINT unique_code DO NOTHING;

Even casting seems to not fix the problem.

If I do not cast, I receive this error error: column "column_name" is of type uuid but expression is of type text.

Brandon
  • 1,447
  • 2
  • 21
  • 41
  • did you ever find an answer? – Josh Mc Dec 12 '19 at 12:06
  • I likely did but this was so long ago and I no longer have access to the code so I cannot confirm what the solution was – Brandon Dec 12 '19 at 21:29
  • 1
    For anyone else stumbling across this, for me I needed to use `CAST(jsonrow->>'key' AS UUID)`, and in my case it was problems around null-allowed uuid column handling. – Josh Mc Dec 12 '19 at 22:05

0 Answers0