In short, I want to split an existing spatial table into two parts. The split itself is straight forward. I start with this (fully functioning) table:
CREATE TABLE table ( my_identifier smallint, geom geometry, CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2), CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 4326) ) WITH ( OIDS=TRUE );
and create the new tables by:
CREATE TABLE public.table_part1 AS SELECT * FROM public.table WHERE idmy_identifier = 1; CREATE TABLE public.table_part2 AS SELECT * FROM public.table WHERE my_identifier = 2;
However, the problem is that although the geom column gets transferred to my new tables, the geometry (?) itself doesn't get transferred correctly:
CREATE TABLE table_part1 ( my_identifier smallint, geom geometry ) WITH ( OIDS=FALSE );
I tried running
SELECT Probe_Geometry_Columns(); but it didn't help. Do I need to insert data into the geometry_columns table? If so, exactly what?