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.klaver@aklaver.com