Reset auto increment counter in postgresql

1
votes

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;
2
sqlpostgresqlauto-incrementreset
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
prepend schema... - Vao Tsun
Yea, pgadmin is open. As an example the following query works perfectly: SELECT * FROM public.categories ORDER BY categoryid ASC - user3657270
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
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

2 Answers

0
votes

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';
0
votes

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!