Postgres Pro
Downloads
Home   >   mailing lists

Thread: unique constraint

unique constraint

From
"Dominique Bessette - Halsema"
Date:
I'm getting the following error, and I think it's because when i insert into postgres from geoserver's WFS, i first delete then insert the track, and geoserver does this in succession but very fast.  If I personally test inserting two tracks with the same guid in a row, then it works, but because geoserver is going so fast it blows up and gives this error.  i'm looking into fixing it another way but does any one have an idea of anything i can do to avoid this error in postgres?
 

STATEMENT: INSERT INTO "public"."tracks" ("guid","classification","creationtimestamp","lastupdatetime","effectivetime","reportedtime","staletime","confidencevalue","behavioridentity","nationality","classificationcategory","classconfidencevalue","axislabels","uomlabels","aoumajaxis","aouminaxis","aouvalid","the_geom") VALUES ('OOSTZEE','U','2008-01-22T21:05: 15.366Z','2008-01-22T21:05:15.366Z','2008-01-22T21:05:15.366Z','2008-01-22T21:05:15.366Z','2008-01-22T21:05:15.366Z',1.0,'FRIEND','','SEA','1.0','Lat Long h','degree degree',9999999,9999999,'true',setSRID('00000000014029D8E757928E0D404B9C28CBD1244A'::geometry,4326))

ERROR: duplicate key violates unique constraint "tracks_guid_key"

 

 Track=# \d tracks
                                       Table "public.tracks"
         Column         |       Type        |                      Modifiers
------------------------+-------------------+------------------------------------------------------
 sid                    | integer           | not null default nextval('tracks_sid_seq'::regclass)
 guid                   | character varying | not null
 classification         | character varying |
 creationtimestamp      | character varying |
 lastupdatetime         | character varying |
 effectivetime          | character varying |
 reportedtime           | character varying |
 staletime              | character varying |
 confidencevalue        | numeric           |
 behavioridentity       | character varying |
 nationality            | character varying |
 classificationcategory | character varying |
 classconfidencevalue   | character varying |
 axislabels             | character varying |
 uomlabels              | character varying |
 aoumajaxis             | numeric           |
 aouminaxis             | numeric           |
 aouvalid               | character varying |
 the_geom               | geometry          |
Indexes:
    "tracks_pkey" PRIMARY KEY, btree (sid)
    "tracks_guid_key" UNIQUE, btree (guid)
    "tracks_the_geom_gist" gist (the_geom)
Check constraints:
    "enforce_geotype_the_geom" CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL)
    "enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)

Re: unique constraint

From
Erik Jones
Date:
On Jan 24, 2008, at 12:36 PM, Dominique Bessette - Halsema wrote:

> I'm getting the following error, and I think it's because when i
> insert into postgres from geoserver's WFS, i first delete then
> insert the track, and geoserver does this in succession but very
> fast.  If I personally test inserting two tracks with the same guid
> in a row, then it works, but because geoserver is going so fast it
> blows up and gives this error.  i'm looking into fixing it another
> way but does any one have an idea of anything i can do to avoid
> this error in postgres?
>
> STATEMENT: INSERT INTO
> "public"."tracks" ("guid","classification","creationtimestamp","lastup
> datetime","effectivetime","reportedtime","staletime","confidencevalue"
> ,"behavioridentity","nationality","classificationcategory","classconfi
> dencevalue","axislabels","uomlabels","aoumajaxis","aouminaxis","aouval
> id","the_geom") VALUES ('OOSTZEE','U','2008-01-22T21:05:
> 15.366Z','2008-01-22T21:05:15.366Z','2008-01-22T21:05:15.366Z','2008-0
> 1-22T21:05:15.366Z','2008-01-22T21:05:15.366Z',
> 1.0,'FRIEND','','SEA','1.0','Lat Long h','degree degree',
> 9999999,9999999,'true',setSRID
> ('00000000014029D8E757928E0D404B9C28CBD1244A'::geometry,4326))
>
> ERROR: duplicate key violates unique constraint "tracks_guid_key"
>

Perhaps you haven't wrapped these successive DELETE -> INSERT
statements in a transaction?

BEGIN;
DELETE ...;
INSERT ...;
COMMIT;

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com