Thread: Converting to identity columns with domains on PK columns
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 ? -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
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
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. So, there is a way to convert that field to a identity field ? -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
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)
>
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
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.klaver@aklaver.com
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.klaver@aklaver.com
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
Well, I think it´ll not as easy as you said. That tables has dependencies. So, if I try to alter type it gives me ERROR: cannot alter type of a column used in a trigger definition. I dropped all Triggers of that table and it gives me ERROR: cannot alter type of a column used by a view or rule. Then, if I need to drop everything to change that thing I think is better just have an empty structure and copy entire database data to it. And why do we used domains, because we were Firebird and on that database a integer or a domain based on an integer is the same, so we converted to Postgres using that way. Thanks -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
On 7/5/19 4:38 AM, PegoraroF10 wrote: > Well, I think it´ll not as easy as you said. That tables has dependencies. > So, if I try to alter type it gives me ERROR: cannot alter type of a column > used in a trigger definition. I dropped all Triggers of that table and it > gives me ERROR: cannot alter type of a column used by a view or rule. > Then, if I need to drop everything to change that thing I think is better > just have an empty structure and copy entire database data to it. > > And why do we used domains, because we were Firebird and on that database a > integer or a domain based on an integer is the same, so we converted to > Postgres using that way. That is not what the Firebird docs say: https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-datatypes-custom.html https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-domn.html https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-ddl-enhance.html > > Thanks > > > > -- > Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html > > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian, on Firebird if you create a table or procedure with fields or params declared with domains, they can be used with those domains or with their base type. On Postgres I32 is not equal to integer. create procedure myproc(id i32) returns(x i32) as ... select * from myproc(cast(5 as integer)) -- works on firebird. On Postgres it doesn´t because that procedure or function expects for a I32 and not a integer. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
PegoraroF10 <marcos@f10.com.br> writes:
> Adrian, on Firebird if you create a table or procedure with fields or params
> declared with domains, they can be used with those domains or with their
> base type. On Postgres I32 is not equal to integer.
> create procedure myproc(id i32) returns(x i32) as ...
> select * from myproc(cast(5 as integer)) -- works on firebird. On Postgres
> it doesn´t because that procedure or function expects for a I32 and not a
> integer.
Really?
regression=# create domain i32 as int;
CREATE DOMAIN
regression=# create function myproc(id i32) returns i32
language sql as 'select $1';
CREATE FUNCTION
regression=# select myproc(cast(5 as integer));
myproc
--------
5
(1 row)
I think the primary reason we don't allow domains over int for identity
columns is that it's not clear how domain constraints ought to interact
with the identity-value-generating behavior.
regards, tom lane
On 7/5/19 1:01 PM, PegoraroF10 wrote: > Adrian, on Firebird if you create a table or procedure with fields or params > declared with domains, they can be used with those domains or with their > base type. On Postgres I32 is not equal to integer. > > create procedure myproc(id i32) returns(x i32) as ... > > select * from myproc(cast(5 as integer)) -- works on firebird. On Postgres > it doesn´t because that procedure or function expects for a I32 and not a > integer. Tom beat me to the example. Still the point is that I think you are going to find that there is not that much difference between Firebird and Postgres on this point. To get back to the original case, is there a specific reason you want a IDENTITY column on the id field? > > > > -- > Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html > > -- Adrian Klaver adrian.klaver@aklaver.com
Sorry, the example I was thinking was this one, which works on Firebird, using its way of writing, obviously. create function myproc(id integer) returns I32 language sql as 'select $1'; On postgres ERROR: return type mismatch in function declared to return i32 What I mean is that Firebird sees I32 and integer as the same, Postgres doesn´t. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
- Because we don´t need to give rigths to user on sequences; - Nobody will change values of pk fields, because we would like to have GENERATE ALWAYS on those PK Fields. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
On 7/5/19 1:49 PM, PegoraroF10 wrote:
> Sorry, the example I was thinking was this one, which works on Firebird,
> using its way of writing, obviously.
>
> create function myproc(id integer) returns I32 language sql as 'select $1';
>
> On postgres ERROR: return type mismatch in function declared to return i32
>
> What I mean is that Firebird sees I32 and integer as the same, Postgres
> doesn´t.
Yeah, but if you reverse the casting you did in your first example it works:
create function myproc(id integer) returns I32 language sql as 'select
$1::i32';
CREATE FUNCTION
test_(aklaver)> select myproc(5);
myproc
--------
5
(1 row)
test_(aklaver)> select pg_typeof(myproc(5));
pg_typeof
-----------
i32
(1 row)
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 7/5/19 1:55 PM, PegoraroF10 wrote:
> - Because we don´t need to give rigths to user on sequences;
> - Nobody will change values of pk fields, because we would like to have
> GENERATE ALWAYS on those PK Fields.
An IDENTITY column is still backed by a sequence:
create table identity_test(id integer PRIMARY KEY GENERATED BY DEFAULT
AS IDENTITY);
\ds identity_test_id_seq
List of relations
Schema | Name | Type | Owner
--------+----------------------+----------+---------
public | identity_test_id_seq | sequence | aklaver
You end up with same thing as using a sequence(with some additional
syntax over its behavior):
create table seq_id_test(id integer PRIMARY KEY);
create sequence seq_id_test_seq AS integer OWNED BY seq_id_test.id;
\ds seq_id_test_seq
List of relations
Schema | Name | Type | Owner
--------+-----------------+----------+---------
public | seq_id_test_seq | sequence | aklaver
Rights are the same:
\c - production
You are now connected to database "test" as user "production".
test_(production)> insert into identity_test (id) values(default);
ERROR: permission denied for table identity_test
test_(production)> insert into seq_id_test (id) values(default);
ERROR: permission denied for table seq_id_test
A user can change the PK by using OVERRIDING SYSTEM VALUE in an INSERT.
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 7/5/19 1:49 PM, PegoraroF10 wrote:
>> Sorry, the example I was thinking was this one, which works on Firebird,
>> using its way of writing, obviously.
>> create function myproc(id integer) returns I32 language sql as 'select $1';
>>
>> On postgres ERROR: return type mismatch in function declared to return i32
>> What I mean is that Firebird sees I32 and integer as the same, Postgres
>> doesn´t.
> Yeah, but if you reverse the casting you did in your first example it works:
> create function myproc(id integer) returns I32 language sql as 'select
> $1::i32';
> CREATE FUNCTION
Yeah. This isn't an inherent property of Postgres, it's just that
SQL-language functions aren't defined to provide any implicit casting
of their results. The given expression must yield exactly the declared
function result type.
Most other places in PG are laxer and will automatically perform
implicit (and maybe assignment) casts for you. I don't remember
offhand whether there are good reasons for SQL functions to be
picky about this or it's just a shortage of round tuits. I have
a vague feeling that there might be some compatibility issues
in there, though.
regards, tom lane
On 7/5/19 3:32 PM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> On 7/5/19 1:49 PM, PegoraroF10 wrote:
>>> Sorry, the example I was thinking was this one, which works on Firebird,
>>> using its way of writing, obviously.
>>> create function myproc(id integer) returns I32 language sql as 'select $1';
>>>
>>> On postgres ERROR: return type mismatch in function declared to return i32
>>> What I mean is that Firebird sees I32 and integer as the same, Postgres
>>> doesn´t.
>
>> Yeah, but if you reverse the casting you did in your first example it works:
>> create function myproc(id integer) returns I32 language sql as 'select
>> $1::i32';
>> CREATE FUNCTION
>
> Yeah. This isn't an inherent property of Postgres, it's just that
> SQL-language functions aren't defined to provide any implicit casting
> of their results. The given expression must yield exactly the declared
> function result type.
Aah:
CREATE OR REPLACE FUNCTION public.domain_test(id integer)
RETURNS i32
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN id;
END;
$function$
;
test=> select domain_test(5);
domain_test
-------------
5
test=> select pg_typeof(domain_test(5));
pg_typeof
-----------
i32
(1 row)
So it works in plpgsql.
>
> Most other places in PG are laxer and will automatically perform
> implicit (and maybe assignment) casts for you. I don't remember
> offhand whether there are good reasons for SQL functions to be
> picky about this or it's just a shortage of round tuits. I have
> a vague feeling that there might be some compatibility issues
> in there, though.
>
> regards, tom lane
>
--
Adrian Klaver
adrian.klaver@aklaver.com