0

I've written a query in MySQL which is :-

SELECT * FROM `employees` WHERE ((first_name LIKE "s%"  OR middle_name LIKE "s%" OR last_name LIKE "s%")) ORDER BY first_name ASC;

I tried to executed this query in PostgreqSQL but it gives an error which says column "s%" does not exist.

After that I put single quote in LIKE statements instead of double qoutes.

SELECT * FROM `employees` WHERE ((first_name LIKE 's%'  OR middle_name LIKE 's%' OR last_name LIKE 's%')) ORDER BY first_name ASC;

Now PostgreSQL executes that query but it returns an empty set. I'm using postgreSQL 8.3

Improve this question

4 Answers 4

Reset to default
3

The second statement will not run in PostgreSQL due to the invalid backticks around employees - but maybe that is a copy & paste error.

If you actually removed those dreaded backticks (which are not even necessary in MySQL) most probably the second statement did not return any values, because string comparison in PostgreSQL is case-sensitive and your columns contain a captial S instead of s

Try: 

SELECT * 
FROM employees 
WHERE ((lower(first_name) LIKE 's%'  OR 
        lower(middle_name) LIKE 's%' OR 
        lower(last_name) LIKE 's%')) 
ORDER BY first_name ASC;
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

ILIKE would be another option but that's not standard SQL.
@mu: true. Would ILIKE be able to use an index? LIKE could use a function based index on lower(col_name) in this case.
Not sure, I'd have to ask the query plan. I'd expect PostgreSQL to be clever enough to treat them equivalently but expectations and reality aren't the same thing. Shame that the varied capitalizations of names wouldn't let us force all three columns to title case so that x LIKE 'S%' would work, a pattern like that certainly could use an index.
You are right those backticks are causing problem but I'm using ruby on rails so activeRecord is generating the queries here. Is there any way of preventing activeRecords from adding them in the query? I'm using activeRecords 2.3.5
Sounds like you need a different database adpater. And there have been about 30 releases of ActiveRecord since 2.3.5. wiki.rubyonrails.org/database-support/postgres
|
1

Postgres defaults to case-sensitive LIKE statements, whereas MySQL defaults to case-insensitive.

Use the ILIKE operator to get the same behavior as MySQL.

Improve this answer

1 Comment

+1 for mentioning ILIKE operator for case insensitive search.
1

Here's an example, I'll use generated "names" which are actually numbers, which should work the same...

Note that if you want the search to ignore accents, like MySQL does, you'll need to replace lower() with a function that turns accented characters into non-accented ones. Since that function will probably be slow, it would be better to materialize the column.

If you want LIKE to use an index, don't forget to use the special index type text_pattern_ops !

create table test (a TEXT, b TEXT, c TEXT);
INSERT INTO test SELECT n, n*3, n*7 FROM generate_series( 1,100000 ) n;
create index test_a on test( lower( a ) text_pattern_ops );
create index test_b on test( lower( b ) text_pattern_ops );
create index test_c on test( lower( c ) text_pattern_ops );


EXPLAIN ANALYZE SELECT * FROM test WHERE lower(a) LIKE '12%' OR lower(b) LIKE '12%' OR lower(c) LIKE '12%' ORDER BY lower(a) LIMIT 10;
                                                               QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1081.12..1081.15 rows=10 width=17) (actual time=16.987..16.992 rows=10 loops=1)
   ->  Sort  (cost=1081.12..1096.04 rows=5969 width=17) (actual time=16.986..16.986 rows=10 loops=1)
         Sort Key: (lower(a))
         Sort Method:  top-N heapsort  Memory: 25kB
         ->  Bitmap Heap Scan on test  (cost=144.68..952.13 rows=5969 width=17) (actual time=1.107..13.261 rows=6405 loops=1)
               Recheck Cond: ((lower(a) ~~ '12%'::text) OR (lower(b) ~~ '12%'::text) OR (lower(c) ~~ '12%'::text))
               Filter: ((lower(a) ~~ '12%'::text) OR (lower(b) ~~ '12%'::text) OR (lower(c) ~~ '12%'::text))
               ->  BitmapOr  (cost=144.68..144.68 rows=6341 width=0) (actual time=1.081..1.081 rows=0 loops=1)
                     ->  Bitmap Index Scan on test_a  (cost=0.00..22.95 rows=1068 width=0) (actual time=0.210..0.210 rows=1111 loops=1)
                           Index Cond: ((lower(a) ~>=~ '12'::text) AND (lower(a) ~<~ '13'::text))
                     ->  Bitmap Index Scan on test_b  (cost=0.00..81.85 rows=3758 width=0) (actual time=0.603..0.603 rows=3707 loops=1)
                           Index Cond: ((lower(b) ~>=~ '12'::text) AND (lower(b) ~<~ '13'::text))
                     ->  Bitmap Index Scan on test_c  (cost=0.00..35.42 rows=1515 width=0) (actual time=0.266..0.266 rows=1587 loops=1)
                           Index Cond: ((lower(c) ~>=~ '12'::text) AND (lower(c) ~<~ '13'::text))

