Click here to Skip to main content
14,545,210 members
Rate this:
Please Sign up or sign in to vote.
See more:
I am looking for the right update command to update table tmp.x so, that in column tmp.x.t these two dates ('08.12.2017','03.12.2017') from tmp.y.p where added to the text[] array. And this only when tmp.x.n = tmp.y.n and tmp.x.v = tmp.y.v.

http://sqlfiddle.com/#!15/53eb3/1 [^]

What I have tried:

update 
	tmp.x
set 
	t = array_append(tmp.x.t,tmp.y.p)
from 
	tmp.y
where 
	tmp.x.n = tmp.y.n
and
	tmp.x.v = tmp.y.v;
Posted
Updated 21-May-20 7:44am
v2

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

The problem might be that sqlfiddle does not support this functionality.
Schema's do not seem to be supported, but you can try like this:
create table tmpx (n text, v text, t text[]);
create table tmpy (n text, v text, p text);
delete from tmpx;
delete from tmpy;
insert into tmpx values ('android.apps', '3.1.4', null);
insert into tmpy values ('android.apps', '3.1.4', '08.12.2017');
insert into tmpy values ('android.apps', '3.1.4', '03.12.2017');

UPDATE tmpx set t = sub.t 
FROM (select array_agg(p) as t 
      from tmpy,tmpx where tmpx.n = tmpy.n and tmpx.v = tmpy.v) AS sub;

select * from tmpx;

Also you need to select PostgreSQL 9.6 instead of 9.3
   
v5
Comments
Member 13453926 21-May-20 14:10pm
   
I used sqlfiddle for an example. I tried it also in postgresql with my real data without luck. I seems to me that update isn't able to update the same row multiple times in the destination table.
RickZeeland 22-May-20 1:43am
   
Updated the answer with the ARRAY_AGG() function, see: https://www.postgresqltutorial.com/postgresql-aggregate-functions/postgresql-array_agg-function/

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100