Re: varchar does not work too well with IS NOT NULL partial indexes. - Mailing list pgsql-general

"Dawid Kuroczko" <qnex42@gmail.com> writes:

> ALTER TABLE foo ALTER COLUMN i TYPE text;
> EXPLAIN SELECT * FROM foo WHERE i=17;
>                                 QUERY PLAN
> -----------------------------------------------------------------------------
> Bitmap Heap Scan on foo  (cost=12.14..554.42 rows=500 width=32)
>   Recheck Cond: (i = '17'::text)
>   ->  Bitmap Index Scan on foo_i_index  (cost=0.00..12.01 rows=498 width=0)
>         Index Cond: (i = '17'::text)

I think you've lost some single-quotes around 17 in this query. With the
single-quotes it works like this which seems like the correct result. You
don't need the casts in the index definition if you write the query with
single-quotes.

> EXPLAIN SELECT * FROM foo WHERE i=17;
>                       QUERY PLAN
> ---------------------------------------------------------
> Seq Scan on foo  (cost=0.00..1772.00 rows=500 width=34)
>   Filter: ((i)::text = '17'::text)

This is now an error:

LINE 1: EXPLAIN SELECT * FROM foo WHERE i=17;
                                         ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.


In fact it's not clear what you would want to happen here. Should it cast the
text to an integer and use integer comparison or cast the integer to text and
use text comparison? They don't necessarily generate the same results. (In
fact I suspect they would for equals but consider the same situation for < or
>)

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com