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)
>
>

I know this worked, but then I got to wondering why?

Found the answer in sequence.c(init_params) in the if (as_type != NULL)
section. When creating a sequence you can specify AS data_type as long
as the type is one of smallint, integer or bigint. If data_type is not
specified then the default is bigint. If I am following correctly in
tablecommands.c when you create an IDENTITY column it uses the type it
gets from the column for the AS data_type. In your case that would be a
domain type which is != to the base types above.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com