Re: Primary key data type: integer vs identity

On 4/19/19 2:31 PM, Ken Tanzer wrote:
> On Fri, Apr 19, 2019 at 1:39 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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 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//
> /https://demo.agency-software.org/client/
> ken(dot)tanzer(at)agency-software(dot)org <mailto:ken(dot)tanzer(at)agency-software(dot)org>
> (253) 245-3801
>
> Subscribe to the mailing list
> <mailto:agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com