How to change id column data type integer to auto-increment Intenger in hasura

Question

I got this error in hasura console:

{ "internal": { "statement": "ALTER TABLE users\nADD id Integer auto-increment;", "prepared": false, "error": { "exec_status": "FatalError", "hint": null, "message": "syntax error at or near \"auto\"", "status_code": "42601", "description": null }, "arguments": [] }, "path": "$[0]", "error": "query execution failed", "code": "postgres-error" }

After checking my table, I find that I don't give the id column to the integer (auto-increment) datatype now id column is attack with another table so I can not delete it so I want to change id column to auto-increment and I use Postgres database with Hasura and graphql

This is my id look like

id - integer, primary key, unique, default: nextval('users_id_seq'::regclass)

Answer 1

If you have not a SEQUENCE firstly you must create sequence for table:

CREATE 
    SEQUENCE test_table_id_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 2147483647
    START 1
    CACHE 1
    NO CYCLE;

After than you can add this sequence to your table:

ALTER TABLE 
    test_table
ALTER COLUMN 
id SET DEFAULT nextval('test_table_id_seq'::regclass);

After than, you must update your sequence value to max(id) of table:

SELECT setval('test_table_id_seq', (SELECT MAX(id)+1 FROM test_table));