From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | PegoraroF10 <marcos(at)f10(dot)com(dot)br>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Converting to identity columns with domains on PK columns |
Date: | 2019-07-04 21:20:17 |
Message-ID: | 41d40a94-4e5c-904b-dd29-8b0130a6920f@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2019-07-04 21:42:40 | Re: Why does jsonb_set() remove non-mentioned keys? |
Previous Message | David G. Johnston | 2019-07-04 21:09:14 | Re: Why does jsonb_set() remove non-mentioned keys? |