In PostgreSQL, use the ALTER SEQUENCE command, or the setval function.

ALTER SEQUENCE tablename_colname_seq RESTART WITH 1;

Resetting All Sequences

The following SQL query generates an SQL statement that allows resetting all sequences from a database to the current maximum value found in the column the sequence is attached to:

SELECT 'select ' 
        || trim(trailing ')' 
           from replace(pg_get_expr(d.adbin, d.adrelid),
                        'nextval', 'setval'))
        || ', (select max( ' || a.attname || ') from only '
        || nspname || '.' || relname || '));' 
  FROM pg_class c 
       JOIN pg_namespace n on n.oid = c.relnamespace 
       JOIN pg_attribute a on a.attrelid = c.oid
       JOIN pg_attrdef d on d.adrelid = a.attrelid 
                          and d.adnum = a.attnum
                          and a.atthasdef 
 WHERE relkind = 'r' and a.attnum > 0 
       and pg_get_expr(d.adbin, d.adrelid) ~ '^nextval';

See the article Resetting sequences on <tapoueh.org> for more details.

Sequences and Identity

The SQL standard has no notion of an auto increment. It provides a unique non-transactionnal object named a SEQUENCE that implements the notion in a careful way.

The SQL standard also includes the notion of an identity column, which is supported starting in PostgreSQL 10. Read PostgreSQL 10 identity columns explained for more information about them.

Serial and BigSerial

When using PostgreSQL, use the Serial Types to implement the auto_increment facility found in MySQL.

The serial and bigserial pseudo-types create a SEQUENCE object and attach it to your table as a default value, as in the following example:

CREATE TABLE tablename (
    colname SERIAL
);

This is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Once you have a sequence, the nextval() function returns the next available integer from the sequence each time it’s called, and the SEQUENCE is updated immediately.

That contrast to usual transaction boundaries, where SQL objects are only affected at COMMIT time, and where a ROLLBACK could cancel the whole processing. A SEQUENCE object must be able to deliver integers to all concurrent clients that need one, and so doesn’t respect transaction boundaries.

SQL Standard and Identity

Starting in PostgreSQL 10, GENERATED ... AS IDENTITY is the SQL-standard way to do what serial has always done as a PostgreSQL-specific convenience — and it’s the syntax to reach for by default in new schema today, serial included.

CREATE TABLE test_new (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    payload text
);

Two variants, and the difference matters:

• GENERATED ALWAYS AS IDENTITY — the database generates the value. Supplying your own value in an INSERT is an error unless you explicitly say OVERRIDING SYSTEM VALUE. Use this when nothing should ever hand-pick an id — which is most of the time.
• GENERATED BY DEFAULT AS IDENTITY — the database generates the value by default, but an explicit value in the INSERT is accepted and used. This is the one that actually matches serial’s permissive behavior, and it’s the one you want when migrating data that needs to preserve its original ids (a pgloader migration inserting rows with their existing MySQL AUTO_INCREMENT values, for instance).

Why prefer this over serial in new schema: serial is really just sugar that creates a plain sequence and a default expression pointing at it — the sequence’s ownership and permissions end up as a slightly separate, easy-to-misconfigure object. An identity column is a first-class property of the column itself, defined by the standard, and it’s what \d tablename in psql will show you as identity rather than a default-expression default. Same practical result, cleaner underlying model.