Re: Need help with upsert

Hello,

On Dec 4, 2013, at 12:39 PM, Eric Lamer <eric(at)phoenixsecure(dot)com> 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