Как создать эквивалент столбца идентификаторов SQL Server в Postgres


Есть приложение, которое использует MSSQL в качестве своего бэк-энда. Сейчас я разрабатываю код, чтобы он мог использовать PostgreSQL. Я почти завершил его, за исключением одного отличия при выполнении:

При сохранении нового приложения,

Код SQL Server:

create table tower 
(
  npages integer, 
  ifnds integer, 
  ifnid integer, 
  name varchar(20), 
  towid integer not null IDENTITY
)

PostgreSQL код:

create table tower 
(
  npages integer, 
  ifnds integer, 
  ifnid integer, 
  name varchar(20)
)

Почему поле towid (которое является полем по умолчанию) не генерируется автоматически при выполнении через PostgreSQL ?

Какая-нибудь возможная причина? Триггеры? Процедуры?

2   2   2017-01-11 09:06:08

2 ответа:

PostgreSQL создаст таблицу точно так, как вы просили. Он не будет генерировать какой-то столбец автоматически (почти, но это низкий уровень). Вам нужно добавить towid serial primary key к вашему запросу.

Tl; dr

Теперь в Postgres 10 укажите GENERATED BY DEFAULT AS IDENTITY в соответствии со стандартом SQL.

create table tower 
(
  npages integer, 
  ifnds integer, 
  ifnid integer, 
  name varchar(20), 
  towid integer GENERATED BY DEFAULT AS IDENTITY    -- per SQL standard
)

Столбец идентичности

Postgres 10 теперь поддерживает концепциюстолбца идентификаторов и использует стандартный синтаксис SQL. Хотя я не эксперт по MS SQL Server, я считаю, что эта новая стандартная поддержка эквивалентна.

GENERATED … AS IDENTITY

Команда GENERATED … AS IDENTITY, используемая в CREATE TABLE, создает неявную последовательность. Создание, именование, разрешения и удаление этой последовательности прозрачны вам, в отличие от SERIAL. Очень интуитивный сейчас. Если вы даете разрешение на использование таблицы, они получают разрешение на последовательность. Если вы отбрасываете таблицу, последовательность отбрасывается автоматически.

Два варианта стандартного синтаксиса. Разница имеет значение только в том случае, если вы передаете значение, а не позволяете ему генерироваться. Как правило, люди всегда полагаются на созданное значение, поэтому обычно вы просто используете первую версию, GENERATED BY DEFAULT AS IDENTITY.

  • GENERATED BY DEFAULT AS IDENTITY
    • генерирует значение если только команда INSERT не предоставляет значение.
  • GENERATED ALWAYS AS IDENTITY
    • игнорирует любое значение, предоставленное INSERT, если не указано OVERRIDING SYSTEM VALUE

См.CREATE TABLE страница для документации.

Далее этой интересной страницы от Peter Eisentraut. Он объясняет некоторые странные проблемы с SERIAL. Нет таких проблем с новой функцией столбца идентификаторов. Так что больше нет причин использовать SERIAL, никаких минусов, только плюсы; SERIAL есть вытеснено GENERATED … AS IDENTITY.

Обратите внимание, что столбец идентификаторов не обязательно является первичным ключом и не индексируется автоматически. Поэтому вам все равно нужно явно указать PRIMARY KEY, если это ваше намерение (как это обычно бывает).
CREATE TABLE person_ (

    id_ 
        INTEGER 
        GENERATED BY DEFAULT AS IDENTITY   -- Replaces SERIAL. Implicitly creates a SEQUENCE, specified as DEFAULT.
        PRIMARY KEY                        -- Creates index. Specifies UNIQUE. Marks column for relationships.
        ,

    name_ 
        VARCHAR( 80 )

) ;
Намерение состоит в том, чтобы внутренние детали реализации были скрыты от вас. Вам не нужно знать имя последовательности, генерируемой под обложками. Например, вы можете сбросить счетчик через столбец, не зная основная последовательность.
ALTER TABLE person_ 
    ALTER COLUMN id_ 
    RESTART WITH 1000      -- Reset sequence implicitly, without a name.
;

Указание идентичности неявно:

  • маркирует колонку NOT NULL
  • создает последовательность
    • Тип последовательности соответствует столбцу (32-разрядный 64-разрядный и т. д. )
  • привязывает последовательность к столбцу
    • наследует разрешения
    • каскады падают
    • остается привязанным к столбцу, даже если столбец переименован
  • задает последовательность в качестве источника значений по умолчанию для этого колонка

Столбец identity может принимать те же параметры, что и CREATE SEQUENCE:

  • START WITH start
  • MINVALUE minvalue | NO MINVALUE
  • MAXVALUE maxvalue / NO MAXVALUE
  • INCREMENT [ BY ] increment
  • CYCLE / NO CYCLE
  • CACHE кэш
  • OWNED BY NONE
    ( указание права собственности для столбца identity не имеет смысла для меня, поскольку владение управляется автоматически)

Глупый пример вариантов:

id_ INTEGER 
GENERATED BY DEFAULT AS IDENTITY ( 
    START WITH 200 
    MINVALUE 100 
    MAXVALUE 205 
    CYCLE 
    INCREMENT BY 3 
) PRIMARY KEY

Добавление 4 строк:

200

203

100

103