postgres insert data from an other table inside array type columns

Question

I have tow table on Postgres 11 like so, with some ARRAY types columns.

CREATE TABLE test (
  id INT UNIQUE,
  category TEXT NOT NULL,
  quantitie NUMERIC,
  quantities INT[],
  dates INT[]
);
INSERT INTO test (id, category, quantitie, quantities, dates) VALUES (1, 'cat1', 33, ARRAY[66], ARRAY[123678]);
INSERT INTO test (id, category, quantitie, quantities, dates) VALUES (2, 'cat2', 99, ARRAY[22], ARRAY[879889]);                                                                       


CREATE TABLE test2 (
  idweb INT UNIQUE,
  quantities INT[],
  dates INT[]
);
INSERT INTO test2 (idweb, quantities, dates) VALUES (1, ARRAY[34], ARRAY[8776]);
INSERT INTO test2 (idweb, quantities, dates) VALUES (3, ARRAY[67], ARRAY[5443]);

I'm trying to update data from table test2 to table test only on rows with same id. inside ARRAY of table test and keeping originals values.

I use INSERT on conflict,

how to update only 2 columns quantities and dates.
running the sql under i've got also an error that i don't understand the origin.

Schema Error: error: column "quantitie" is of type numeric but expression is of type integer[]


INSERT INTO test (SELECT * FROM test2 WHERE idweb IN (SELECT id FROM test)) 
ON CONFLICT (id) 

DO UPDATE 
        SET
          quantities = array_cat(EXCLUDED.quantities, test.quantities),
          dates = array_cat(EXCLUDED.dates, test.dates);

https://www.db-fiddle.com/f/rs8BpjDUCciyZVwu5efNJE/0

is there a better way to update table test from table test2, or where i'm missing the sql?

update to show result needed on table test:

**Schema (PostgreSQL v11)**


| id  | quantitie | quantities | dates       |  category |
| --- | --------- | ---------- | ----------- | --------- |
| 2   | 99        | 22         | 879889      | cat2      |
| 1   | 33        | 34,66      | 8776,123678 | cat1      |

i update my question to show what i'm looking for, thanks @Jeremy — ziSigmund
– ziSigmund, Commented May 22, 2020 at 21:09

GMB · Accepted Answer · 2020-05-22 23:41:02Z

Basically, your query fails because the structures of the tables do not match - so you cannot insert into test select * from test2.

You could work around this by adding "fake" columns to the select list, like so:

insert into test
select idweb, 'foo', 0, quantities, dates  from test2 where idweb in (select id from test) 
on conflict (id) 
do update set
    quantities = array_cat(excluded.quantities, test.quantities),
    dates = array_cat(excluded.dates, test.dates);

But this looks much more convoluted than needed. Essentially, you want an update statement, so I would just recommend:

update test
set
    dates = test2.dates || test.dates,
    quantities = test2.quantities || test.quantities
from test2
where test.id = test2.idweb

Note that this ues || concatenation operator instead of array_cat() - it is shorter to write.

Demo on DB Fiddle:

id | category | quantitie | quantities | dates        
-: | :------- | --------: | :--------- | :------------
 2 | cat2     |        99 | {22}       | {879889}     
 1 | cat1     |        33 | {34,66}    | {8776,123678}

Collectives™ on Stack Overflow

postgres insert data from an other table inside array type columns

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related