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

Postgresql – Postgres update Query for Array column

This is so simple query. I need to update Bookings tables’, LinkedBookings column (int array data type) to it’s own Id. Below query didn’t work for me.

update public."Booking" b set "LinkedBookings" = {"Id"}

Above is giving below error,

SQL Error [42601]: ERROR: syntax error at or near "{"

I searching thorough the internet and postgres documentation and couldn’t find any proper simple query. I can understand there is a way to do with writing a Pg Function. Rather than doing such a big thing, isn’t there any simple query for that?

Tags:

2

Answers


  1. 0

    Assuming LinkedBookings is an array column and assuming that you want to set it to an array containing only the Id of each row, the correct SQL should be >>

       UPDATE public."Booking" b 
    SET "LinkedBookings" = ARRAY[b."Id"];
    Login or Signup to reply.
  2. 0

    Here’s the the doc you were looking for.

    The syntax you used is for literals – you got the error because you’re missing the single quotes around the brackets, and the entire thing should be a text before it can become an int[] array. Demo at db<>fiddle:

    update public."Booking" b set "LinkedBookings" = ('{'||"Id"::text||'}')::int[]
  returning *,pg_typeof("LinkedBookings");
    Id LinkedBookings pg_typeof
    1 {1} integer[]
    2 {2} integer[]
    3 {3} integer[]

    The alternative syntax is wrapping the value in ARRAY[], already pointed out. You can add the explicit cast to be sure:

    update public."Booking" b set "LinkedBookings" = (ARRAY["Id"])::int[];

    If you’re just making "Id" available in an alternative form to save array-wrapping elsewhere, you can consider setting up "LinkedBookings" as a generated column – that way you don’t need to keep refreshing the table with periodic update.

    alter table public."Booking" drop column if exists "LinkedBookings";
alter table public."Booking" add column "LinkedBookings" int[] 
   generated always as ((ARRAY["Id"])::int[]) stored;
--no update necessary
select *,pg_typeof("LinkedBookings") from public."Booking";
    Id LinkedBookings pg_typeof
    1 {1} integer[]
    2 {2} integer[]
    3 {3} integer[]

    If you’re running this update to pre-populate the column and you plan to modify it later, don’t make it generated as those cannot be modified directly. They can only be re-generated by modifying the values they depend on.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search