Re: Converting to identity columns with domains on PK columns

On 7/4/19 12:41 PM, PegoraroF10 wrote:
> Domains on Postgres are really strange to me. Am I creating a domain which is
> exactly equal to integer, right ?
>
> create domain i32 as integer;
> create domain T50 as varchar(50);
>
> Create table MyTable(
> ID I32 not null primary key,
> Description T50);
>
> Then, after inserts and updates done to that table, I want to convert that
> primary key to a identity column.
>
> alter table MyTable alter ID add generated always as identity;
>
> ERROR: identity column type must be smallint, integer, or bigint
>
> So, What do I need do to create this identity column ?
> Why Postgres consider different I32 and integer ?

Because one(integer) is a base type and the other is a domain over a
base type(I32). Domains can have restrictions over what is accepted so I
can see why they would not be good candidates for a sequence(identity).

Solutions:

1) Create a new integer column for the identity.

2) alter table MyTable alter ID type integer;

alter table MyTable alter ID add generated always as identity;

\d 'MyTable'

Table "public.mytable"

Column | Type | Collation | Nullable | Default

-------------+---------+-----------+----------+------------------------------

id | integer | | not null | generated always as
identity

description | t50 | | |

>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>
>
>

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