Thread: matching a timestamp field

<div class="WordSection1"><p class="MsoNormal">Hello,<p class="MsoNormal"> <p class="MsoNormal"> <p
class="MsoNormal">Whyis my sql below accepted in 8.1.19 and refused in 8.4.9 ???<p class="MsoNormal">Is there something
Ihave missed in the doc ?<p class="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal">Welcome to psql 8.1.19, the
PostgreSQLinteractive terminal.<p class="MsoNormal">Type:  \copyright for distribution terms<p class="MsoNormal">      
\hfor help with SQL commands<p class="MsoNormal">       \? for help with psql commands<p class="MsoNormal">       \g or
terminatewith semicolon to execute query<p class="MsoNormal">       \q to quit<p class="MsoNormal"> <p
class="MsoNormal">ansroc=#select * from s12hwdb where record ~'2012-09-20' limit 5;<p class="MsoNormal">   host   |
exchange|   rit   |  board   | var  | lceid | pceid | mnem  | eq | rtyp | rv |  cetype  |       record        | type |
zone<p
class="MsoNormal">----------+----------+---------+----------+------+-------+-------+-------+----+------+----+----------+---------------------+------+------<p
class="MsoNormal">and5032t| and5032t | 01a0301 | 21122994 | ebjb | 0000  | 000c  | con3a | e  | ef03 | b1 | plce#xfx |
2012-09-2011:50:02 | H    | a1<p class="MsoNormal">and5032t | and5032t | 01a0307 | 21406298 | aaca | 0000  | 000c  |
mmca | e  | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H    | a1<p class="MsoNormal">and5032t | and5032t | 01a0309 |
21406298| aaca | 0000  | 000c  | mmca  | s  | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H    | a1<p
class="MsoNormal">and5032t| and5032t | 01a0311 | 21407930 | aaaa | 0000  | 000c  | mmcb  | e  | ef03 | b1 | plce#xfx |
2012-09-2011:50:02 | H    | a1<p class="MsoNormal">and5032t | and5032t | 01a0313 | 21407932 | abca | 0000  | 000c  |
mcud | e  | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H    | a1<p class="MsoNormal">(5 rows)<p class="MsoNormal"> <p
class="MsoNormal">ansroc=#\q<p class="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal">psql
(8.4.9)<pclass="MsoNormal">Type "help" for help.<p class="MsoNormal">ansroc=# select * from s12hwdb where record
~'2012-09-20'limit 5;<p class="MsoNormal">ERROR:  operator does not exist: timestamp without time zone ~ unknown<p
class="MsoNormal">LINE1: select * from s12hwdb where record ~'2012-09-20' limit 5;<p
class="MsoNormal">                                          ^<p class="MsoNormal">HINT:  No operator matches the given
nameand argument type(s). You might need to add explicit type casts.<p class="MsoNormal">ansroc=#<p
class="MsoNormal"> <pclass="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal"><span style=""> </span><p
class="MsoNormal"><spanstyle="">Pierre.</span><p class="MsoNormal"><span style="">+32 471 68 12 23</span><p
class="MsoNormal"> </div><br/><hr /><font color="Blue" face="Arial" size="2"><br /> ***** Disclaimer *****<br />
http://www.belgacom.be/maildisclaimer<br/></font> 

BACHELART PIERRE (CIS/SCC) <pierre.bachelart@belgacom.be> wrote:

> Hello,
> 
>  
> 
>  
> 
> Why is my sql below accepted in 8.1.19 and refused in 8.4.9 ???
> 
> Welcome to psql 8.1.19, the PostgreSQL interactive terminal.
> 
> ansroc=# select * from s12hwdb where record ~'2012-09-20' limit 5;
>  
> 
> psql (8.4.9)
> 
> 
> ERROR:  operator does not exist: timestamp without time zone ~ unknown
> 
> LINE 1: select * from s12hwdb where record ~'2012-09-20' limit 5;
> 

Because of the dropped implicid casts since IIRC 8.2.

You have to rewrite your query to:

select * from s12hwdb where record::date = '2012-09-20'::date limit 5;

(assuming record is a TIMESTAMP-Field)

Short example:

test=# select now() ~ '2012-09-22';
ERROR:  operator does not exist: timestamp with time zone ~ unknown
LINE 1: select now() ~ '2012-09-22';                    ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
Time: 0,156 ms
test=!# rollback;
ROLLBACK
Time: 0,079 ms
test=# select now()::date = '2012-09-22'::date;?column?
----------t
(1 row)


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Hello

2012/9/20 BACHELART PIERRE (CIS/SCC) <pierre.bachelart@belgacom.be>:
> Hello,
>
>
>
>
>
> Why is my sql below accepted in 8.1.19 and refused in 8.4.9 ???
>
> Is there something I have missed in the doc ?
>

you cannot use ~ operator for timestamp, it is nonsense - use '=' instead

see 8.3 release notes

http://www.postgresql.org/docs/9.1/static/release-8-3.html

A dump/restore using pg_dump is required for those wishing to migrate
data from any previous release.

Observe the following incompatibilities:
E.51.2.1. General
   Non-character data types are no longer automatically cast to TEXT
(Peter, Tom)

Regards

Pavel Stehule
>
>
>
>
> Welcome to psql 8.1.19, the PostgreSQL interactive terminal.
>
> Type:  \copyright for distribution terms
>
>        \h for help with SQL commands
>
>        \? for help with psql commands
>
>        \g or terminate with semicolon to execute query
>
>        \q to quit
>
>
>
> ansroc=# select * from s12hwdb where record ~'2012-09-20' limit 5;
>
>    host   | exchange |   rit   |  board   | var  | lceid | pceid | mnem  |
> eq | rtyp | rv |  cetype  |       record        | type | zone
>
>
----------+----------+---------+----------+------+-------+-------+-------+----+------+----+----------+---------------------+------+------
>
> and5032t | and5032t | 01a0301 | 21122994 | ebjb | 0000  | 000c  | con3a | e
> | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H    | a1
>
> and5032t | and5032t | 01a0307 | 21406298 | aaca | 0000  | 000c  | mmca  | e
> | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H    | a1
>
> and5032t | and5032t | 01a0309 | 21406298 | aaca | 0000  | 000c  | mmca  | s
> | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H    | a1
>
> and5032t | and5032t | 01a0311 | 21407930 | aaaa | 0000  | 000c  | mmcb  | e
> | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H    | a1
>
> and5032t | and5032t | 01a0313 | 21407932 | abca | 0000  | 000c  | mcud  | e
> | ef03 | b1 | plce#xfx | 2012-09-20 11:50:02 | H    | a1
>
> (5 rows)
>
>
>
> ansroc=# \q
>
>
>
>
>
>
>
> psql (8.4.9)
>
> Type "help" for help.
>
> ansroc=# select * from s12hwdb where record ~'2012-09-20' limit 5;
>
> ERROR:  operator does not exist: timestamp without time zone ~ unknown
>
> LINE 1: select * from s12hwdb where record ~'2012-09-20' limit 5;
>
>                                            ^
>
> HINT:  No operator matches the given name and argument type(s). You might
> need to add explicit type casts.
>
> ansroc=#
>
>
>
>
>
>
>
>
>
> Pierre.
>
> +32 471 68 12 23
>
>
>
>
> ________________________________
>
> ***** Disclaimer *****
> http://www.belgacom.be/maildisclaimer