Re: Adding identity column to a non-empty table

"Igal @ Lucee.org" <igal@xxxxxxxxx> · Sun, 15 Oct 2017 18:09:15 -0700



    Melvin,

    
    On 10/15/2017 5:56 PM, Melvin Davidson wrote:

    
          On Sun, Oct 15, 2017 at 8:24 PM, Igal
            @ Lucee.org <igal@xxxxxxxxx>
            wrote:

            On
                10/15/2017 4:01 PM, Igal @ Lucee.org wrote:

                
                  Hello,

                  
                  I'm trying to add an identity column to a table that
                  has records (previously had a bigserial column which I
                  removed):

                  
              There is probably a better solution, but the one I came up
              with is to add the column as BIGSERIAL and DROP the
              SEQUENCE CASCADE, SELECT the max(rid) + 1, and then
              convert the column to IDENTITY:

              
        The correct way to make r_id the
          primary key would be:
        

        ALTER TABLE  event_log

            ADD COLUMN r_id SERIAL;

          
          ALTER TABLE  event_log
          ALTER COLUMN r_id TYPE BIGINT,

            ADD CONSTRAINT dummy_pk PRIMARY KEY (r_id);

          
        That automatically generates the column
          as 

        
        r_id bigint NOT NULL DEFAULT
          nextval('dummy_r_id_seq'::regclass),

            CONSTRAINT dummy_pk PRIMARY KEY (r_id)
        

        and creates the appropriate sequence
          for you.
        

    Does that use the new IDENTITY construct that was added in Postgres
    10?  I do not really care for the PRIMARY KEY constraint.  I just
    want the sequence with the benefits of the new IDENTITY "type".

    
    Thanks,

    
    Igal