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.

Answer 1

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" (...)}

Answer 2

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.