PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Doing a bulk update in Vercel Postgres by dynamically building query

I have a simple table in Vercel Postgres:

export const sets = createTable("card_table", {
  id: varchar("id").primaryKey(),
  info: jsonb("info"),
});

I have populated the table with data and would like to do a bulk update. I figured out how to update one at a time:

const new_data = {
  id: "idx",
  new_field: {
    x: 'test'
  }
}

const query = 
`
  UPDATE card_table
  SET
     info = jsonb_set(
              info, '{new_field}', '${JSON.stringify(new_data.new_field)}'
            )
  WHERE info ->> 'id' = '${new_data.id}'
`

Now I’m trying to modify the query to do bulk updates:

const data = [
       {
         "id": "id1",
         "new_field": {
           "x": "test"
       },
       {
         "id": "id2",
         "new_field": {
           "x": "test"
       }
    ]

const updateValues = JSON.stringify(
  data.map((d) => ({ id: d, new_field: d.new_field }))
);

const query = 
`
  UPDATE card_table
  SET
     info = jsonb_set(
              info, '{new_field}', (elem->'new_field')::jsonb
            )
  FROM jsonb_array_elements('${updateValues}')
  WHERE info ->> 'id' = (elem->'id')::text
`

but the rows aren’t updating and I’m not getting any error messages. What am I doing wrong and/or is there a better way to go about this?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    With the help of the posted answer, I was able to get the query work by adding a CAST to the WHERE clause:

    Previous:

    WHERE info ->> 'id' = (elem->'id')::text

    Fixed:

    WHERE (info ->> 'id')::text = (elem->'id')::text

    apparently when you cast to 'text', quotes are added so previously the comparison would be something like: x = "x" which is False


  2. 0

    Both j->>'id' and (j->'id')::text will end up resulting in a text value, but it won’t necessarily be the same because of quoting:
    demo at db<>fiddle

    with cte(DATA,elem) as (values
  ('{"id":"1"}'::jsonb,'{"id":"1"}'::jsonb)
)
select DATA ->> 'id'
     , (elem->'id')::text
     , DATA ->> 'id' = (elem->'id')::text as "are these equal"
from cte;
    ?column? text are these equal
    1 "1" False

    Which means you might want to change your WHERE condition:

    WHERE DATA ->> 'id' = elem->>'id'

    Assuming there’s a 3rd column called DATA and based on contents of which you’re trying to update. Otherwise, I think you wanted

    UPDATE card_table
SET
   info = jsonb_set(
            card_table.info, '{new_field}', (j.elem->'new_field')::jsonb
          )
FROM jsonb_array_elements('${updateValues}') as j(elem)
WHERE card_table.id = j.elem->>'id'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search