0

I have a database with a more than 100 tables. In each table i have column named id (int8 NOT NULL) with primary key. I would like to update whole database (all columns) to change this id type to SERIAL.

The reason is problem with inserting data. I would like to know how to write a procedure which will upadate in one shot all tables.

Rafal
  • 1
  • 1
  • http://stackoverflow.com/questions/9490014/adding-serial-to-existing-column-in-postgres and http://stackoverflow.com/questions/20593946/postgresql-reconfigure-existing-table-changing-primary-key-to-type-serial –  Jan 20 '15 at 17:07
  • `SERIAL` isn't a type. You have to create a sequence, lock the table, alter the table default to use the sequence, update the sequence so the next value is the highest value in the table + 1, and commit. You can use a `DO` block that loops over `information_schema` to find columns and table names, and use PL/PgSQL `EXECUTE format(...)` dynamic queries to make the changes. See the numerous related posts for details. – Craig Ringer Jan 20 '15 at 20:38

1 Answers1

0
CREATE SEQUENCE tablename _fieldname_seq MAXVALUE 2147483647;

ALTER TABLE tablename ALTER COLUMN code_f TYPE INTEGER;

ALTER TABLE public.fournisseur ALTER COLUMN code_f SET DEFAULT nextval('public.fournisseur_code_f_seq'::text);
Hamza Benzaoui
  • 168
  • 3
  • 14