Total runtime: 17.038 ms

Here the OR are turned into bitmap-OR index scans which are pretty fast. Note here, the first two "letters" are used, which gives a selectivity of 1%. If you use only one letter you might get pretty low selectivity and a huge result list which is unlikely to be useful, so better wait until the user types 2 chars before firing up that autocompletion.

Using UNION isn't faster.

But there is another hack ! Look :

select * from (values ('a'),('b'),('A'),('Â')) foo where column1 >= 'a' AND column1 < 'b';
 column1 
---------
 a
 A
 Â

ok...

create index test_aa on test( a );
create index test_ba on test( b );
create index test_ca on test( c );

Here you'd need to take the string typed by the user (for instance, 'no'), remove accents and lowercase it, and increment the last character (which gives you 'np'). All names that start with 'no' have the property that name >= 'no' and name < 'np', because of collation rules, and it includes accents.

EXPLAIN ANALYZE 
SELECT * FROM (SELECT * FROM test WHERE a >= '12' AND a < '13' ORDER BY a LIMIT 10) f1
UNION
SELECT * FROM (SELECT * FROM test WHERE b >= '12' AND b < '13' ORDER BY b LIMIT 10) f2
UNION
SELECT * FROM (SELECT * FROM test WHERE c >= '12' AND c < '13' ORDER BY c LIMIT 10) f3
ORDER BY a;

                                                                  QUERY PLAN                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=27.20..27.27 rows=30 width=96) (actual time=0.451..0.456 rows=30 loops=1)
   Sort Key: public.test.a
   Sort Method:  quicksort  Memory: 27kB
   ->  HashAggregate  (cost=26.16..26.46 rows=30 width=96) (actual time=0.399..0.404 rows=30 loops=1)
         ->  Append  (cost=0.00..25.94 rows=30 width=96) (actual time=0.157..0.372 rows=30 loops=1)
               ->  Limit  (cost=0.00..7.35 rows=10 width=17) (actual time=0.157..0.163 rows=10 loops=1)
                     ->  Index Scan using test_aa on test  (cost=0.00..784.85 rows=1068 width=17) (actual time=0.156..0.161 rows=10 loops=1)
                           Index Cond: ((a >= '12'::text) AND (a < '13'::text))
               ->  Limit  (cost=0.00..6.96 rows=10 width=17) (actual time=0.122..0.133 rows=10 loops=1)
                     ->  Index Scan using test_ba on test  (cost=0.00..2614.31 rows=3758 width=17) (actual time=0.122..0.131 rows=10 loops=1)
                           Index Cond: ((b >= '12'::text) AND (b < '13'::text))
               ->  Limit  (cost=0.00..11.03 rows=10 width=17) (actual time=0.066..0.072 rows=10 loops=1)
                     ->  Index Scan using test_ca on test  (cost=0.00..1671.03 rows=1515 width=17) (actual time=0.066..0.070 rows=10 loops=1)
                           Index Cond: ((c >= '12'::text) AND (c < '13'::text))
 Total runtime: 0.527 ms

That's much, much faster, and it has some very useful properties.

Since the sorts are removed and an index-scan is used instead (because of the LIMIT 10) the query is extremely fast in all cases. It just doesn't return all the results.

If the user searches for names like 'a%' and gets 5000 results, this result is useless to the user, instead he'll just make a more precise query by adding a letter. So, the database resources expended to compute and return the 5000 results were spent uselessly. It is much better to make a quick, bounded time query like this one and display 'more results...' so the user knows he should enter a few more letters in the search box.

Improve this answer

Comments

0

Try to avoid OR statements in SQL. The select would be:

SELECT * FROM ((SELECT * FROM employees WHERE first_name LIKE 's%') UNION (SELECT * FROM employees WHERE middle_name LIKE 's%') UNION (SELECT * FROM employees WHERE last_name LIKE 's%')) AS foo ORDER BY first_name;
Improve this answer

3 Comments

Are these actually executed differently? I assumed this is what postgres would do. But for readability an OR is much clearer.
OR takes much, much longer to execute as far as I know. I've read it in PostgreSQL documentation. I think it applies to any RDBMS.
OR will probably make the planner use a single seq scan. Whether this is faster or not depends on the elements of your union being significantly faster than seq scans. With indices, they should be.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.