Search Postgresql Archives

Re: valid use of wildcard

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Scott Marlowe wrote:
On Wed, Oct 29, 2008 at 5:04 PM, Irene Barg <ibarg@xxxxxxxx> wrote:
> Hi,
>
> Is the following query a valid use of the 'wildcard' in (='2008-10-27%')?
>
>> [arcsoft@dsan4 arcsoft]$ psql metadata
>> Password: Welcome to psql 8.1.9, the PostgreSQL interactive terminal.
>>
>> metadata=# SELECT * FROM viewspace.siap AS t WHERE
>> t."startDate"='2008-10-27%' AND t.prop_id LIKE '%' LIMIT 1000;
>
> Causes the %CPU to jump and process lingers for over an hour.

Bad move.  dates aren't strings, and their format can change based on
what you've got set for datestyle.

If you want a start date (that's a date or a timestamp) then use the
proper operators

where startDate='2008-10-27'

If startDate is a text / varchar type then you need to change it to a
date.  storing dates in strings is bad.

Surprisingly, '2008-10-27%' casts to a date in 8.3.3. I was expecting the planner to cast the field to string to compare it (or throw an error about implicit casting), but the literal goes to the field type (see explain on a timestamp field below). Does the % have any special meaning in casts to date/timestamp?

postgres=# select version();
                      version
-----------------------------------------------------
PostgreSQL 8.3.3, compiled by Visual C++ build 1400
(1 row)

postgres=# select '2008-10-27%'::date;
   date
------------
2008-10-27
(1 row)

postgres=# explain select * from data where "timestamp" = '2008-10-27%';
                                 QUERY PLAN
------------------------------------------------------------------------------
Seq Scan on data  (cost=0.00..504.68 rows=2 width=27)
Filter: ("timestamp" = '2008-10-27 00:00:00'::timestamp without time zone)
(2 rows)

postgres=#


klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789 Fax: 02 6773 3266
EMail: kgore4@xxxxxxxxxx


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux