Início / dba / Perguntas / 64270
Accepted
davetapley
Asked: 2014-05-01 22:09:13 +0800 CST

Chave primária nos dados JSON no Postgresql

  • 772

Tenho uma tabela t, com uma coluna chamada json, do tipo JSON. Dentro do JSON existe uma chave natural :

> SELECT json->'id' AS id FROM t LIMIT 1;
             id              
-----------------------------
 " 63631ff3809de7a17398602f"

Posso criar um UNIQUE INDEXon id, assim:

> CREATE UNIQUE INDEX t_id ON t((json->>'id'));                                                                                                                       

CREATE INDEX

Eu gostaria de adicionar isso como um table_constraint_using_index , mas falha para ambos PRIMARY KEY:

> ALTER TABLE t ADD CONSTRAINT t_pkey PRIMARY KEY USING INDEX t_id;                                                                                   
ERROR:  index "t_id" contains expressions
LINE 1: ALTER TABLE t ADD CONSTRAINT t_pkey
                                          ^
DETAIL:  Cannot create a primary key or unique constraint using such an index.

e UNIQUE:

> ALTER TABLE t ADD CONSTRAINT t_unique_id UNIQUE USING INDEX t_id;
ERROR:  index "t_id" contains expressions
LINE 1: ALTER TABLE t ADD CONSTRAINT t_unique_id...
                                          ^
DETAIL:  Cannot create a primary key or unique constraint using such an index.

Devo ser capaz de adicionar tal restrição?

postgresql-9.3 constraint

2 respostas

  1. Best Answer

    Não, você não deve ser capaz de adicionar tal restrição.

    O PRIMARY KEYdeve ser um índice simples ou composto. Pode não ser um índice parcial ou de expressão.

    O índice atua como uma restrição e, funcionalmente, é praticamente o mesmo, mas não pode aparecer como PRIMARY KEYnos metadados da tabela e não pode ser usado como o destino de uma restrição de chave estrangeira. O mesmo vale para as UNIQUErestrições.

    PRIMARY KEYO problema aqui é que as definições e restrições do padrão SQL UNIQUEnão permitem expressões ou predicados de correspondência de linha. Portanto, se o PostgreSQL listar um índice de expressão ou índice parcial como uma restrição, ele está quebrando o padrão e mentindo para os aplicativos sobre o que está fazendo. Os aplicativos que entendem os recursos do PostgreSQL podem procurar o índice nos próprios catálogos do Pg e também há informações em information_schema, mas não podem entrar como uma restrição listada.

    • 6

  2. Conforme explicado na resposta de Craig, nem PRIMARY KEYnem UNIQUEpode ser usado.

    No entanto, aqui está uma solução em duas partes para contornar isso:

    • Para impor not null , use uma restrição de verificação , assim:

      ALTER TABLE t ADD CONSTRAINT id_not_null CHECK ((json->>'id') IS NOT NULL);

    • Para impor exclusividade , crie um índice exclusivo*, assim:

      CREATE UNIQUE INDEX t_id ON t ((json->>'id'))

    Depois disso, um \d ton in psqllhe dará:

    Indexes:
    "t_id" UNIQUE, btree ((json ->> 'id'::text))
Check constraints:
    "id_not_null" CHECK ((json ->> 'id'::text) IS NOT NULL)

    E você pode ver que não nulos e exclusivos são aplicados:

    test=# INSERT INTO t VALUES ('{"no id" : "field here"}');
ERROR:  new row for relation "t" violates check constraint "id_not_null"
DETAIL:  Failing row contains ({"no id" : "field here"}).

test=# INSERT INTO t VALUES ('{"id" : "abc"}');
INSERT 0 1

test=# INSERT INTO t VALUES ('{"id" : "abc"}');
ERROR:  duplicate key value violates unique constraint "t_id"
DETAIL:  Key ((json ->> 'id'::text))=(abc) already exists

    * observe que a mera existência do t_idíndice é suficiente para impor a exclusividade, mesmo que não seja explicitamente fornecida como uma restrição.

    • 6

relate perguntas