When using drag and drop to copy a table to another schema in a PostGIS database, the structure of the table is incorrectly copied. There are issues with:
sequences
PKs (new syntax)
other constraints
Steps to reproduce the issue
Have a postgis connexion
create this table
create table gis_data(
id bigint generated always as identity primary key,
geom geometry(Polygon, 3857) check (st_
isvalid(geom)),
name text unique, author text not null
);
observe the structure from psql with \d gis_data
Table "public.gis_data"
Column │ Type │ Collation │ Nullable │ Default
════════╪════════════════════════╪═══════════╪══════════╪══════════════════════════════
id │ bigint │ │ not null │ generated always as identity
geom │ geometry(Polygon,3857) │ │ │
name │ text │ │ │
author │ text │ │ not null │
Indexes:
"git_data2_pkey" PRIMARY KEY, btree (id)
"git_data2_name_key" UNIQUE CONSTRAINT, btree (name)
Check constraints:
"git_data2_geom_check" CHECK (st_isvalid(geom))
create a new postgis schema (from qgis or from psql) create schema test;
drag & drop the table to the new schema test
observe the new table structure \d test.gis_data
Table "test.gis_data"
Column │ Type │ Collation │ Nullable │ Default
════════╪════════════════════════╪═══════════╪══════════╪══════════════════════════════════════════════
id_0 │ integer │ │ not null │ nextval('test.gis_data_id_0_seq'::regclass)
geom │ geometry(Polygon,3857) │ │ │
id │ bigint │ │ │
name │ character varying │ │ │
author │ character varying │ │ │
Indexes:
"gis_data_pkey" PRIMARY KEY, btree (id_0)
observe the differences:
qgis didn't pick the new way of defining auto-incrementing id (generated always as identity) and created a new PK. I've also tested with serial, and while QGis did detect the PK in this case, it also fails to generate a new sequence for the new table, meaning the old and the new PKS are sharing a sequence (this will bring trouble)
qgis completely failed to copy other constraints, including not null, unique and check.
What is the bug or the crash?
When using drag and drop to copy a table to another schema in a PostGIS database, the structure of the table is incorrectly copied. There are issues with:
Steps to reproduce the issue
\d gis_datacreate schema test;test\d test.gis_datagenerated always as identity) and created a new PK. I've also tested with serial, and while QGis did detect the PK in this case, it also fails to generate a new sequence for the new table, meaning the old and the new PKS are sharing a sequence (this will bring trouble)not null,uniqueandcheck.Versions