Adding Unique Constraint to Geography Type

Question

I'm just getting into Postgis and I'm running into an interesting problem:

I've added a unique constraint to my table between a varchar name column with a generic geography geom column:

CREATE TABLE public.locations
(
    id uuid NOT NULL,
    name character varying(255) COLLATE pg_catalog."default",
    geom geography,
    inserted_at timestamp(0) without time zone NOT NULL,
    updated_at timestamp(0) without time zone NOT NULL,
    CONSTRAINT locations_pkey PRIMARY KEY (id)
)

I've added in a unique constraint using btree

CREATE INDEX locations_geom_index
    ON public.locations USING btree
    (geom ASC NULLS LAST)
    TABLESPACE pg_default;
-- Index: locations_name_geom_index

-- DROP INDEX public.locations_name_geom_index;

CREATE UNIQUE INDEX locations_name_geom_index
    ON public.locations USING btree
    (name COLLATE pg_catalog."default" ASC NULLS LAST, geom ASC NULLS LAST)
    TABLESPACE pg_default;

It looks like the unique index is not being respected. I read online that I need to use a GIST index (but that won't allow unique values). How can I properly add a unique constraint so I can be sure that something with the same name and GPS location won't be duplicated?
Since I will be storing points, should I change this to be a geography(Point, 4326)?

The second index will most assuredly guarantee that no two rows can have the same name **and** the same geometry. Perhaps that is not what you are after. Perhaps there is a difference in the `double precision` coordinates that is to small for display. — Laurenz Albe, Jul 15 '20 at 05:59
I wrote an automated test in my app code, and it seems that i'm still able to create this. So I'm pretty sure that my index must be wrong. I can try to do this by hand and see if that works or not. — Allen, Jul 15 '20 at 08:27
Yes, please. I won't believe it without seeing a counterexample. — Laurenz Albe, Jul 15 '20 at 08:28
The index is respected as long as the name and geometry are not null. Yes, restricting the geometry column to a specific type and crs is a good safeguard — JGH, Jul 15 '20 at 12:05

Adding Unique Constraint to Geography Type

0 Answers0