Skip site navigation (1) Skip section navigation (2)

Re: matching a timestamp field

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: "BACHELART PIERRE (CIS/SCC)" <pierre(dot)bachelart(at)belgacom(dot)be>
Cc: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: matching a timestamp field
Date: 2012-09-22 08:16:46
Message-ID: CAFj8pRBERUNLvev4Ofm0yKzUQsiD5t1wuST_HU9oQG1FyOGrmQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
Hello

2012/9/20 BACHELART PIERRE (CIS/SCC) <pierre(dot)bachelart(at)belgacom(dot)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


In response to

pgsql-sql by date

Next:From: Wayne CuddyDate: 2012-09-22 17:34:31
Subject: transactions and ecpg
Previous:From: Andreas KretschmerDate: 2012-09-22 08:08:17
Subject: Re: matching a timestamp field

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group