SELECT in MySQL and PostgreSQL

Question

PostgreSQL

Good afternoon, I recently decided to use PostgreSQL and noticed one feature, SELECT in Postres is case sensitive. If earlier in MySQL I wanted to get the testTest record and requested it in any way, it was given, but now it does not work. Is it possible to somehow build a query in Postgres so that it is not case-sensitive?

Answer 1

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

or use ILIKE.

Answer 2

It looks like it's a matter of coding. More precisely in collation.
In mysql, you used one of the case independent collation options. for example utf8_general_ci which is set by default when utf8 is selected.
If you use the lower function, then the index will probably not work.
Maybe you made a mistake when setting the encoding for the base?

Answer 3

Postgres reduces field and table names to lowercase by default. For example, if there is a testTest field in the my_table table, then the query SELECT testTest FROM my_tablewill return an error that there is no testtest field. To get the value of this field, enclose the name in quotation marks. The request will SELECT "testTest" FROM my_tablework fine.

Answer 4

No, it is necessary to receive not a field and value from a field.
SELECT * FROM table WHERE = 'testtest';
or
WHERE = 'TestTest';
gave me the testTest entry, which is exactly how it was written.
The option that DevMan suggested is more or less suitable, but it’s not always convenient for me to supply a lowercase value to the input.

Answer 5

WHERE column ILIKE 'TestTest' is fine, Thanks DevMan.

Answer 6

Now logical questions about speed.
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."
What would be more logical to use when looking for a single record that is UNIQUE and PRIMARY KEY from ~ 100 thousand.
Although my comment probably answers itself.