Hi everyone,
Hi everyone,
I need help to set a sequence value.
I use the following query to list sequences in the database and it works returning the list of available sequences on the database.
SELECT sequence_schema, sequence_name
FROM information_schema.sequences
ORDER BY sequence_name;
However the query to set the next value on the sequence:
SELECT setval('Actuacion_id_seq', 12345, true);
...fails with the following error:
ERROR: relation "actuacion_id_seq" does not exist (SQLSTATE 42P01)
Can you help me with the correct query to set the next sequence value?
10 Replies
ratty-blush•16mo ago
Hmm, what happens if you use double quotes instead of single quotes
SELECT setval("Actuacion_id_seq", 12345, true);
You have to use double quotes, otherwise Postgres will automatically lowercase the table name you specifygenetic-orange•16mo ago
I've tried using double quotes and I got the same error... do you have any other suggestion?
ratty-blush•16mo ago
hmm, what happens when you go to the tables page? Do you see the table there?
One alternative to show tables is to run
\dt in the Neon Console's SQL editorgenetic-orange•16mo ago
As expected, it returns the list of the database's tables
List of relations
Schema Name Type Owner
public Actuacion table oscarsaraza
public Adjunto table oscarsaraza
... and another 12 rows....
ratty-blush•16mo ago
hmm, that's really odd, because I just tried the code you shared and I wasn't able to reproduce the issue
@Rishi Raj Jain perhaps you have an idea what's happening?
genetic-orange•16mo ago
Maybe a little more context helps: the database is used on a Next.js project, I'm using the T3 stack with Prisma to handle database schema and database generation.
Could it be a Prisma related issue?
ratty-blush•16mo ago
interesting. I found this answer in a GitHub issue https://github.com/prisma/prisma/discussions/22718
If you are using Prisma with a PostgreSQL database for example, you can modify the starting value of an autoincrement sequence when you are creating a migration. The process involves manually editing the migration file generated by Prisma because it is not possible to do this within the Schema file. The steps you would take are outlined below: Define your model in the schema.prisma file and include an @id field with @default(autoincrement()). Generate a migration with the prisma migrate dev --create-only command, which creates a new migration file without applying it. Edit the generated SQL migration file in the prisma/migrations folder to include the desired starting value for the autoincrement sequence. Apply the migration with the prisma migrate dev command. For PostgreSQL, the SQL command to alter the sequence might look something like this: ALTER SEQUENCE "YourModel_id_seq" RESTART WITH 1000000;
GitHub
How to set AUTO_INCREMENT · prisma prisma · Discussion #22718
Problem I want set AUTO_INCREMENT start with 1000000 Suggested solution // like this? model Order { id Int @id @default(autoincrement(100000)) } Alternatives CREATE TABLE
orders ( order_num int...ratty-blush•16mo ago
But I don't understand, why it needs a migration though 🤔
genetic-orange•16mo ago
I have tried with ALTER SEQUENCE "Actuacion_id_seq" RESTART WITH 70000; and it worked as expected.
The newly inserted rows started with id 70000. 👍
Thank you very much for your help.
wise-white•16mo ago
Thank you for raising this, @Ossar!
@Mahmoud you rock.