Início / dba / Perguntas / 147645
Accepted
Chloe
Asked: 2016-08-24 11:07:33 +0800 CST

Como esse índice exclusivo pode permitir linhas duplicadas?

  • 772

Existe uma maneira de esse índice exclusivo permitir linhas duplicadas? Achei que talvez houvesse alguns caracteres espaciais extras, mas não consigo encontrá-los.

=> select *, length(keyword), length(country), length(language) from keyword where id in (4588076, 4951423);
   id    |       keyword       | seed_id | source | search_count | country | language | volume | cpc  | competition | modified_on | violation | revenue | length | length | length
---------+---------------------+---------+--------+--------------+---------+----------+--------+------+-------------+-------------+-----------+---------+--------+--------+--------
 4588076 | power wallet review |         | SPYFU  |            0 |         |          |     70 | 0.11 |        0.31 |             |           |         |     19 |        |
 4951423 | power wallet review |         | SPYFU  |            2 |         |          |     70 | 0.11 |        0.31 |             |           |         |     19 |        |
(2 rows)

O índice é

"keyword_keyword_country_language" UNIQUE, btree (keyword, country, language)

PostgreSQL 9.5.3

OK, eu estava planejando remover as outras duas colunas, mas pensei em testar a keywordcoluna e encontrei isto:

=> select k1.id, k1.keyword, k2.id, k2.keyword, k1.keyword=k2.keyword from keyword k1, keyword k2 where k1.id=4588076 and k2.id=4951423;
   id    |       keyword       |   id    |       keyword       | ?column?
---------+---------------------+---------+---------------------+----------
 4588076 | power wallet review | 4951423 | power wallet review | f
postgresql index

2 respostas

  1. A resposta pode ser encontrada na incrível documentação .. PARECE que você tem valores NULL em suas tabelas .. Quando o banco de dados verifica a exclusividade, ele diz "NULL é igual a NULL? NOPE!" e o permite.

    A parte importante abaixo (ênfase minha):

    não é permitido. Valores nulos não são considerados iguais. Um exclusivo de várias colunas

    Se você quiser manter a exclusividade em TODAS AS TRÊS COLUNAS e, ao mesmo tempo, tratar nulos como iguais, então você deve ser criativo com seus índices UNIQUE tornando-os índices parciais.

    CREATE UNIQUE INDEX ix1 ON table (col1, col2wNull, col3wNull) WHERE col2wNull is not NULL and col3wNull is not Null;
CREATE UNIQUE INDEX ix2 ON table (col1, col2wNull) WHERE col2wNull is not Null and col3wNull is Null;
CREATE UNIQUE INDEX ix3 ON table (col1, col3wNull) WHERE col2wNull is Null and col3wNull is not Null;
CREATE UNIQUE INDEX ix4 ON table (col1) WHERE col2wNull is NULL and col3wNull is NULL;

    Como você pode ver .. pode facilmente ficar um pouco louco.

    Uma alternativa diferente seria definir col2wNull e col3wNull como NOT NULL e fornecer algum valor padrão para quando nada for fornecido. ISSO PODE SER OU NÃO UMA BOA IDEIA , dependendo do que você estiver fazendo. Os "valores mágicos" tendem a causar muitos problemas mais tarde.

    Com relação à sua edição e às duas strings que parecem iguais, mas o banco de dados relata que não são - só posso imaginar que existem alguns caracteres "invisíveis" (UTF-8?) Que estão na string. Ou pode ser algo tão simples quanto uma string com um espaço adicional no final. Depende parcialmente de como você está salvando no banco de dados. (Você está executando um trim() neles, lower(), etc..)

    Você pode tentar comparar as strings de várias outras maneiras (como olhar para o hash md5). Acredito que você também pode pedir ao postgres para converter o valor da coluna em hexadecimal para visualizar, mas como fazer isso está me escapando no momento (minhas desculpas).

    • 6
  2. Best Answer

    OK, descobri que havia um espaço ininterrupto no texto, que usava 2 bytes, embora o Postgres dissesse que eles tinham o mesmo tamanho.

    => select k1.id, k1.keyword, k2.id, k2.keyword, k1.keyword=k2.keyword, encode(convert_to(k1.keyword, 'UTF8'), 'hex'), encode(convert_to(k2.keyword, 'UTF8'), 'hex') from keyword k1, keyword k2 where k1.id=4588076 and k2.id=4951423;
   id    |       keyword       |   id    |       keyword       | ?column? |                  encode                  |                 encode
---------+---------------------+---------+---------------------+----------+------------------------------------------+----------------------------------------
 4588076 | power wallet review | 4951423 | power wallet review | f        | 706f7765722077616c6c6574c2a0726576696577 | 706f7765722077616c6c657420726576696577
(1 row)

    Os dois valores hexadecimais foram

    706f7765722077616c6c6574c2a0726576696577
706f7765722077616c6c657420726576696577

    A diferença era c2a0. PHP:

    >>> hex2bin('c2a0')
=> " "

    Pesquisei c2a0e descobri que era um espaço ininterrupto. http://www.fileformat.info/info/unicode/char/00a0/index.htm

    Eu não sei de qualquer maneira para evitar isso em um índice único. Você?

    A outra resposta também merece algum crédito.

    • 0

relate perguntas