I'm transitioning from MySQL to PostgreSQL 10, and am using Datagrip.
I made my columns that were previously auto-incremented in MySQL into integer primary keys in PostgreSQL. I then ran:
ALTER TABLE test_new
ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;
for each relevant table. Now, I'm getting problems when I try to insert a new row. Mostly that I am violating the constraint of the primary key.
It seems that a sequence is out of order. When I generated the DDL code, it looks like my column is actually converted to a serial with a sequence..
id serial not null
Am I understanding this incorrectly? And shouldn't the identity generate the correct sequence? If I am understanding this incorrectly, how can I update my sequence so that I can insert data..