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
_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
