Re: Converting to identity columns with domains on PK columns

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 ?

A domain is more than just a different name for a data type, so
the system doesn't treat them as identical.

Another way to proceed would be:

ALTER TABLE mytable ALTER id TYPE integer;
ALTER TABLE mytable ALTER id ADD GENERATED ALWAYS AS IDENTITY;

That would not rewrite the table, just "relabel" the type name
to "integer" and then convert it to an identity column.

Why do you want that extra level of obfuscation rather than
calling an integer an integer?

Yours,
Laurenz Albe

--
Cybertec | https://www.cybertec-postgresql.com