Postgres truncate restart identity doesn't restart identity

Question

Pretty much as the topic says.

truncate table "Account" restart identity cascade;
insert into "Account" ("name", "enabled") values ("test", 1);
select * from "Account";

Outputs:

 accountId | name | enabled
-----------+------+---------
        14 | test |       1
(1 row)

This is the schema for the table:

                                       Table "public.Account"
  Column   |          Type          |                           Modifiers
-----------+------------------------+---------------------------------------------------------------
 accountId | integer                | not null default nextval('"Account_accountId_seq"'::regclass)
 name      | character varying(255) | not null
 enabled   | integer                | not null
Indexes:
    "Account_pkey" PRIMARY KEY, btree ("accountId")
Referenced by:
    TABLE ""AccountPropertyAccess"" CONSTRAINT "AccountPropertyAccess_accountId_fkey" FOREIGN KEY ("accountId") REFERENCES "Account"("accountId")
    TABLE ""User"" CONSTRAINT "User_accountId_fkey" FOREIGN KEY ("accountId") REFERENCES "Account"("accountId")

And here are some extra words because stack exchange thinks I don't have enough words because I have too much code.

Your insert statement in the first code block is invalid. String literals have to use single quotes `"test"` denotes a column, not a value. — , Jun 25 '14 at 19:12

score 7 · Accepted Answer · answered Jun 25 '14 at 19:15

It seems you did not create the column as serial column and thus Postgres does not know that the sequence "belongs" to the column and therefore "restart identity" doesn't reset the sequence.

You can fix that by either re-creating the table using serial instead of integer and a default value.

Or you can just tell Postgres that the column "owns" the sequence:

alter sequence "Account_accountId_seq" owned by "Account"."accountId";

^{Btw: using quoted identifiers is usually much more trouble than it's worth (at leas in my experience). Most of the time it's better to never use quoted identifiers at all, e.g. create table Account (...) instead of create table "Account" (...)}

score 3 · Answer 2 · edited May 23 '17 at 11:45

3

It looks like you have an auto increment sequence id. You will need to reset it too.

The PostgreSQL documentation shows

ALTER SEQUENCE Account RESTART WITH 1;

There are a few questions here and here regarding it.

edited May 23 '17 at 11:45

Community

1
1

answered Jun 25 '14 at 19:11

Timothy Brown

2,220
18
22

score 2 · Answer 3 · answered Aug 12 '21 at 12:14

2

I have found easier way

TRUNCATE TABLE Account RESTART IDENTITY;

answered Aug 12 '21 at 12:14

Stefano Mtangoo

6,017
6
47
93

Postgres truncate restart identity doesn't restart identity

3 Answers3