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](http://stackoverflow.com/questions/1493262/list-all-sequences-in-a-postgres-db-8-1-with-sql) — Bagus Tesa, Dec 15 '16 at 10:36
Yea, pgadmin is open. As an example the following query works perfectly: SELECT * FROM public.categories ORDER BY categoryid ASC — user3657270, Dec 15 '16 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. — a_horse_with_no_name, Dec 15 '16 at 10:40
there's no `CREATE sequence`... and you don't use `bigserial` for `categoryid ` — Vao Tsun, Dec 15 '16 at 10:49
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) — a_horse_with_no_name, Dec 15 '16 at 10:49
@a_horse_with_no_name Still a newb in this.. I'll dig deeper on it, thx for your answer ;) — user3657270, Dec 15 '16 at 10:54

Laurenz Albe · Answer 1 · 2016-12-15T10:56:44.377

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 '16 at 10:56

answered Dec 15 '16 at 10:39

Laurenz Albe

129,316
15
96
132

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 – user3657270 Dec 15 '16 at 10:44
@user3657270 the important information is in the `column_default` column. What do you get there? – Laurenz Albe Dec 15 '16 at 10:57

score 0 · Accepted Answer · edited Dec 15 '16 at 12:47

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 '16 at 12:47

SQB

3,583
1
24
44

answered Dec 15 '16 at 11:01

Saddam Khan

180
13

Reset auto increment counter in postgresql

2 Answers2