I want to know if there is a way to only allow certain Geometry types to be added to a table in PostgreSQL.

I have a tool that currently allows points, lines and polygons but would like to restrict it so that only points are allowed but am not sure how to do this in pgAdmin.

  • 1
    Suggest commenters review: meta.stackexchange.com/conduct – Jay Cummins Aug 16 at 10:51
  • You should fix your tool first. Otherwise if the DB doesn't comply with the tool expectations you will have errors that might not be handled properly by the tool (crash, erratic behavior etc) – JGH Aug 16 at 12:03
up vote 4 down vote accepted

You can change the underlying column type. It is likely a geometry column, that you can change to a geometry(point) column.

Let's note that the type change will work only if there is only point in the column, so you would have to clean your current data first:

Create table public.a(  
    id integer,
    geom geometry
); 

insert into public.a values (1, st_geomFromText('point(1 1)',4326));
insert into public.a values (2, st_geomFromText('LINESTRING(2 3, 3 4)',4326)); 


Alter table public.a alter column geom type geometry(point,4326); 
--ERROR:  Geometry type (LineString) does not match column type (Point)


DELETE from public.a where id =2;

Alter table public.a alter column geom type geometry(point,4326); 

--OK

Alternatively, you could create a trigger on before insert/update that ensure the new geometry is indeed a point and throws an error otherwise.

An additional solution to my question is to create a geom_point geometry column and then just use this column to add point data:

CREATE TABLE public.test1
(
    gid integer NOT NULL DEFAULT nextval('test1_gid_seq'::regclass),
    description text COLLATE pg_catalog."default",
    item_id integer,
    item_class text COLLATE pg_catalog."default",
    origin_x double precision,
    origin_y double precision,
    origin_z double precision,
    geom_point geometry(Point,27700),
    CONSTRAINT test1_pkey PRIMARY KEY (gid)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.test1
    OWNER to tommy;



CREATE INDEX test1_geom_point_sidx
    ON public.test1 USING gist
    (geom_point)
    TABLESPACE pg_default;

And if you are updating an existing table you will need to create the geom_point column and copy the existing geometry into the new column.

ALTER TABLE public.test1
   ADD COLUMN  geom_point geometry(POINT, 27700);



CREATE INDEX test1_geom_point_sidx
    ON public.test1 USING gist
    (geom_point)
    TABLESPACE pg_default;


UPDATE test1 SET geom_point = geometry;

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.