Re: [GENERAL] Need help with upsert

[Richard Dunks]

Hello,

On Dec 4, 2013, at 12:39 PM, Eric Lamer  wrote:

> Hi,
>  
>I need some help with upsert.
>  
>Some info on what I want to do:
>  
>Each day I create a table for my firewall logs.   I have one entry for one 
> occurrence of all the field except sum, which is the number of occurrence I 
> have of each log that match all the field. My table has the following field: 
> firewall,action,src_zone,src_ip,dst_zone,dst_ip,proto,port,hex1,hex2,sum
>   
>   Each day I want to copy the last 7 days into one table so I have one table 
> with the last 7 days of logs.
>  
>   So I want to copy the data from 7 tables into 1.  If the row does not exist 
> I just insert and if the row already exist I just update the sum (existing 
> sum + new sum).
>  
>   Public.test is the table I use for the last 7 days logs.
>   daily.daily_20131202 is table for 1 day.
>   I will run this command 7 times with different daily table.
>  
> WITH upsert as
> (update public.test T set 
> firewall=S.firewall,action=S.action,src_zone=S.src_zone,src_ip=S.src_ip,dst_zone=S.dst_zone,dst_ip=S.dst_ip,proto=S.proto,port=S.port,hex1=S.hex1,hex2=S.hex2,sum=T.sum+S.sum
>  from daily.daily_20131202 S where (T.firewall=S.firewall and 
> T.action=S.action and T.src_zone=S.src_zone and T.src_ip=S.src_ip and 
> T.dst_zone=S.dst_zone and T.dst_ip=S.dst_ip and T.proto=S.proto and 
> T.port=S.port and T.hex1=S.hex1 and T.hex2=S.hex2) RETURNING * )
> insert into public.test select * from daily.daily_20131202 a WHERE NOT EXISTS 
> (SELECT * FROM upsert b WHERE a.firewall=b.firewall and a.action=b.action and 
> a.src_zone=b.src_zone and a.src_ip=b.src_ip and a.dst_zone=b.dst_zone and 
> a.dst_ip=b.dst_ip and a.proto=b.proto and a.port=b.port and a.hex1=b.hex1 and 
> a.hex2=b.hex2);
>  
> When I run the command I get an  error
> ERROR:  column reference "firewall" is ambiguous
> LINE 3: ... a WHERE NOT EXISTS (SELECT * FROM upsert b WHERE b.firewall...
>  
> Any idea what I am doing wrong?

In your UPDATE statement, I'd suggest explicitly putting the "T" table alias 
before each column you're setting. That will make the assignment more explicit 
and hopefully get around the error.

>  
> Also, is there an easier way to do that?
>  
> Thanks for the help.

Best,
Richard Dunks

Re: [GENERAL] INSERT RETURNING with values other than inserted ones.

[Richard Dunks]

If you're just replicating the data from table A into table B, why does it need 
its own ID number? Wouldn't the table A ID suffice?

I'd recommend using the following:

CREATE TABLE b AS ( SELECT * FROM a );

This way, you only define the columns and insert the data once, then let 
Postgres do the rest for you. Obviously if you need to have a separate table B 
ID, you can alter as necessary. 

Good luck,
Richard Dunks

On Jun 10, 2013, at 7:29 PM, Aleksandr Furmanov  
wrote:

> Hello,
> I want to insert new values into target table 'a' from source table 'b', and 
> then update table 'b' with ids from table 'a', somewhat like:
> 
> CREATE TABLE a(id SERIAL, name TEXT);
> INSERT INTO a (name) VALUES('Jason');
> INSERT INTO a (name) VALUES('Peter');
> 
> CREATE TABLE b(row_id serial, id INT, name TEXT);
> INSERT INTO b (name) VALUES('Jason');
> INSERT INTO b (name) VALUES('Peter');
> 
> 
> WITH inserted AS (INSERT INTO a (name) SELECT b.name FROM b WHERE b.name = 
> name RETURNING a.id)
>  UPDATE b SET id = inserted.id FROM inserted WHERE inserted.row_id = b.row_id;
> 
> However this would not work for obvious reason:
> 
> WHERE inserted.row_id = b.row_id is invalid because RETURNING clause cannot 
> return row_id.
> What can be returned are only columns of 'a', but they are insufficient to 
> identify matching records of 'b'.
> 
> So the question is - what to put in WHERE clause to match RETURNING with rows 
> being inserted from 'b'?
> 
> Thanks!
> 
> --
> Aleksandr Furmanov
> 
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general