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 <eric@phoenixsecure.com>

Show quoted text

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.

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.fonville@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 <eric@phoenixsecure.com <mailto:eric@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.

Le mercredi 04 dￜcembre 2013 ￜ 17:48 +0000, 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