From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> |
Cc: | Rich Shepard <rshepard(at)appl-ecosys(dot)com>, "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Primary key data type: integer vs identity |
Date: | 2019-04-19 19:50:38 |
Message-ID: | 2752f2d1-2cec-1df4-21f7-b0b004f8e500@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Ken Tanzer | 2019-04-19 20:02:10 | Re: Primary key data type: integer vs identity |
Previous Message | Ken Tanzer | 2019-04-19 19:35:00 | Re: Primary key data type: integer vs identity |