Search Postgresql Archives

Re: Need help with upsert

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,


> ERROR:  column reference "firewall" is ambiguous

> LINE 3: ... a WHERE NOT EXISTS (SELECT * FROM upsert b WHERE b.firewall...

set firewal = misses a table

HTH

Kind regards/met vriendelijke groet,

Serge Fonville

http://www.sergefonville.nl


2013/12/4 Eric Lamer <eric@xxxxxxxxxxxxxxxxx>

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="" from daily.daily_20131202 S where (T.firewall=S.firewall and T.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="" 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?

 

Also, is there an easier way to do that?

 

Thanks for the help.



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux