Question: How can I convert an existing Column in Postgresql to become an auto generated column ? The existing Table column is an integer and is a Primary Key . There are already 103 rows in the table - with sequential numbers in place - from 1 -> 103. The goal is to start at the number 104 , and increment by 1 .
Related Articles
Answer: It is possible to achieve this goal on PostgreSQL . I don't have access to your DDL but here is some sample code , demonstrating the basic process of setting up a SEQUENCE on an existing Postgresql table with data in place. Adapt to your purposes.
The actual code creating the SEQUENCE code is :
ALTER TABLE sequence_test ALTER COLUMN seq_id ADD GENERATED BY DEFAULT AS IDENTITY(SEQUENCE NAME seq_test1 START WITH 104 INCREMENT BY 1);
This method is available from Postgresql 10. If you're using a PostgreSQL version earlier that 10 , look at using the CREATE SEQUENCE method.
You'll need the table name , column name and a naming convention for the SEQUENCE NAME. In the example , the increment will be by 1
In the CREATE TABLE step I haven't added the schema name prefix . That may be something you need to complete . To execute the statement without a schema - you may need to change your PostgreSQL default schema
--create a test table in PostgreSQL CREATE TABLE IF NOT EXISTS sequence_test ( seq_id integer NOT NULL, job_nm character varying(50) COLLATE pg_catalog."default", CONSTRAINT sequence_test_pkey PRIMARY KEY (seq_id) ) --add some data prior to adding an auto generated IDENTITY column. Note: adding data for both columns insert into sequence_test select 1 , 'job1' UNION select 2, 'job2'; --check the data select * from sequence_test; --alter an existing column and convert to SERIAL Note: starting at maxvalue + 1 ALTER TABLE sequence_test ALTER COLUMN seq_id ADD GENERATED BY DEFAULT AS IDENTITY(SEQUENCE NAME seq_test1 START WITH 104 INCREMENT BY 1); --add some rows . Note: adding just the job_num insert into sequence_test (job_nm)values('job3'); insert into sequence_test (job_nm)values('job4'); --check the data select * from sequence_test;