Re: [GENERAL] Need help with upsert
Richard Dunks richarddu...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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? Also, is there an easier way to do that? Thanks for the help. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Need help with upsert
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 e...@phoenixsecure.com 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? Also, is there an easier way to do that? Thanks for the help.
Re: [GENERAL] Need help with upsert
LINE 3: ... a WHERE NOT EXISTS (SELECT * FROM upsert b WHERE b.firewall... As you can see it is b.firewall where b is alias to table upsert. That's why I don't understand the error. From: Serge Fonville [mailto:serge.fonvi...@gmail.com] Sent: December 4, 2013 12:53 PM To: Eric Lamer Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Need help with upsert 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 e...@phoenixsecure.com mailto:e...@phoenixsecure.com 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? Also, is there an easier way to do that? Thanks for the help. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Need help with upsert
Le mercredi 04 décembre 2013 à 17:48 +, Eric Lamer a écrit : 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). Unless you have a specific reason not to, ISTM you could make your life _much_ easier with just one table and a date column? 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. Also, is there an easier way to do that? I would just insert all rows into one big table with a date field, and then make a select count() with the appropriate where clause on the date field and group by clause. -- Salutations, Vincent Veyron Legal cases, contracts and insurance claims management http://libremen.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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? Also, is there an easier way to do that? Thanks for the help. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Need help with upsert
Hello, On Dec 4, 2013, at 12:39 PM, Eric Lamer e...@phoenixsecure.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
