Primary key data type: integer vs identity
When I created the database I set primary key data types as integer; for
example:
Column | Type | Collation | Nullable | Default
--------------+-----------------------+-----------+----------+-------------
org_id | integer | | not null |
nextval('organizations_org_id_seq'::regclass)
I probably should have used the serial type, but didn't.
If it would be advisable for me to convert from integer to identity please
point me to the appropriate work flow.
Reading the CREATE TABLE pages in the manual did not give me sufficient
insights to appreciate the differences or indicate how to change the
column's data type.
Regards,
Rich
On 4/19/19 10:55 AM, Rich Shepard wrote:
When I created the database I set primary key data types as integer; for
example:Column | Type | Collation | Nullable | Default
--------------+-----------------------+-----------+----------+-------------
org_id | integer | | not null |
nextval('organizations_org_id_seq'::regclass)I probably should have used the serial type, but didn't.
If you want it to be like serial(assuming the sequences are unique to
each table) then:
https://www.postgresql.org/docs/11/sql-altersequence.html
"OWNED BY table_name.column_name
OWNED BY NONE
The OWNED BY option causes the sequence to be associated with a
specific table column, such that if that column (or its whole table) is
dropped, the sequence will be automatically dropped as well. If
specified, this association replaces any previously specified
association for the sequence. The specified table must have the same
owner and be in the same schema as the sequence. Specifying OWNED BY
NONE removes any existing association, making the sequence “free-standing”.
"
If it would be advisable for me to convert from integer to identity please
point me to the appropriate work flow.
If it is working for you now I see no reason to switch.
Reading the CREATE TABLE pages in the manual did not give me sufficient
insights to appreciate the differences or indicate how to change the
column's data type.
IDENTITY is a SQL standard.
The difference:
https://www.postgresql.org/docs/11/sql-createtable.html
"GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
This clause creates the column as an identity column. It will have
an implicit sequence attached to it and the column in new rows will
automatically have values from the sequence assigned to it.
The clauses ALWAYS and BY DEFAULT determine how the sequence value
is given precedence over a user-specified value in an INSERT statement.
If ALWAYS is specified, a user-specified value is only accepted if the
INSERT statement specifies OVERRIDING SYSTEM VALUE. If BY DEFAULT is
specified, then the user-specified value takes precedence. See INSERT
for details. (In the COPY command, user-specified values are always used
regardless of this setting.)
The optional sequence_options clause can be used to override the
options of the sequence. See CREATE SEQUENCE for details.
"
Basically the ALWAYS/DEFAULT choices.
Regards,
Rich
--
Adrian Klaver
adrian.klaver@aklaver.com
On Fri, Apr 19, 2019 at 10:55 AM Rich Shepard <rshepard@appl-ecosys.com>
wrote:
When I created the database I set primary key data types as integer; for
example:Column | Type | Collation | Nullable | Default
--------------+-----------------------+-----------+----------+-------------
org_id | integer | | not null |
nextval('organizations_org_id_seq'::regclass)I probably should have used the serial type, but didn't.
If it would be advisable for me to convert from integer to identity please
point me to the appropriate work flow.
https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL
"The data types smallserial, serial and bigserial are not true types, but
merely a notational convenience for creating unique identifier columns"
so really there's no difference, what you have is fine.
-Michel
Reading the CREATE TABLE pages in the manual did not give me sufficient
Show quoted text
insights to appreciate the differences or indicate how to change the
column's data type.Regards,
Rich
On Fri, 19 Apr 2019, Michel Pelletier wrote:
https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL
"The data types smallserial, serial and bigserial are not true types, but
merely a notational convenience for creating unique identifier columns"
so really there's no difference, what you have is fine.
Michel,
That's what I thought. And confirmation is always valuable.
Regards,
Rich
On Fri, 19 Apr 2019, Adrian Klaver wrote:
If it is working for you now I see no reason to switch.
Adrian,
It does work. I just learned about the SQL identity type and want to learn
when it's most appropriate to use. The databases I develop all work with
integers as primary keys and reading about the type didn't clarify (for me)
when it should be used.
Regards,
Rich
On 4/19/19 11:14 AM, Rich Shepard wrote:
On Fri, 19 Apr 2019, Adrian Klaver wrote:
If it is working for you now I see no reason to switch.
Adrian,
It does work. I just learned about the SQL identity type and want to learn
when it's most appropriate to use. The databases I develop all work with
integers as primary keys and reading about the type didn't clarify (for me)
when it should be used.
Mainly for folks that want cross database SQL compliance. It is not a
type so much as a way of specifying an auto-increment column.
Regards,
Rich
--
Adrian Klaver
adrian.klaver@aklaver.com
On Fri, Apr 19, 2019 at 11:20 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
On 4/19/19 11:14 AM, Rich Shepard wrote:
On Fri, 19 Apr 2019, Adrian Klaver wrote:
If it is working for you now I see no reason to switch.
Adrian,
It does work. I just learned about the SQL identity type and want to
learn
when it's most appropriate to use. The databases I develop all work with
integers as primary keys and reading about the type didn't clarify (forme)
when it should be used.
Mainly for folks that want cross database SQL compliance. It is not a
type so much as a way of specifying an auto-increment column.
It also sounds like it has advantages in terms of tying your sequence
directly to the column. If you drop a serial column, it doesn't drop the
sequence.
Once I've upgraded to 10+, I might look at converting my existing serial
columns. Peter Eisentraut wrote a good piece(1) on identity columns,
including a function for converting existing serial columns. I've copied
the function below, but had two questions about it:
1) Would the function as written also reassign ownership to that table
column? (I see the update to pg_depend and pg_attribute, but don't know
enough about them to know if that changes ownership)
2) Would one have to be a superuser to do this?
Thanks,
Ken
(1) https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/
CREATE OR REPLACE FUNCTION upgrade_serial_to_identity(tbl regclass, col
name)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
colnum smallint;
seqid oid;
count int;
BEGIN
-- find column number
SELECT attnum INTO colnum FROM pg_attribute WHERE attrelid = tbl AND
attname = col;
IF NOT FOUND THEN
RAISE EXCEPTION 'column does not exist';
END IF;
-- find sequence
SELECT INTO seqid objid
FROM pg_depend
WHERE (refclassid, refobjid, refobjsubid) = ('pg_class'::regclass, tbl,
colnum)
AND classid = 'pg_class'::regclass AND objsubid = 0
AND deptype = 'a';
GET DIAGNOSTICS count = ROW_COUNT;
IF count < 1 THEN
RAISE EXCEPTION 'no linked sequence found';
ELSIF count > 1 THEN
RAISE EXCEPTION 'more than one linked sequence found';
END IF;
-- drop the default
EXECUTE 'ALTER TABLE ' || tbl || ' ALTER COLUMN ' || quote_ident(col) ||
' DROP DEFAULT';
-- change the dependency between column and sequence to internal
UPDATE pg_depend
SET deptype = 'i'
WHERE (classid, objid, objsubid) = ('pg_class'::regclass, seqid, 0)
AND deptype = 'a';
-- mark the column as identity column
UPDATE pg_attribute
SET attidentity = 'd'
WHERE attrelid = tbl
AND attname = col;
END;
$$;
--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken.tanzer@agency-software.org
(253) 245-3801
Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
On Fri, 19 Apr 2019, Adrian Klaver wrote:
Mainly for folks that want cross database SQL compliance. It is not a type
so much as a way of specifying an auto-increment column.
Thank you, Adrian. I saw that it was a column specification and your
explanation of its application is really helpful.
Carpe weekend,
Rich
On 4/19/19 11:32 AM, Ken Tanzer wrote:
On Fri, Apr 19, 2019 at 11:20 AM Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:On 4/19/19 11:14 AM, Rich Shepard wrote:
On Fri, 19 Apr 2019, Adrian Klaver wrote:
If it is working for you now I see no reason to switch.
Adrian,
It does work. I just learned about the SQL identity type and want
to learn
when it's most appropriate to use. The databases I develop all
work with
integers as primary keys and reading about the type didn't
clarify (for me)
when it should be used.
Mainly for folks that want cross database SQL compliance. It is not a
type so much as a way of specifying an auto-increment column.It also sounds like it has advantages in terms of tying your sequence
directly to the column. If you drop a serial column, it doesn't drop
the sequence.
A serial column will:
test=> create table serial_test(id serial);
CREATE TABLE
test=> \d serial_test
Table "public.serial_test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+-----------------------------------------
id | integer | | not null |
nextval('serial_test_id_seq'::regclass)
test=> select * from serial_test_id_seq ;
last_value | log_cnt | is_called
------------+---------+-----------
1 | 0 | f
(1 row)
test=> drop table serial_test ;
DROP TABLE
test=> select * from serial_test_id_seq ;
ERROR: relation "serial_test_id_seq" does not exist
LINE 1: select * from serial_test_id_seq ;
If you just use a sequence as a default value it will not unless you
make it OWNED by the table per the link I posted upstream.
Once I've upgraded to 10+, I might look at converting my existing serial
columns. Peter Eisentraut wrote a good piece(1) on identity columns,
including a function for converting existing serial columns. I've
copied the function below, but had two questions about it:1) Would the function as written also reassign ownership to that table
column? (I see the update to pg_depend and pg_attribute, but don't know
enough about them to know if that changes ownership)
2) Would one have to be a superuser to do this?Thanks,
Ken
--
Adrian Klaver
adrian.klaver@aklaver.com
On 4/19/19 11:32 AM, Ken Tanzer wrote:
On Fri, Apr 19, 2019 at 11:20 AM Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:On 4/19/19 11:14 AM, Rich Shepard wrote:
On Fri, 19 Apr 2019, Adrian Klaver wrote:
If it is working for you now I see no reason to switch.
Adrian,
It does work. I just learned about the SQL identity type and want
to learn
when it's most appropriate to use. The databases I develop all
work with
integers as primary keys and reading about the type didn't
clarify (for me)
when it should be used.
Mainly for folks that want cross database SQL compliance. It is not a
type so much as a way of specifying an auto-increment column.It also sounds like it has advantages in terms of tying your sequence
directly to the column. If you drop a serial column, it doesn't drop
the sequence.
Misread your post the first time still:
create table serial_test(id serial, fld_1 text);
CREATE TABLE
test=> \d serial_test
Table "public.serial_test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+-----------------------------------------
id | integer | | not null |
nextval('serial_test_id_seq'::regclass)
fld_1 | text | | |
test=> alter table serial_test drop column id;
ALTER TABLE
test=> \d serial_test
Table "public.serial_test"
Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
fld_1 | text | | |
test=> select * from serial_test_id_seq ;
ERROR: relation "serial_test_id_seq" does not exist
LINE 1: select * from serial_test_id_seq ;
Once I've upgraded to 10+, I might look at converting my existing serial
columns. Peter Eisentraut wrote a good piece(1) on identity columns,
including a function for converting existing serial columns. I've
copied the function below, but had two questions about it:1) Would the function as written also reassign ownership to that table
column? (I see the update to pg_depend and pg_attribute, but don't know
enough about them to know if that changes ownership)
2) Would one have to be a superuser to do this?Thanks,
Ken
--
Adrian Klaver
adrian.klaver@aklaver.com
On Fri, Apr 19, 2019 at 12:02 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
On 4/19/19 11:32 AM, Ken Tanzer wrote:
On Fri, Apr 19, 2019 at 11:20 AM Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:On 4/19/19 11:14 AM, Rich Shepard wrote:
On Fri, 19 Apr 2019, Adrian Klaver wrote:
If it is working for you now I see no reason to switch.
Adrian,
It does work. I just learned about the SQL identity type and want
to learn
when it's most appropriate to use. The databases I develop all
work with
integers as primary keys and reading about the type didn't
clarify (for me)
when it should be used.
Mainly for folks that want cross database SQL compliance. It is not a
type so much as a way of specifying an auto-increment column.It also sounds like it has advantages in terms of tying your sequence
directly to the column. If you drop a serial column, it doesn't drop
the sequence.A serial column will:
Thanks Adrian. You are as usual correct. (I had a bunch of tables created
by a function that I assumed were serial, but were not.) Identity columns
still seem tidier and more manageable. Can you tell if the function I
referenced would change the ownership or not?
Cheers,
Ken
--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken.tanzer@agency-software.org
(253) 245-3801
Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
On 4/19/19 12:35 PM, Ken Tanzer wrote:
Thanks Adrian. You are as usual correct. (I had a bunch of tables
created by a function that I assumed were serial, but were not.)
Identity columns still seem tidier and more manageable. Can you tell if
the function I referenced would change the ownership or not?
I believe in 'when it doubt try it, whats the worst that can happen?:)':
<NOTE> I needed to be a superuser to run due to this:
ERROR: permission denied for table pg_depend
CONTEXT: SQL statement "UPDATE pg_depend
SET deptype = 'i'
WHERE (classid, objid, objsubid) = ('pg_class'::regclass, seqid, 0)
AND deptype = 'a'"
PL/pgSQL function upgrade_serial_to_identity(regclass,name) line 31 at
SQL statement
test=# create table serial_test(id serial, fld_1 text);
CREATE TABLE
test=# \dp serial_test
Access privileges
Schema | Name | Type | Access privileges | Column privileges |
Policies
--------+-------------+-------+-------------------+-------------------+----------
public | serial_test | table | | |
(1 row)
test=# select upgrade_serial_to_identity('serial_test', 'id');
upgrade_serial_to_identity
----------------------------
(1 row)
test=# \d serial_test
Table "public.serial_test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+----------------------------------
id | integer | | not null | generated by default as identity
fld_1 | text | | |
test=# \dp+ serial_test
Access privileges
Schema | Name | Type | Access privileges | Column privileges |
Policies
--------+-------------+-------+-------------------+-------------------+----------
public | serial_test | table | | |
(1 row)
Cheers,
Ken
--
Adrian Klaver
adrian.klaver@aklaver.com
On Fri, Apr 19, 2019 at 12:50 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
On 4/19/19 12:35 PM, Ken Tanzer wrote:
Thanks Adrian. You are as usual correct. (I had a bunch of tables
created by a function that I assumed were serial, but were not.)
Identity columns still seem tidier and more manageable. Can you tell if
the function I referenced would change the ownership or not?I believe in 'when it doubt try it, whats the worst that can happen?:)':
I agree, and if I had a copy of 10+ running, I probably would have! :)
<NOTE> I needed to be a superuser to run due to this:
ERROR: permission denied for table pg_depend
CONTEXT: SQL statement "UPDATE pg_depend
SET deptype = 'i'
WHERE (classid, objid, objsubid) = ('pg_class'::regclass, seqid, 0)
AND deptype = 'a'"
PL/pgSQL function upgrade_serial_to_identity(regclass,name) line 31 at
SQL statementtest=# create table serial_test(id serial, fld_1 text);
CREATE TABLE
test=# \dp serial_test
Access privileges
Schema | Name | Type | Access privileges | Column privileges |
Policies--------+-------------+-------+-------------------+-------------------+----------
public | serial_test | table | | |
(1 row)test=# select upgrade_serial_to_identity('serial_test', 'id');
upgrade_serial_to_identity
----------------------------(1 row)
test=# \d serial_test
Table "public.serial_test"
Column | Type | Collation | Nullable | Default--------+---------+-----------+----------+----------------------------------
id | integer | | not null | generated by default as
identity
fld_1 | text | | |test=# \dp+ serial_test
Access privileges
Schema | Name | Type | Access privileges | Column privileges |
Policies--------+-------------+-------+-------------------+-------------------+----------
public | serial_test | table | | |
(1 row)
Maybe I'm missing it, but I'm not really sure what that is supposed to be
telling me about the ownership of the sequence.
The scenario I'm wondering about is:
Table A owned by User 1, and has column created as serial
The created sequence is altered to be owned by User 2 (with User 1 granted
select & update)
upgrade_serial_to_identity applied to Table A
At that point, who owns the sequence?
I can wait until I've got 10+ running and try it myself, but I thought
maybe someone would know the answer and be willing to share.
Thanks!
Ken
--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken.tanzer@agency-software.org
(253) 245-3801
Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
On 4/19/19 1:02 PM, Ken Tanzer wrote:
On Fri, Apr 19, 2019 at 12:50 PM Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:On 4/19/19 12:35 PM, Ken Tanzer wrote:
Thanks Adrian. You are as usual correct. (I had a bunch of tables
created by a function that I assumed were serial, but were not.)
Identity columns still seem tidier and more manageable. Can youtell if
the function I referenced would change the ownership or not?
I believe in 'when it doubt try it, whats the worst that can happen?:)':
I agree, and if I had a copy of 10+ running, I probably would have! :)
<NOTE> I needed to be a superuser to run due to this:
ERROR: permission denied for table pg_depend
CONTEXT: SQL statement "UPDATE pg_depend
SET deptype = 'i'
WHERE (classid, objid, objsubid) = ('pg_class'::regclass,
seqid, 0)
AND deptype = 'a'"
PL/pgSQL function upgrade_serial_to_identity(regclass,name) line 31 at
SQL statementtest=# create table serial_test(id serial, fld_1 text);
CREATE TABLE
test=# \dp serial_test
Access privileges
Schema | Name | Type | Access privileges | Column
privileges |
Policies
--------+-------------+-------+-------------------+-------------------+----------
public | serial_test | table | |
|
(1 row)test=# select upgrade_serial_to_identity('serial_test', 'id');
upgrade_serial_to_identity
----------------------------(1 row)
test=# \d serial_test
Table "public.serial_test"
Column | Type | Collation | Nullable | Default--------+---------+-----------+----------+----------------------------------
id | integer | | not null | generated by default as
identity
fld_1 | text | | |test=# \dp+ serial_test
Access privileges
Schema | Name | Type | Access privileges | Column
privileges |
Policies
--------+-------------+-------+-------------------+-------------------+----------
public | serial_test | table | |
|
(1 row)Maybe I'm missing it, but I'm not really sure what that is supposed to
be telling me about the ownership of the sequence.The scenario I'm wondering about is:
Table A owned by User 1, and has column created as serial
The created sequence is altered to be owned by User 2 (with User 1
granted select & update)
upgrade_serial_to_identity applied to Table A
At that point, who owns the sequence?I can wait until I've got 10+ running and try it myself, but I thought
maybe someone would know the answer and be willing to share.
select version();
version
----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux)
7.3.1 20180323 [gcc-7-branch revision 258812], 64-bit
select session_user, current_user;
session_user | current_user
--------------+--------------
aklaver | aklaver
create table serial_test(id serial, fld_1 text);
CREATE TABLE
\d
List of relations
Schema | Name | Type | Owner
--------+--------------------+----------+----------
public | serial_test | table | aklaver
public | serial_test_id_seq | sequence | aklaver
test_(aklaver)> \c - postgres
You are now connected to database "test" as user "postgres".
test_(postgres)# select session_user, current_user;
session_user | current_user
--------------+--------------
postgres | postgres
(1 row)
test_(postgres)# select upgrade_serial_to_identity('serial_test', 'id');
upgrade_serial_to_identity
----------------------------
(1 row)
List of relations
Schema | Name | Type | Owner
--------+--------------------+----------+----------
public | serial_test | table | aklaver
public | serial_test_id_seq | sequence | aklaver
The function is working directly on the system catalogs and I do not
anything that changes ownership:
UPDATE pg_depend
SET deptype = 'i'
WHERE (classid, objid, objsubid) = ('pg_class'::regclass, seqid, 0)
AND deptype = 'a';
-- mark the column as identity column
UPDATE pg_attribute
SET attidentity = 'd'
WHERE attrelid = tbl
AND attname = col;
Thanks!
Ken
--
AGENCY Software
A Free Software data system
By and for non-profits
/http://agency-software.org//
/https://demo.agency-software.org/client/
ken.tanzer@agency-software.org <mailto:ken.tanzer@agency-software.org>
(253) 245-3801Subscribe to the mailing list
<mailto:agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
--
Adrian Klaver
adrian.klaver@aklaver.com
On Fri, Apr 19, 2019 at 1:39 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
On 4/19/19 1:02 PM, Ken Tanzer wrote:
On Fri, Apr 19, 2019 at 12:50 PM Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:On 4/19/19 12:35 PM, Ken Tanzer wrote:
Thanks Adrian. You are as usual correct. (I had a bunch of
tables
created by a function that I assumed were serial, but were not.)
Identity columns still seem tidier and more manageable. Can youtell if
the function I referenced would change the ownership or not?
I believe in 'when it doubt try it, whats the worst that can
happen?:)':
I agree, and if I had a copy of 10+ running, I probably would have! :)
<NOTE> I needed to be a superuser to run due to this:
ERROR: permission denied for table pg_depend
CONTEXT: SQL statement "UPDATE pg_depend
SET deptype = 'i'
WHERE (classid, objid, objsubid) = ('pg_class'::regclass,
seqid, 0)
AND deptype = 'a'"
PL/pgSQL function upgrade_serial_to_identity(regclass,name) line 31at
SQL statement
test=# create table serial_test(id serial, fld_1 text);
CREATE TABLE
test=# \dp serial_test
Access privileges
Schema | Name | Type | Access privileges | Column
privileges |
Policies--------+-------------+-------+-------------------+-------------------+----------
public | serial_test | table | |
|
(1 row)test=# select upgrade_serial_to_identity('serial_test', 'id');
upgrade_serial_to_identity
----------------------------(1 row)
test=# \d serial_test
Table "public.serial_test"
Column | Type | Collation | Nullable | Default--------+---------+-----------+----------+----------------------------------
id | integer | | not null | generated by default as
identity
fld_1 | text | | |test=# \dp+ serial_test
Access privileges
Schema | Name | Type | Access privileges | Column
privileges |
Policies--------+-------------+-------+-------------------+-------------------+----------
public | serial_test | table | |
|
(1 row)Maybe I'm missing it, but I'm not really sure what that is supposed to
be telling me about the ownership of the sequence.The scenario I'm wondering about is:
Table A owned by User 1, and has column created as serial
The created sequence is altered to be owned by User 2 (with User 1
granted select & update)
upgrade_serial_to_identity applied to Table A
At that point, who owns the sequence?I can wait until I've got 10+ running and try it myself, but I thought
maybe someone would know the answer and be willing to share.select version();
version----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux)
7.3.1 20180323 [gcc-7-branch revision 258812], 64-bitselect session_user, current_user;
session_user | current_user
--------------+--------------
aklaver | aklavercreate table serial_test(id serial, fld_1 text);
CREATE TABLE\d
List of relations
Schema | Name | Type | Owner
--------+--------------------+----------+----------public | serial_test | table | aklaver
public | serial_test_id_seq | sequence | aklavertest_(aklaver)> \c - postgres
You are now connected to database "test" as user "postgres".
test_(postgres)# select session_user, current_user;
session_user | current_user
--------------+--------------
postgres | postgres
(1 row)test_(postgres)# select upgrade_serial_to_identity('serial_test', 'id');
upgrade_serial_to_identity
----------------------------(1 row)
List of relations
Schema | Name | Type | Owner
--------+--------------------+----------+----------public | serial_test | table | aklaver
public | serial_test_id_seq | sequence | aklaverThe function is working directly on the system catalogs and I do not
anything that changes ownership:UPDATE pg_depend
SET deptype = 'i'
WHERE (classid, objid, objsubid) = ('pg_class'::regclass, seqid, 0)
AND deptype = 'a';-- mark the column as identity column
UPDATE pg_attribute
SET attidentity = 'd'
WHERE attrelid = tbl
AND attname = col;
Thanks, though I don't see what this shows, since there were not separate
users involved. So I loaded up a copy of 11.2, and discovered that you
actually can't change the ownership of a sequence created by serial.
ag_tz_test=# ALTER SEQUENCE t_serial_id_seq OWNER TO develop;
ERROR: cannot change owner of sequence "t_serial_id_seq"
DETAIL: Sequence "t_serial_id_seq" is linked to table "t_serial".
I also missed the part in the article where it talks about assigning
ownership to the column. In psql, it's easy to miss because a \ds (or
\ds+) (as opposed to a \d) shows the user that owns the sequence, not a
column:
test=# \ds+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-------------------+----------+-------+------------+-------------
public | t_serial_id_seq | sequence | u1 | 8192 bytes |
Whereas if you look at \d it shows the column:
test=# \d+ t_serial_id_seq
Sequence "public.t_serial_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
integer | 1 | 1 | 2147483647 | 1 | no | 1
Owned by: public.t_serial.id
(Side note: it is surprising that the Size and Description don't show up
with \d+. I always thought that a \d+ was the best way to get all the
detail on an object.)
But even if you drop the default on the column, it doesn't seem like you
can change the sequence's owner:
test=# ALTER TABLE t_serial ALTER COLUMN id DROP DEFAULT;
ALTER TABLE
test=# alter sequence t_serial_id_seq OWNER TO u2;
ERROR: cannot change owner of sequence "t_serial_id_seq"
DETAIL: Sequence "t_serial_id_seq" is linked to table "t_serial".
Although you can drop it:
DROP SEQUENCE t_serial_id_seq;
DROP SEQUENCE
Anyhoo, I've learned a bit more today, and thanks for your help!
Cheers,
Ken
--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken.tanzer@agency-software.org
(253) 245-3801
Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
On 4/19/19 2:31 PM, Ken Tanzer wrote:
On Fri, Apr 19, 2019 at 1:39 PM Adrian Klaver <adrian.klaver@aklaver.com
Thanks, though I don't see what this shows, since there were not
separate users involved. So I loaded up a copy of 11.2, and discovered
Well the table was created by one user and the serial was changed to
IDENTITY by another.
that you actually can't change the ownership of a sequence created by
serial.ag_tz_test=# ALTER SEQUENCE t_serial_id_seq OWNER TO develop;
ERROR: cannot change owner of sequence "t_serial_id_seq"
DETAIL: Sequence "t_serial_id_seq" is linked to table "t_serial".
That is covered here:
https://www.postgresql.org/docs/11/sql-altersequence.html
"OWNED BY table_name.column_name
...
The specified table must have the same owner and be in the same schema
as the sequence. ..."
Which to me means the opposite is true also.
Though you can:
" Specifying OWNED BY NONE removes any existing association, making
the sequence “free-standing”.
"
I also missed the part in the article where it talks about assigning
ownership to the column. In psql, it's easy to miss because a \ds (or
\ds+) (as opposed to a \d) shows the user that owns the sequence, not a
column:test=# \ds+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-------------------+----------+-------+------------+-------------
public | t_serial_id_seq | sequence | u1 | 8192 bytes |Whereas if you look at \d it shows the column:
test=# \d+ t_serial_id_seq
Sequence "public.t_serial_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
integer | 1 | 1 | 2147483647 | 1 | no | 1
Owned by: public.t_serial.id <http://public.t_serial.id>(Side note: it is surprising that the Size and Description don't show up
with \d+. I always thought that a \d+ was the best way to get all the
detail on an object.)But even if you drop the default on the column, it doesn't seem like you
can change the sequence's owner:test=# ALTER TABLE t_serial ALTER COLUMN id DROP DEFAULT;
ALTER TABLE
test=# alter sequence t_serial_id_seq OWNER TO u2;
ERROR: cannot change owner of sequence "t_serial_id_seq"
DETAIL: Sequence "t_serial_id_seq" is linked to table "t_serial".Although you can drop it:
DROP SEQUENCE t_serial_id_seq;
DROP SEQUENCEAnyhoo, I've learned a bit more today, and thanks for your help!
Cheers,
Ken--
AGENCY Software
A Free Software data system
By and for non-profits
/http://agency-software.org//
/https://demo.agency-software.org/client/
ken.tanzer@agency-software.org <mailto:ken.tanzer@agency-software.org>
(253) 245-3801Subscribe to the mailing list
<mailto:agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
--
Adrian Klaver
adrian.klaver@aklaver.com
Hi Rich
I think you may have serial already there (indicated by sequence in the the
default value). If you wish to change to identity columns this should be
useful: https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/
Regards
Peter
On Tue, 23 Apr 2019, Peter Devoy wrote:
I think you may have serial already there (indicated by sequence in the the
default value).
Peter,
As serial is an alias for integer (the actual data type) using integer and
specifying it as the primary key works.
If you wish to change to identity columns this should be useful:
https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/
Nah, I don't see a reason to change. Identity columns obviously have a
benefit for some but not for me.
Thanks for the suggestion,
Rich