[postgis-users] Small question about GEOMETRY typmod registration in geometry_columns

[Jorge Gustavo Rocha]

Hi,

To prevent an improper registration of GEOMETRY columns in the
geometry_columns view, I'm using the following workaround:

CREATE TABLE place (
	id uuid NOT NULL DEFAULT uuid_generate_v1mc() PRIMARY KEY,
	name varchar(255) not NULL,
	geom geometry(GEOMETRY,3763) NOT null,
	CONSTRAINT line_or_polygon CHECK (geometrytype(geom) in ('GEOMETRY',
'LINESTRING', 'POLYGON'))
);

select f_table_name, type from geometry_columns gc where f_table_name =
'place';

f_table_name|type    |
------------|--------|
place       |GEOMETRY|

Can this workaround have any adverse side effect?

With this CONSTRAINT, I'm able to insert just POLYGON or LINESTRING
features. The geometry_columns view type is GEOMETRY, so applications
like QGIS can ask the use if he wants to use a LINESTRING layer or
POLYGON layer.

Regards,

Jorge Gustavo

On 03/05/20 23:05, Jorge Gustavo Rocha wrote:
> Hi Postgisers,
> 
> If I create a new table with a generic GEOMETRY column, it is properly
> registered in geometry_columns view as type GEOMETRY.
> 
> Example:
> 
> CREATE TABLE place (
> 	id uuid NOT NULL DEFAULT uuid_generate_v1mc() PRIMARY KEY,
> 	name varchar(255) not NULL,
> 	geom geometry(GEOMETRY,3763) NOT NULL
> );
> 
> select f_table_name, type from geometry_columns gc where f_table_name =
> 'place';
> 
> Perfect, as expected!
> 
> f_table_name|type    |
> ------------|--------|
> place       |GEOMETRY|
> 
> Problem
> 
> I want to restrict this generic GEOMETRY column just to LINESTRING and
> POLYGON geometries.
> 
> To do so, I use the same typmod geometry(GEOMETRY,3763) and an
> additional CONSTRAINT, like:
> 
> CREATE TABLE place (
> 	id uuid NOT NULL DEFAULT uuid_generate_v1mc() PRIMARY KEY,
> 	name varchar(255) not NULL,
> 	geom geometry(GEOMETRY,3763) NOT null,
> 	CONSTRAINT line_or_polygon CHECK (geometrytype(geom) = 'LINESTRING' OR
> geometrytype(geom) = 'POLYGON')
> );
> 
> If I do so, the column is registered as LINESTRING and not GEOMETRY.
> 
> select f_table_name, type from geometry_columns gc where f_table_name =
> 'place';
> 
> f_table_name|type      |
> ------------|----------|
> place       |LINESTRING|
> 
> For me, it would make sense to have it registered as GEOMETRY, not
> LINESTRING (or POLYGON). Shouldn't it be based just on the typmod?
> 
> Best regards,
> 
> J. Gustavo
> 

J. Gustavo
-- 
Jorge Gustavo Rocha
Departamento de Informática
Universidade do Minho
4710-057 Braga
Gabinete 3.29 (Piso 3)
Tel: +351 253604480
Fax: +351 253604471
Móvel: +351 910333888
skype: nabocudnosor