Re: Primary key data type: integer vs identity

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(dot)klaver(at)aklaver(dot)com