to_char in where clause SQL / Postgresql

Question

I'm using Postgresql DB.

I want to perform a query against a numeric column in a table to find if the value starts with a particular number.

I currently have:

SELECT * FROM myTable WHERE to_char(ID, '12345678') LIKE '2%'

However this returns an empty dataset (but there are values in the ID column which start with 2.

Many thanks

Steve

the second argument for to_char is intended as a template. Only 0 and 9 are applicable to numbers. You could also use a plain cast: WHERE id::text LIKE '2%' — wildplasser
– wildplasser, Commented Sep 16, 2012 at 16:19

João Silva · Accepted Answer · 2012-09-16 16:19:36Z

4

That's because the pattern for a number in TO_CHAR is either 9 (value with the specified number of digits) or 0 (value with leading zeros). Either use the following:

SELECT * FROM myTable WHERE TRIM(to_char(ID, '99999999')) LIKE '2%'

Or cast the number to a char.

answered Sep 16, 2012 at 16:19

João Silva

91.8k29 gold badges156 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pleymor Over a year ago

Thanks! It help me to figure out why my where to_char(usedate, 'MONTH') = 'JANUARY' did not work. to_char leaves whitespaces after JANUARY. => fix: where TRIM(to_char(usedate, 'MONTH')) = 'JANUARY'

wildplasser · Accepted Answer · 2012-09-16 18:09:55Z

Don't laugh. As an alternative to the casts, you could use a range query, like:

SELECT * FROM mytable
WHERE id = 2
OR (id >=20 AND id < 30)
OR (id >=200 AND id < 300)
OR (id >=2000 AND id < 3000)
OR (id >=20000 AND id < 30000)
OR (id >=200000 AND id < 300000)
OR (id >=2000000 AND id < 3000000)
OR (id >=20000000 AND id < 30000000)
OR (id >=200000000 AND id < 300000000)
OR (id >=2000000000 AND id < 3000000000)
OR (id >=20000000000 AND id < 30000000000)
OR (id >=200000000000 AND id < 300000000000)
        -- ...
        ;

This could cause postgres to generate a much better query plan, because an index can be used (if present, which can be expected for an id field)

UPDATE: The query plans:

Bitmap Heap Scan on reservations  (cost=1838.15..13290.11 rows=110809 width=4012) (actual time=11.310..24.379 rows=111111 loops=1)
   Recheck Cond: ((id = 2) OR ((id >= 20) AND (id < 30)) OR ((id >= 200) AND (id < 300)) OR ((id >= 2000) AND (id < 3000)) OR ((id >= 20000) AND (id < 30000)) OR ((id >= 200000) AND (id < 300000)) OR ((id >= 2000000) AND (id < 3000000)) OR ((id >= 20000000) AND (id < 30000000)) OR ((id >= 200000000) AND (id < 300000000)) OR ((id >= 2000000000) AND (id < 3000000000::bigint)) OR ((id >= 20000000000::bigint) AND (id < 30000000000::bigint)) OR ((id >= 200000000000::bigint) AND (id < 300000000000::bigint)))
   ->  BitmapOr  (cost=1838.15..1838.15 rows=112192 width=0) (actual time=11.242..11.242 rows=0 loops=1)
         ->  Bitmap Index Scan on reservations_pkey  (cost=0.00..1.49 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1)
               Index Cond: (id = 2)
         ->  Bitmap Index Scan on reservations_pkey  (cost=0.00..1.58 rows=10 width=0) (actual time=0.003..0.003 rows=10 loops=1)
               Index Cond: ((id >= 20) AND (id < 30))
         ->  Bitmap Index Scan on reservations_pkey  (cost=0.00..2.52 rows=104 width=0) (actual time=0.013..0.013 rows=100 loops=1)
               Index Cond: ((id >= 200) AND (id < 300))
         ->  Bitmap Index Scan on reservations_pkey  (cost=0.00..14.24 rows=1036 width=0) (actual time=0.110..0.110 rows=1000 loops=1)
               Index Cond: ((id >= 2000) AND (id < 3000))
         ->  Bitmap Index Scan on reservations_pkey  (cost=0.00..144.73 rows=10845 width=0) (actual time=1.050..1.050 rows=10000 loops=1)
               Index Cond: ((id >= 20000) AND (id < 30000))
         ->  Bitmap Index Scan on reservations_pkey  (cost=0.00..1332.24 rows=100196 width=0) (actual time=10.013..10.013 rows=100000 loops=1)
               Index Cond: ((id >= 200000) AND (id < 300000))
         ->  Bitmap Index Scan on reservations_pkey  (cost=0.00..1.49 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=1)
               Index Cond: ((id >= 2000000) AND (id < 3000000))
         ->  Bitmap Index Scan on reservations_pkey  (cost=0.00..1.49 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
               Index Cond: ((id >= 20000000) AND (id < 30000000))
         ->  Bitmap Index Scan on reservations_pkey  (cost=0.00..1.49 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
               Index Cond: ((id >= 200000000) AND (id < 300000000))
         ->  Bitmap Index Scan on reservations_pkey  (cost=0.00..1.49 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
               Index Cond: ((id >= 2000000000) AND (id < 3000000000::bigint))
         ->  Bitmap Index Scan on reservations_pkey  (cost=0.00..1.49 rows=1 width=0) (actual time=0.026..0.026 rows=0 loops=1)
               Index Cond: ((id >= 20000000000::bigint) AND (id < 30000000000::bigint))
         ->  Bitmap Index Scan on reservations_pkey  (cost=0.00..1.49 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
               Index Cond: ((id >= 200000000000::bigint) AND (id < 300000000000::bigint))
 Total runtime: 28.720 ms
(28 rows)


 Seq Scan on reservations  (cost=0.00..19219.52 rows=4383 width=4012) (actual time=0.025..184.532 rows=111111 loops=1)
   Filter: ((id)::text ~~ '2%'::text)
 Total runtime: 189.100 ms
(3 rows)

This is particularly valuable if you think that all of your id values are in a single range -- the LIKE approach is a sure loser in that case.
wildplasser, I must say this is a very good answer, thank you. Logically speaking I have no idea why I didn't figure this out myself, but I guess x heads are better than 1. :) I just wish I could +2 - one for the answer, one for the query plans.

segasai · Accepted Answer · 2012-09-16 16:52:34Z

2

This will return the leading digit:

floor(a/10.^(floor(log(a))))

Example:

select a from generate_series(1,1000) as a where 
    floor(a/10.^(floor(log(a)))) = 2;

If you have numbers which are smaller than one or less than zero you should filter them out (depending on what you want exactly).

answered Sep 16, 2012 at 16:52

segasai

8,5581 gold badge31 silver badges40 bronze badges

1 Comment

Craig Ringer Over a year ago

Handily, this expression is suitable for a functional index, so if the query runs often enough or is slow enough for an index to be worth the costs, you can add one.

Steve · Accepted Answer · 2012-09-16 16:19:29Z

0

I found the answer:

SELECT * FROM myTable WHERE CAST (ID AS CHAR) LIKE '2%'

Steve

answered Sep 16, 2012 at 16:19

Steve

3504 silver badges18 bronze badges

2 Comments

kgrittn Over a year ago

Be careful; this will work, but probably not for exactly the reason you think it will. CHAR without a length factor will always be a single character -- prove it to yourself by running SELECT CAST(23456 as CHAR); or similar. The statement in your answer is semantically exactly the same as SELECT * FROM myTable WHERE CAST (ID AS CHAR) = '2', so if you tried looking for a two-character prefix, you would get an unpleasant surprise. A cast to text would probably be less surprising.

Chris Travers Over a year ago

Second the cast to text instead. You can also say text(id) instead of cast(id as text). Also if you do this you will want to index output of text(id) so that you can use an index.

kgrittn · Accepted Answer · 2012-12-19 22:58:25Z

-1

You dont need the to_char, just id like '2%'.

edited Dec 19, 2012 at 22:58

kgrittn

19.8k4 gold badges41 silver badges47 bronze badges

answered Sep 16, 2012 at 16:15

user1675905

791 silver badge2 bronze badges

2 Comments

Steve Over a year ago

er, no. Error code 0, SQL state 42883: ERROR: operator does not exist: numeric ~~ unknown Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 42

Craig Ringer Over a year ago

@user1675905 Might be worth deleting that answer.

Collectives™ on Stack Overflow

to_char in where clause SQL / Postgresql

5 Answers 5

1 Comment

2 Comments

1 Comment

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

2 Comments

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related