Reset auto increment counter in postgresql

Question

I would like to force the auto increment field of a table to some value, unfortunately my query seems to fail

ALTER SEQUENCE categories_categoryid_seq RESTART WITH 1;
ERROR:  relation "your_sequence_name" does not exist

My table categories has the following columns:

categoryid
functions
name

Edit: my Create query:

-- Table: public.categories

-- DROP TABLE public.categories;

CREATE TABLE public.categories
(
    categoryid bigint NOT NULL,
    functions character varying(255) COLLATE pg_catalog."default" NOT NULL,
    name character varying(255) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT categories_pkey PRIMARY KEY (categoryid)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.categories
    OWNER to postgres;

are you sure it's the name of the sequence? have you checked it with pgAdmin or similar tools? if not, try to check it with a query — Bagus Tesa
– Bagus Tesa, Commented Dec 15, 2016 at 10:36
Yea, pgadmin is open. As an example the following query works perfectly: SELECT * FROM public.categories ORDER BY categoryid ASC — user3657270
– user3657270, Commented Dec 15, 2016 at 10:38
Apparently there is no sequence with that name. edit your question and show us the complete create table statement for categories and the create statement for the sequence. — user330315
– user330315, Commented Dec 15, 2016 at 10:40
The column is defined without a default value and not as a serial. Why do you expect the sequence to exist in the first place? (Also: limiting a varchar to 255 character has no performance or storage advantages at all. 255 is not some magic limit that enables faster processing) — user330315
– user330315, Commented Dec 15, 2016 at 10:49

SQB · Accepted Answer · 2016-12-15 12:47:11Z

0

There are no sequences is created in this above questions because you are not added serial or sequence :(as per script). bigint must be bigserial or serial.

If you want to check it the sequence is created or not run this script.

Select column_default 
from information_schema.columns 
where table_name = 'categories' and column_name = 'categoryid';

edited Dec 15, 2016 at 12:47

SQB

4,1262 gold badges34 silver badges53 bronze badges

answered Dec 15, 2016 at 11:01

Saddam Khan

18014 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Laurenz Albe · Accepted Answer · 2016-12-15 10:56:44Z

0

You can verify the name sequence defined in the DEFAULT clause:

SELECT column_name, column_default
FROM information_schema.columns
WHERE table_schema = 'myschema'
   AND table_name = 'categories'
ORDER BY ordinal_position;

Then you'll know which sequence to reset!

edited Dec 15, 2016 at 10:56

answered Dec 15, 2016 at 10:39

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

2 Comments

user3657270 Over a year ago

with the following query: FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'categories' AND column_name LIKE 'categoryid' I got my result: categoryid but still... the sequence is not correct

Laurenz Albe Over a year ago

@user3657270 the important information is in the column_default column. What do you get there?

Collectives™ on Stack Overflow

Reset auto increment counter in postgresql

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related