Bug report #6798
when copying a table by d&d from one schema to another the serial property is lost in the pk
Status: | Closed | ||
---|---|---|---|
Priority: | Normal | ||
Assignee: | - | ||
Category: | DB Manager | ||
Affected QGIS version: | master | Regression?: | No |
Operating System: | Easy fix?: | No | |
Pull Request or Patch supplied: | No | Resolution: | end of life |
Crashes QGIS or corrupts data: | No | Copied to github as #: | 15946 |
Description
When copying (by d&d) a PG table from one schema to another, it seems that the primary key column loses the autocremental property (in the new table), resulting in a error when adding features.
Related issues
History
#1 Updated by Paolo Cavallini almost 12 years ago
- Assignee changed from Giuseppe Sucameli to anonymous -
#2 Updated by Jürgen Fischer over 11 years ago
- Assignee deleted (
anonymous -)
#3 Updated by Giovanni Manghi over 11 years ago
it happens only when in the dialog the user does not specify the primary key by enabling the "primary key" checkbox, so while it a very boring bug for real work, it is not that critical.
#4 Updated by Paolo Cavallini over 11 years ago
Why not setting the box checked by default?
#5 Updated by Giovanni Manghi over 10 years ago
- Target version changed from Version 2.0.0 to Version 2.4
#6 Updated by Jürgen Fischer over 10 years ago
- Target version changed from Version 2.4 to Future Release - High Priority
#7 Updated by Giovanni Manghi almost 9 years ago
- Subject changed from primary key issue when copying a table from one schema to another to when copying a table by d&d from one schema to another the serial property is lost in the pk
- Target version deleted (
Future Release - High Priority)
Giovanni Manghi wrote:
it happens only when in the dialog the user does not specify the primary key by enabling the "primary key" checkbox, so while it a very boring bug for real work, it is not that critical.
this workaround does not work anymore.
#8 Updated by Bo Victor Thomsen almost 9 years ago
A more specific explanation of the problem:
A "serial" data type in PostgreSQL is syntactic sugar for "integer NOT NULL DEFAULT nextval('some_sequence'::regclass)", combined with the implicit creation of the sequence used in the DEFAULT clause.
So the D&D function do create the correct base type for the column, but fails to create the corresponding sequence and the default clause for the column.
#9 Updated by Paul Kanelli over 8 years ago
See also #14104
#10 Updated by Paul Kanelli over 8 years ago
This issue is quite annoying and it's unfixed for 3 years. Can someone set the priority to high or blocker?
#11 Updated by Giovanni Manghi over 8 years ago
Paul Kanelli wrote:
This issue is quite annoying and it's unfixed for 3 years. Can someone set the priority to high or blocker?
we usually don't raise priority based on age of tickets. Blockers are regressions, and high priority is usually reserved for issues causing crash or data corruption (that are not regressions). This issue does not fit either.
#12 Updated by Paul Kanelli over 8 years ago
Thx for the info. Here comes the workaround:
SELECT S E T V A L('<schema><table>_<column>_seq', (SELECT M A X(<column>) FROM <schema>.<table>));
ALTER TABLE <schema>.<table>
ALTER COLUMN <column> SET DEFAULT nextval('<schema><table>_<column>_seq'::regclass);
Please ignore the spaces in SETVAL and MAX.
Example:
SELECT S E T V A L('public.buildings_gid_seq', (SELECT M A X(gid) FROM public.buildings));
ALTER TABLE public.buildings
ALTER COLUMN gid SET DEFAULT nextval('public.buildings_gid_seq'::regclass);
#13 Updated by Paul Kanelli over 8 years ago
- Assignee set to Giuseppe Sucameli
#14 Updated by Paul Kanelli over 8 years ago
- Assignee deleted (
Giuseppe Sucameli)
#15 Updated by Jason Wheatley over 8 years ago
Paul - Thanks for the work around. I'll have to test that.
This is definitely an annoying bug. I only found this out recently when we decided to switch to PostGIS/QGIS as our main production workflow. Anytime you need to move a table from one location to another, you have to create a new primary key column (check primary key box and type in a new column name) otherwise you are unable to add any new records to those imported tables.
I would love to see this one fixed!
#16 Updated by Giovanni Manghi over 8 years ago
Paul Kanelli wrote:
Thx for the info. Here comes the workaround:
SELECT S E T V A L('<table>_<column>_seq', (SELECT M A X(<column>) FROM <schema>.<table>));
ALTER TABLE <schema>.<table>
ALTER COLUMN <column> SET DEFAULT nextval('<table>_<column>_seq'::regclass);Please ignore the spaces in SETVAL and MAX.
if this is a patch that can be applied in DB manager code please submit it via pull request in the QGIS github repository.
#17 Updated by Paul Kanelli over 8 years ago
Hi Giovanni, it's just a SQL query to sync the primary key. I've no programming skills.
Related: #14712
#18 Updated by R. R. over 8 years ago
Is there any chance that we get this issue fixed soon?
#19 Updated by R. R. over 8 years ago
(In some cases the name of the primary key is placed in quotes when copying a table by D&D (see also: #15226). After removing the quotes in the "Import vector layer" dialog, adding new features works properly. To me, this is really mysterious.)
EDIT: fixed
#20 Updated by R. R. about 8 years ago
In QGIS 2.18.0 the sequence is completely missing after copying a table by D&D, but maybe this is better than a broken seuqence.
#21 Updated by R. R. about 8 years ago
- File bug_6798_qgis_2_18.mp4 added
#22 Updated by R. R. almost 8 years ago
See also: #15991
#23 Updated by R. R. almost 8 years ago
- File 6798_rename_primary_key.mp4 added
It seems that renaming the primary key is the only way to create a working sequence (see screencast 6798_rename_primary_key.mp4).
#24 Updated by R. R. almost 8 years ago
- File 6798_create_sequence.mp4 added
- File pg_create_sequence_qgis_2x.py added
Here comes a workaround for the missing sequence ...created with a lot of help from gis.stackexchange.com.
#25 Updated by Giovanni Manghi almost 8 years ago
Reinhard Reiterer wrote:
Here comes a workaround for the missing sequence ...created with a lot of help from gis.stackexchange.com.
it would be nice of you can do a PR to add this script to
#26 Updated by R. R. over 7 years ago
- File 6798_create_sequence_version_2.mp4 added
- File pg_create_sequence_qgis_2x_version_2.py added
Hi Giovanni, I will check GitHub as soon as I find some time. At the moment, I do not know how a PR works. However, I have implemented a small improvement. There is no longer need to reload the layer manually.
#27 Updated by Giovanni Manghi over 7 years ago
- Regression? set to No
- Easy fix? set to No
#28 Updated by Giovanni Manghi over 5 years ago
- Status changed from Open to Closed
- Resolution set to end of life
End of life notice: QGIS 2.18 LTR
Source:
http://blog.qgis.org/2019/03/09/end-of-life-notice-qgis-2-18-ltr/