Can't set Default value on primary key to be table_name_id_seq

[nickaus]

Hi,

We are in the process of moving from Retool Cloud to self-hosting. I have a simple table with a normal int4 primary key. However, somewhere in the migration process (export to CSV, import to new table etc), the PK does not have a Default value.

I can see that the sequence I want to use exists:

select * from pg_sequences where sequencename = 'org_team_id_seq'

...returns data so the sequence is there. I tried altering the sequence to set it to the max value in the id column:

ALTER SEQUENCE company_team_id_seq RESTART WITH 342;

However I continue to get the message:

Error: column "id" of relation "company_team" is an identity column

Any ideas? Do I need to recreate the table?

Regards,
Nick

[ThomasW]

Struggling with the same error message. Want to add a column to a table in retool database, making it with auto-increment and than as a primary key. At first the default value with the id_seq is there, but after closing and opening the table the default i gone, and when rewriting it as default I get the error: Error: column "id" of relation "service_log" is an identity column.

[MRConcept]

Struggling with the same error. I am still on Cloud but had trouble setting the primary key and now I can't find a way to reinsert this logic with the seq_id I am getting same error as you are

[Mariusz_Kolaszewski]

Same issue here... unable to insert an auto-incrementing logic into existing primary key field.

Please advise!

[kschirrmacher]

What happens if you set the column type to this?

[Mariusz_Kolaszewski]

Hi @kschirrmacher, thank you for the response.
Unfortunately, this is an existing primary key field and that option is no longer available.

[nickaus]

Hi all,

It's not a solution, but we recently migrated back to Cloud from our self-hosted instance. As part of this I re-created all tables manually. The table which I was having this issue with is now behaving properly. So perhaps the quickest way past this is re-creating the table (if possible). I guess it depends how many foreign keys you may need to patch as a result.

Nick

[kschirrmacher]

That's my easy fix suggestion too. Either create a second table, migrate programmatically and rename or export it, drop the table and re-create.

[ThomasW]

Also fixed it by dropping the table and starting over. Deleting the table in the retool database interface didn't fix it. Had the same problem after creating a new table with the same name. Dropped it with postgrlsql command to make it work.
How/where can I see the status and existence of these autoincrementing sequences?

[nickaus]

Hi @ThomasW

You can query/update from within Retool (a DB resource). For example, checking the contacts.id sequence:

SELECT nextval('contacts_id_seq');

You can also connect to the DB using pgAdmin or similar tool. Screenshot here of my connection via pgAdmin.

The connection details are under Resources / Retool Database -> Connection

Regards,
Nick

[Mariusz_Kolaszewski]

Thank you Nick. This is very helpful!

[PaFi]

This also just "happened" to the primary key column of one of our db tables. I'm getting the same error when trying to update the default value. The table was working just fine last week... very frustrating. The retool hosted db has a history of odd behavior in our experience. What is going on in this case?