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 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 you can write the following instead:

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

This is the proper way to do things as per the SQL standard.