SELECT statement in MySQL and PostgreSQL

0 like 0 dislike
5 views
Good day, recently decided to use PostgreSQL and I noticed one peculiarity, SELECT Postres registercustomer. Earlier in MySQL I wanted to write testTest and asked it anything else, it was given, now it does not work. Is it possible to build the query in Postgres, so it's not case sensitive?
by | 5 views

6 Answers

0 like 0 dislike
SELECT * FROM table WHERE lower(column) = 'testtest'; 
or use ILIKE.
by
0 like 0 dislike
Apparently it's in the encoding. More precisely in the collation.
In mysql you used any of the options are case independent collation. for example utf8_general_ci which is the default when you select utf8.
If you use the function lower, then perhaps the index is not going to work.
Maybe you made a mistake when setting the encoding for base?
by
0 like 0 dislike
Postgres by default makes the field names and tables to lowercase. For example, if you have a field testTest in table my_table, the query SELECT testTest FROM my_table will return an error that the fields testtest no. To get the value of this field must enclose the name in quotation marks. The query SELECT "testTest" FROM my_table will work fine.
by
0 like 0 dislike
No need to field and not the value from the field.
\r
SELECT * FROM table WHERE = 'testtest';
\r
or
\r
WHERE = 'TestTest';
\r
gave me the entry testTest, which is exactly what is written.
\r
The option that offered DevMan less suitable, but to apply to the input value in the lower register not always convenient for me.
by
0 like 0 dislike
WHERE column ILIKE 'TestTest' choice, Thanks to DevMan.
by
0 like 0 dislike
Now the logical questions about speed.
\r
ILIKE — "They tend to be slow because there is no index support, so they must process all documents for every search."
\r
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."
\r
What would be more logical to use in the search for a record which is UNIQUE and PRIMARY KEY from ~100tysyach.
\r
Although perhaps my comment replies.
by

Related questions

0 like 0 dislike
2 answers
0 like 0 dislike
1 answer
0 like 0 dislike
2 answers
0 like 0 dislike
4 answers
asked Mar 24, 2019 by potapuff
110,608 questions
257,186 answers
0 comments
28,888 users