Re: Primary key data type: integer vs identity
От | Adrian Klaver |
---|---|
Тема | Re: Primary key data type: integer vs identity |
Дата | |
Msg-id | c1df337c-8819-711b-496e-459e7fb88408@aklaver.com обсуждение исходный текст |
Ответ на | Primary key data type: integer vs identity (Rich Shepard <rshepard@appl-ecosys.com>) |
Ответы |
Re: Primary key data type: integer vs identity
(Rich Shepard <rshepard@appl-ecosys.com>)
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: