Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

How to auto generate id in postgresql on an existing column

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 .

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;




This post first appeared on Dba-ninja.com, please read the originial post: here

Share the post

How to auto generate id in postgresql on an existing column

×

Subscribe to Dba-ninja.com

Get updates delivered right to your inbox!

Thank you for your subscription

×