Re: Converting to identity columns with domains on PK columns

On 7/4/19 1:27 PM, Adrian Klaver wrote:
> On 7/4/19 1:03 PM, PegoraroF10 wrote:
>> ok, thanks for the explanation but ... I cannot add a field and move 
>> data,
>> constraints, triggers, identity to it because my pk field will be
>> repositioned to the last field on that table and I have lots of other 
>> codes
>> which point to pk as the first field on every table.
> 
> I stay away from using the index position of field for this reason.
> 
>>
>> So, there is a way to convert that field to a identity field ?
> 
> See my second option in previous post.
> 
> Or, old school identity column:):
> 
> create sequence id_seq owned by mytable.id;
> 
> alter table mytable alter column id set default nextval('id_seq');
> 
> \d mytable
>                           Table "public.mytable"
>     Column    | Type | Collation | Nullable |           Default
> -------------+------+-----------+----------+-----------------------------
>   id          | i32  |           | not null | nextval('id_seq'::regclass)
>   description | t50  |           |          |
> Indexes:
>      "mytable_pkey" PRIMARY KEY, btree (id)
> 

Further thinking pointed up the peril of the above. Your field is 
defined as integer and per my previous post a sequence without an AS 
data_type will be bigint. At some point the sequence is going to start 
trying to set nextval() to a number your field cannot handle. If you go 
that route you will need to do something like:

create sequence id_seq AS integer owned by mytable.id;


-- 
Adrian Klaver
adrian.klaver@aklaver.com