On 03/24/2013 12:07:01 PM, Gsell Reto ([email protected]) wrote: > Hi, > > I have a problem with duplicated key entries in postgresql 9.2.
> On every INSERT/COPY I get the following error in the db: > > ------------------ > 2013-03-24 15:51:01 CET ERROR: duplicate key value violates unique > constraint "test_24_15_pk" > 2013-03-24 15:51:01 CET DETAIL: Key (agent_id, etype, mac_src, > mac_dst, tcp_flags, ip_proto, iface_in, iface_out, stamp_inserted)= > (9, > 800 , xx:xx:xx:xx:xx:xx, xx:xx:xx:xx:xx:xx, 31, 6, 963, 962, > 2013-03-24 15:50:00) already exists. > 2013-03-24 15:51:01 CET CONTEXT: COPY test_24_15, line 2930: > "2013-03-24 15:51:01,2013-03-24 15:50:00, xx:xx:xx:xx:xx:xx, > xx:xx:xx:xx:xx:xx,800,833,897,0,1,9,1024,1..." > 2013-03-24 15:51:01 CET STATEMENT: COPY test_24_15 (stamp_updated, > stamp_inserted, mac_src, mac_dst, etype, iface_in, iface_out, > tcp_flags, ip_proto, agent_id, packets, bytes) FROM STD > CREATE TABLE test_%d_%H ( > agent_id BIGINT NOT NULL DEFAULT 0, > iface_in BIGINT NOT NULL DEFAULT 0, > iface_out BIGINT NOT NULL DEFAULT 0, > etype CHAR(5) NOT NULL DEFAULT '', > mac_src macaddr NOT NULL DEFAULT '0:0:0:0:0:0', > mac_dst macaddr NOT NULL DEFAULT '0:0:0:0:0:0', > ip_proto SMALLINT NOT NULL DEFAULT 0, > tcp_flags SMALLINT NOT NULL DEFAULT 0, > packets BIGINT NOT NULL, > bytes BIGINT NOT NULL, > flows BIGINT NOT NULL DEFAULT 0, > stamp_inserted timestamp without time zone NOT NULL DEFAULT > '0001-01-01 00:00:00', > stamp_updated timestamp without time zone, > CONSTRAINT test_%d_%H_pk PRIMARY KEY (agent_id, etype, > mac_src, mac_dst, tcp_flags, ip_proto, iface_in, iface_out, > stamp_inserted) > ); Well, yeah. I don't know what the resolution of your clock is, but if one machine sends more than 1 packet per second to the same destination (or 1 packet per minute, or hour, or 10th of a second, or whatever your clock resolution is) you're going to have a duplicate value. You might be better served to have some sort of an id column declared SERIAL PRIMARY KEY. Karl <[email protected]> Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
