Postgresql update sequence start value

Find centralized, trusted content and collaborate around the technologies you use most. Connect and share knowledge within a single location that is structured and easy to search. Otherwise you're calling setval with a single argument, while it requires two or three. Note that setval expects either regclass, bigint or regclass, bigint, boolean.

In the above example I am providing untyped literals. That works too. But if you feed typed variables to the function you may need explicit type casts to satisfy function type resolution. You need Postgres 8. The better way to avoid hard-coding of sequence name, next sequence value and to handle empty column table correctly, you can use the below way:.

I don't try changing sequence via setval. And this only work for me:. Also there is a page on wiki. Below the text from link:. Run the file and save its output in a way that doesn't include the usual headers, then run that output. How are we doing? Please help us improve Stack Overflow. Take our short survey. Stack Overflow for Teams — Collaborate and share knowledge with a private group.

Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Postgres manually alter sequence Ask Question. If unspecified, the old cache value will be maintained. The optional CYCLE key word can be used to enable the sequence to wrap around when the maxvalue or minvalue has been reached by an ascending or descending sequence respectively. If the limit is reached, the next number generated will be the minvalue or maxvalue , respectively.

If the optional NO CYCLE key word is specified, any calls to nextval after the sequence has reached its maximum value will return an error.

The OWNED BY option causes the sequence to be associated with a specific table column, such that if that column or its whole table is dropped, the sequence will be automatically dropped as well. If specified, this association replaces any previously specified association for the sequence. The specified table must have the same owner and be in the same schema as the sequence.

They will use up all cached values prior to noticing the changed sequence generation parameters. The current backend will be affected immediately. Before PostgreSQL 8. The sequence restarts the numbering series with and increments by 50 every time that a number is generated.

The value returned of -9,,,,,, is the lowest possible value for the bigint data type. To correct this error, the owner executes the following statement. Subsequent calls to the sequence object will return error The following statement changes the sequence object to cycle and sets a cache of Now when the sequence object reaches 9,,,,,, it will cycle, and the next number after cycling will be the minimum of the data type, -9,,,,,, The owner realized that the bigint data type uses 8 bytes each time it is used.

The int data type that uses 4 bytes is sufficient. However the data type of a sequence object cannot be altered. To change to an int data type, the owner must drop the sequence object and recreate the object with the correct data type. Skip to main content.