Thread: operator does not exist: timestamp w/out timezone (similar to bug 3807)

-- Easy to duplicate on 3 different operating systems, by executing the
-- following script via psql -f pg83bug.sql

-- begin script pg83bug.sql

\set VERBOSITY verbose;

drop table if exists bugtab;

select version();

create table bugtab (
  date      timestamp without time zone not null
 ,pnum      numeric
);

update bugtab set
    pnum = -8.6
    where date like '2007-01-19%';

-- end script pg83bug.sql

The error message reported on 3 different operating systems:

DROP TABLE

version
--------------------------------------------------------------------------------------------------------
 PostgreSQL 8.3.0 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC)
4.1.2 20070925 (Red Hat 4.1.2-33)
(1 row)

CREATE TABLE
psql:pg83bug.sql:16: ERROR:  operator does not exist: timestamp without
time zone ~~ unknown
LINE 3:     where date like '2007-01-19%';
                       ^
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.


DROP TABLE

version
-----------------------------------------------------------------------------------------------
 PostgreSQL 8.3.1 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3
(Ubuntu 4.2.3-2ubuntu4)
(1 row)

CREATE TABLE
psql:pg83bug.sql:16: ERROR:  operator does not exist: timestamp without
time zone ~~ unknown
LINE 3:     where date like '2007-01-19%';
                       ^
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.


DROP TABLE
                       version
-----------------------------------------------------
 PostgreSQL 8.3.0, compiled by Visual C++ build 1400
(1 row)

CREATE TABLE
psql:pg83bug.sql:16: ERROR:  operator does not exist: timestamp without
time zone ~~ unknown at character 50
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.
psql:pg83bug.sql:16: LINE 3:     where date like '2007-01-19%';
psql:pg83bug.sql:16:

On Sat, Apr 12, 2008 at 4:53 AM, philwalk <nomaps@frii.com> wrote:
>  CREATE TABLE
>  psql:pg83bug.sql:16: ERROR:  operator does not exist: timestamp without
>  time zone ~~ unknown
>  LINE 3:     where date like '2007-01-19%';
>                        ^
>  HINT:  No operator matches the given name and argument type(s). You
>  might need to add explicit type casts.

This isn't a bug. 8.3 removes a bunch of implicit casts to text which
led to unappropriate behaviours. Prior to 8.3, your timestamp was
casted to text implicitely.

Just use date_trunc
(http://www.postgresql.org/docs/current/static/functions-datetime.html):
update bugtab set
   pnum = -8.6
   where date_trunc('day', date) = '2007-01-19';

You can add a functional index on date_trunc('day', date) if necessary.

--
Guillaume