PostgreSQL: Czy możesz utworzyć indeks w definicji CREATE TABLE?

105

Chcę dodać indeksy do niektórych kolumn w tabeli podczas tworzenia. Czy istnieje sposób, aby dodać je do definicji CREATE TABLE, czy też muszę dodać je później za pomocą innego zapytania?

CREATE INDEX reply_user_id ON reply USING btree (user_id);

postgresql database-schema database-indexes

— Xeoncross
źródło

119

Wydaje się, że nie ma sposobu na określenie indeksu w CREATE TABLEskładni. PostgreSQL domyślnie tworzy jednak indeks dla ograniczeń unikatowych i kluczy podstawowych, jak opisano w tej uwadze :

PostgreSQL automatycznie tworzy indeks dla każdego ograniczenia unikatowego i ograniczenia klucza podstawowego, aby wymusić unikalność.

Poza tym, jeśli chcesz mieć nieunikalny indeks, musisz utworzyć go samodzielnie w osobnym CREATE INDEXzapytaniu.

— pozbyć się
źródło

Dzięki, nie wiedziałem o unikalnym ograniczeniu tworzenia indeksu.

— Xeoncross

Zwróć uwagę, że PostgreSQL obsługuje aktualizacje schematów transakcyjnych - dobrym pomysłem jest rozpoczęcie / zatwierdzenie instrukcji CREATE TABLE i CREATE INDEX, jeśli chcesz, aby ogólne tworzenie tabeli netto zakończyło się sukcesem lub niepowodzeniem jako całości.

— mindplay.dk

22

Nie.

Możesz jednak tworzyć uniqueindeksy podczas tworzenia, ale to dlatego, że są one klasyfikowane jako ograniczenia . Nie możesz utworzyć indeksu „ogólnego”.

— Czeski
źródło

6

Peter Krauss szuka kanonicznej odpowiedzi:

Istnieje NOWOCZESNA SKŁADNIA (rok 2020), więc proszę wyjaśnij i pokaż przykłady, zgodne z postgresql.org/docs/current/sql-createtable.html

Poszukujesz definicji indeksu wbudowanego , która nie jest dostępna dla PostgreSQL do aktualnej wersji 12. Z wyjątkiem ograniczenia UNIQUE / PRIMARY KEY, które tworzy indeks bazowy.

UTWÓRZ TABELĘ

[CONSTRAINT nazwa_ograniczenia] {CHECK (wyrażenie) [NO INHERIT] | UNIQUE (nazwa_kolumny [, ...]) parametry_indeksu | PRIMARY KEY (nazwa_kolumny [, ...]) parametry_indeksu |

Przykładowa składnia definicji kolumn wbudowanych (tutaj SQL Server):

CREATE TABLE tab(
  id INT PRIMARY KEY,                            -- constraint
  c INT INDEX filtered (c) WHERE c > 10,         -- filtered index
  b VARCHAR(10) NOT NULL INDEX idx_tab_b,        -- index on column
  d VARCHAR(20) NOT NULL,
  INDEX my_index NONCLUSTERED(d)                 -- index on column as separate entry
);

db <> fiddle demo

Uzasadnienie ich wprowadzenia jest dość interesujące. Czym są indeksy wbudowane? przez Phil Factor

— Łukasz Szozda
źródło

Cześć, rozwiązane (!). Czy możesz dodać coś o bardziej złożonym przypadku, na przykład b VARCHAR(10) NOT NULL INDEX idx_tab_b gin (b gin_trgm_ops).. jak dodać więcej parametrów bez powtarzania nazwy kolumny?

— Peter Krauss

@PeterKrauss Podany przeze mnie przykład dotyczy SQL Server, który nie obsługuje gin_trgm_ops.

— Łukasz Szozda

Cześć @LukaszSzozda Jeśli chodzi o składnię, być może możesz spróbować przekształcić dowolny rodzaj kompleksu CREATE INDEX idxName ON tableName USING MethodName (fieldName optionName);w składnię definicji indeksu wbudowanego . PS: to pytanie dotyczy PostgreSQL, a nie Microsoft-SQL-Server (zobacz tagi).

— Peter Krauss,

@PeterKrauss Jestem w pełni świadomy, że to pytanie dotyczy PostgreSQL. Właśnie podałem właściwą nazwę tej funkcji i pokazałem przykład, jak to może wyglądać. Miejmy nadzieję, że więcej RDBMS będzie obsługiwać indeksy inlinde w przyszłości :)

— Łukasz Szozda