SELECT в MySQL и PostgreSQL

Question

[Алексей]

Добрый день, недавно надумал пользоваться PostgreSQL и заметил одну особенность, SELECT в Postres регистрочувствителен. Если раньше в MySQL я хотел получить запись testTest и запрашивал его как угодно, оно отдавалась, то теперь это не получается. Можно ли как-то строить запрос в Postgres, чтоб он был не регистрозависимым?

Answer 1

[DevMan]

SELECT *
FROM table
WHERE lower(column) = 'testtest';

или использовать ILIKE.

Comments

[DevMan]

А какая версия PostgreSQL?

Answer 2

[pwlnw]

Судя по всему дело в кодировке. Точнее в collation.
В mysql вы использовали какой-то из вариантов case independent collation. например utf8_general_ci, который по умолчанию задается при выборе utf8.
Если вы будете использовать функцию lower, то, наверное, индекс не сработает.
Может вы ошиблись при настройке кодировки для базы?

Comments

[DevMan]

В PostgreSQL нет collation в том виде как у мускуля.

[pwlnw]

Ну и ладно. Прекрасная иллюстрация мифической «более полной поддержки стандартов в postresql» будет.

[Алексей]

да, в MySQL была utf8_general_ci, энивэй, ILIKE мне помог и делает именно то, что нужно.

[pwlnw]

ну а план как выглядит? индекс используется?

[pwlnw]

вот нашел:
PostgreSQL currently only supports one collation at a time, as fixed by the LC_COLLATE variable at the time the database cluster is initialised.

Может быть взять и настроить эту collation, так чтобы кода поменьше пришлось переписывать?

Answer 3

[peter23]

Postgres по умолчанию сводит названия полей и таблиц к нижнему регистру. Например, если есть поле testTest в таблице my_table, то запрос SELECT testTest FROM my_table вернет ошибку, что поля testtest нет. Чтобы получить значение этого поля нужно заключать название в кавычки. Запрос SELECT "testTest" FROM my_table отработает нормально.

Comments

[DevMan]

Вопрос как бы не об этом

[peter23]

Я хотел сказать, что если использовать синтаксис MySQL и не заключать названия полей в кавычки, то без разницы как писать имена, запрос все равно будет к названию в нижнем регистре. Это касается и INSERT и SELECT.

Answer 4

[Алексей]

Нет, нужно получить не поле а значение из поля.

SELECT * FROM table WHERE = 'testtest';

или

WHERE = 'TestTest';

выдавал мне запись testTest, которая именно так и записана.

Вариант, который предложил DevMan более менее подходит, но подавать на вход значение в нижнем регистре мне не всегда удобно.

Comments

[DevMan]

Не вопрос

SELECT *
FROM table
WHERE lower(column) = lower('testTest');

[JeanLouis]

Так лучше не делать, не будут использованы индексы. ILIKE лучше как вы выше сказали.

[DevMan]

Лучше вообще использовать citext.

[ec2]

Если используются только такие запросы, то можно сделать индекс с использованием функций:
create index on table ((lower(column));

Answer 5

[Алексей]

WHERE column ILIKE 'TestTest' подходит, Спасибо DevMan.

Answer 6

[Алексей]

Теперь логичные вопросы про скорость.

ILIKE — «They tend to be slow because there is no index support, so they must process all documents for every search.»

citex — «If you declare a column as UNIQUE or PRIMARY KEY, the implicitly generated index is case-sensitive. So it's useless for case-insensitive searches, and it won't enforce uniqueness case-insensitively.»

Что будет логичнее использовать при поиски одной записи являющейся UNIQUE и PRIMARY KEY из ~100тысяч.

Хотя наверно мой комментарий сам отвечает.

Comments

[Алексей]

поиске*

[DevMan]

Прочитайте ман внимательно:

If you declare a column as UNIQUE or PRIMARY KEY, the implicitly generated index is case-sensitive. So it's useless for case-insensitive searches, and it won't enforce uniqueness case-insensitively

относится не к citex.