Условие для индекса

Question

Есть таблица с индексом который необходимо модифицировать так, чтобы оно ограничивало еще и поле type таким образом чтобы type мог принимать только три значения LONG, SHORT, UNIVERSAL.

По правилу: либо строка должна быть UNIVERSAL тогда никаких дубликатов по комбинации symbol_id, account_id, enabled. Либо допускать один LONG, и один SHORT для комбинации symbol_id, account_id, enabled.

Или другими словами c уникальной комбинацией symbol_id, account_id, enabled где enabled == TRUE может быть либо:

Один универсальный инстанс (UNIVERSAL это означает что он работает и как LONG, и как SHORT).
Два инстанса, один LONG, и один SHORT.
У любого инстанса хотя бы один LONG, или SHORT должен быть true

LONG, и SHORT могут существовать и по отдельности то есть на пример LONG есть, а SHORT отсутствует и не планируется. Либо наоборот.

-- Instances
CREATE TABLE IF NOT EXISTS instances
(
    id            BIGSERIAL PRIMARY KEY,
    enabled       BOOLEAN      NOT NULL,
    title         VARCHAR(255) NOT NULL,
    type          VARCHAR(255) NOT NULL,
    status        VARCHAR(255) NOT NULL,
    updated_at    TIMESTAMP NOT NULL,
    last_callback TIMESTAMP,
    settings      VARCHAR(255),
    user_id       BIGINT REFERENCES users (id),
    symbol_id     BIGINT REFERENCES symbols (id),
    account_id    BIGINT REFERENCES accounts (id),
    strategy_id   BIGINT REFERENCES strategies (id)
);

И индекс:

CREATE UNIQUE INDEX ui_symbol_account_enabled
    ON instances (symbol_id, account_id, enabled)
    WHERE (enabled IS TRUE);

Вероятно это должно быть что-то вроде:

CREATE UNIQUE INDEX ui_symbol_account_enabled_type
    ON instances (symbol_id, account_id, enabled, type)
    WHERE (enabled IS TRUE
        AND (type = 'UNIVERSAL' OR type = 'SHORT' OR type = 'LONG')
        );

Только не одно из 3х значений а по описанному выше правилу. Как это правильно написать?

Answer 1

Если изменения схемы допустимы, то предлагаю вот такой (упрощённый) вариант:

CREATE TABLE t_1 (
  symbol_id  BIGINT  NOT NULL
, account_id BIGINT  NOT NULL
, enabled    BOOLEAN NOT NULL
, long       BOOLEAN NOT NULL
, short      BOOLEAN NOT NULL
, CHECK (long OR short)
)
;

CREATE UNIQUE INDEX
    ON t_1(symbol_id, account_id)
 WHERE (enabled AND short)
;

CREATE UNIQUE INDEX
    ON t_1(symbol_id, account_id)
 WHERE (enabled AND long)
;

Соответственно ваш тип 'UNIVERSAL' превращается в запись, где выполяется условие long AND short.

Если схему менять нельзя, то:

CREATE UNIQUE INDEX
    ON t_1(symbol_id, account_id)
 WHERE (enabled AND type IN ('UNIVERSAL', 'LONG'))
;

CREATE UNIQUE INDEX
    ON t_1(symbol_id, account_id)
 WHERE (enabled AND type IN ('UNIVERSAL', 'SHORT'))
;

БЛОГ НА HUSL

Условие для индекса

Ответы (1 шт):