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

Append cell in postgresql

I have the table in POSTGRESQL. How can insert row if in the table is not exist or update if exist by appending array in last table (Adding to the array should add new text in last column, like "1. First;" "1. First;2.Second;" "1. First;2.Second;2.Second;" etc)?

This is my code that I want to use:

INSERT INTO public.history (arm_number, dt_action)
VALUES ('1', '{"1. First;"}')
ON CONFLICT (arm_number)
DO UPDATE SET dt_action = ARRAY_APPEND(EXCLUDED.dt_action, '2. Second;');

When I use it i got only "1. First;2.Second;" What should I do?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found my mistake. It was necessary to write "history.dt_action" instead of "EXCLUDED.dt_action"

    Below is the final code.

    INSERT INTO public.history (arm_number, dt_action)
VALUES ('1', '{"1. First;"}')
ON CONFLICT (arm_number)
DO UPDATE SET dt_action = ARRAY_APPEND(history.dt_action, '2. Second;');

  2. 0

    Works fine over here, when using an ARRAY:

    CREATE TABLE public.history(arm_number int PRIMARY KEY , dt_action text[]);

INSERT INTO public.history (arm_number, dt_action)
VALUES ('1', '{"1. First;"}')
ON CONFLICT (arm_number)
DO UPDATE SET dt_action = ARRAY_APPEND(EXCLUDED.dt_action, '2. Second;');

INSERT INTO public.history (arm_number, dt_action)
VALUES ('1', '{"1. First;"}')
ON CONFLICT (arm_number)
DO UPDATE SET dt_action = ARRAY_APPEND(EXCLUDED.dt_action, '2. Second;');

SELECT arm_number
     , unnest(dt_action)
FROM public.history;

    Result:

    1,1. First;

    1,2. Second;

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

Back To Top
Search