I have a Database which is migrated from MSSQL to PostgreSQL(9.2).
This Database have 100+ tables, These table have autonumbering filed(PRIMARY KEY field), given below is an example for a table
CREATE TABLE company
(
companyid integer NOT NULL DEFAULT nextval('seq_company_id'::regclass),
company character varying(100),
add1 character varying(100),
add2 character varying(100),
add3 character varying(100),
phoneoff character varying(30),
phoneres character varying(30)
CONSTRAINT gcompany_pkey PRIMARY KEY (companyid)
)
sample data
INSERT INTO company (company, add1, add2, add3, phoneoff, phoneres) VALUES
('company1','add1','add2','add3','00055544','7788848');
INSERT INTO company (company, add1, add2, add3, phoneoff, phoneres) VALUES
('company2','add9','add5','add2','00088844','7458844');
INSERT INTO company (company, add1, add2, add3, phoneoff, phoneres) VALUES
('company5','add5','add8','add7','00099944','2218844');
and below is the sequence for this table
CREATE SEQUENCE seq_company_id
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE seq_company_id
OWNER TO postgres;
while reading PostgreSQL Documentation i read about Serial Types so i wish to change all the existing auto numbering fields to serial.
How to do it?
i have tried
alter table company alter column companyid type serial
ERROR: type "serial" does not exist
********** Error **********