CREATE SEQUENCE my_tab_id_seq;
 CREATE TABLE my_tab (
   id bigint PRIMARY KEY DEFAULT nextval('my_tab_id_seq'::regclass),
   ...
);
 ALTER SEQUENCE my_tab_id_seq OWNED BY my_tab.id;

CREATE TABLE my_tab (
   id bigserial PRIMARY KEY,
   ...
);

CREATE TABLE my_tab (
   id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   ...
);

putong-test=# CREATE TABLE my_tab (
   id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   a text
);
CREATE TABLE

putong-test=# insert into my_tab(a) values ('adsf');
INSERT 0 1
putong-test=# insert into my_tab(a) values ('adsf');
INSERT 0 1
putong-test=# insert into my_tab(a) values ('adsf');
INSERT 0 1
putong-test=# select * from my_tab;
 id |  a
----+------
  1 | adsf
  2 | adsf
  3 | adsf
(3 rows)

putong-test=# insert into my_tab (id , a) values (4, 'asdf');
ERROR:  cannot insert into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.
putong-test=# insert into my_tab (id , a) OVERRIDING SYSTEM VALUE values (4, 'asdf');
INSERT 0 1
putong-test=# insert into my_tab(a) values ('adsf');
ERROR:  duplicate key value violates unique constraint "my_tab_pkey"
DETAIL:  Key (id)=(4) already exists.

CREATE OR REPLACE FUNCTION sequence_values() RETURNS TABLE(name text, value bigint) AS $sequence_values$
DECLARE
   nsp_name TEXT;
   seq_name TEXT;
BEGIN
   FOR nsp_name, seq_name IN
       SELECT nspname::text, relname::text
          FROM pg_class 
          JOIN pg_namespace
          ON pg_class.relnamespace = pg_namespace.oid WHERE relkind='S'
   LOOP
       RETURN QUERY EXECUTE 'SELECT ''' || nsp_name || '.' || seq_name || '''::text, last_value FROM "' || nsp_name || '"."' || seq_name || '"';
   END LOOP;
END;
$sequence_values$
LANGUAGE plpgsql;

SELECT 'SELECT SETVAL(' ||
       quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
       ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
       quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
     pg_depend AS D,
     pg_class AS T,
     pg_attribute AS C,
     pg_tables AS PGT
WHERE S.relkind = 'S'
    AND S.oid = D.objid
    AND D.refobjid = T.oid
    AND D.refobjid = C.attrelid
    AND D.refobjsubid = C.attnum
    AND T.relname = PGT.tablename
ORDER BY S.relname;

SELECT 'ALTER SEQUENCE '|| quote_ident(MIN(schema_name)) ||'.'|| quote_ident(MIN(seq_name))
       ||' OWNED BY '|| quote_ident(MIN(TABLE_NAME)) ||'.'|| quote_ident(MIN(column_name)) ||';'
FROM (
    SELECT 
        n.nspname AS schema_name,
        c.relname AS TABLE_NAME,
        a.attname AS column_name,
        SUBSTRING(d.adsrc FROM E'^nextval\\(''([^'']*)''(?:::text|::regclass)?\\)') AS seq_name 
    FROM pg_class c 
    JOIN pg_attribute a ON (c.oid=a.attrelid) 
    JOIN pg_attrdef d ON (a.attrelid=d.adrelid AND a.attnum=d.adnum) 
    JOIN pg_namespace n ON (c.relnamespace=n.oid)
    WHERE has_schema_privilege(n.oid,'USAGE')
      AND n.nspname NOT LIKE 'pg!_%' escape '!'
      AND has_table_privilege(c.oid,'SELECT')
      AND (NOT a.attisdropped)
      AND d.adsrc ~ '^nextval'
) seq
GROUP BY seq_name HAVING COUNT(*)=1;

SELECT ns.nspname AS schema_name, seq.relname AS seq_name
FROM pg_class AS seq
JOIN pg_namespace ns ON (seq.relnamespace=ns.oid)
WHERE seq.relkind = 'S'
  AND NOT EXISTS (SELECT * FROM pg_depend WHERE objid=seq.oid AND deptype='a')
ORDER BY seq.relname;