Обсуждение: Need help with upsert

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.

Richard Dunks <richarddunks@gmail.com> wrote:

> I will run this command 7 times with different daily table.

> [ ... ] a WHERE NOT EXISTS (SELECT * FROM upsert b WHERE a.firewall [ ... ]
>
> 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?

You are not showing us the actual query or the actual error
message.  (Note the mismatch in aliases qualifying "firewall" near
the ends of the above lines.)  Please cut and paste the entire
query and the entire error message, and make sure they are from the
same run.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company