From pierre.bachelart@belgacom.be Thu Sep 20 11:02:14 2012 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TEeWL-0002JM-RS for pgsql-sql@postgresql.org; Thu, 20 Sep 2012 11:02:14 +0000 Received: from mx23.belgacom.be ([213.181.45.233]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TEeWI-0002Dd-Ni for pgsql-sql@postgresql.org; Thu, 20 Sep 2012 11:02:12 +0000 X-IronPort-AV: E=Sophos;i="4.80,453,1344204000"; d="scan'208,217";a="25798203" Received: from unknown (HELO A03005.BGC.NET) ([10.120.129.161]) by mx23.belgacom.be with ESMTP; 20 Sep 2012 13:01:17 +0200 X-TM-IMSS-Message-ID: <03a141570000b6ad@belgacom.be> Received: from A04021.BGC.NET ([10.120.135.22]) by belgacom.be ([10.120.129.161]) with ESMTP (TREND IMSS SMTP Service 7.1; TLSv1/SSLv3 AES128-SHA (128/128)) id 03a141570000b6ad ; Thu, 20 Sep 2012 13:01:15 +0200 Received: from A04059.BGC.NET ([10.121.135.30]) by A04021.BGC.NET ([10.120.135.22]) with mapi id 14.01.0355.002; Thu, 20 Sep 2012 13:01:15 +0200 From: "BACHELART PIERRE (CIS/SCC)" To: "pgsql-sql@postgresql.org" Subject: matching a timestamp field Thread-Topic: matching a timestamp field Thread-Index: Ac2XHVl6Z/YUrpUBQZG3Rsr5m58Vjg== Date: Thu, 20 Sep 2012 11:01:15 +0000 Message-ID: <3C56CD7AD881324BB4C60AA5CE2C5CC11720019A@A04059.BGC.NET> Accept-Language: en-US, en-GB Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.111.9.140] Content-Type: multipart/alternative; boundary="_000_3C56CD7AD881324BB4C60AA5CE2C5CC11720019AA04059BGCNET_" MIME-Version: 1.0 X-Pg-Spam-Score: -2.4 (--) X-Archive-Number: 201209/47 X-Sequence-Number: 36849 --_000_3C56CD7AD881324BB4C60AA5CE2C5CC11720019AA04059BGCNET_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 ? 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=3D# 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=3D# \q psql (8.4.9) Type "help" for help. ansroc=3D# 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 n= eed to add explicit type casts. ansroc=3D# Pierre. +32 471 68 12 23 ________________________________ ***** Disclaimer ***** http://www.belgacom.be/maildisclaimer --_000_3C56CD7AD881324BB4C60AA5CE2C5CC11720019AA04059BGCNET_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hello,

 

 

Why is my sql below accepted in 8.1.19 and refused i= n 8.4.9 ???

Is there something I have missed in the doc ?

 

 

Welcome to psql 8.1.19, the PostgreSQL interactive t= erminal.

Type:  \copyright for distribution terms

       \h for help wit= h SQL commands

       \? for help wit= h psql commands

       \g or terminate= with semicolon to execute query

       \q to quit

 

ansroc=3D# select * from s12hwdb where record ~'2012= -09-20' limit 5;

   host   | exchange | &nbs= p; rit   |  board   | var  | lceid | pceid | = mnem  | eq | rtyp | rv |  cetype  |    &= nbsp;  record        | type | zone<= /p>

----------+----------+---------+--------= --+------+-------+-------+-------+----+------+-= ---+----------+---------------------+------+------

and5032t | and5032t | 01a0301 | 21122994 | ebjb | 00= 00  | 000c  | con3a | e  | ef03 | b1 | plce#xfx | 2012-09-20= 11:50:02 | H    | a1

and5032t | and5032t | 01a0307 | 21406298 | aaca | 00= 00  | 000c  | mmca  | e  | ef03 | b1 | plce#xfx | 2012-= 09-20 11:50:02 | H    | a1

and5032t | and5032t | 01a0309 | 21406298 | aaca | 00= 00  | 000c  | mmca  | s  | ef03 | b1 | plce#xfx | 2012-= 09-20 11:50:02 | H    | a1

and5032t | and5032t | 01a0311 | 21407930 | aaaa | 00= 00  | 000c  | mmcb  | e  | ef03 | b1 | plce#xfx | 2012-= 09-20 11:50:02 | H    | a1

and5032t | and5032t | 01a0313 | 21407932 | abca | 00= 00  | 000c  | mcud  | e  | ef03 | b1 | plce#xfx | 2012-= 09-20 11:50:02 | H    | a1

(5 rows)

 

ansroc=3D# \q

 

 

 

psql (8.4.9)

Type "help" for help.

ansroc=3D# select * from s12hwdb where record ~'2012= -09-20' limit 5;

ERROR:  operator does not exist: timestamp with= out time zone ~ unknown

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

        &nbs= p;            &= nbsp;           &nbs= p;         ^

HINT:  No operator matches the given name and a= rgument type(s). You might need to add explicit type casts.

ansroc=3D#

 

 

 

 

Pierre.

+32 471 68 12 23

 



***** Disclaimer *****
http://www.belgacom.be/maildisclaimer
 --_000_3C56CD7AD881324BB4C60AA5CE2C5CC11720019AA04059BGCNET_-- From pierre.bachelart@belgacom.be Thu Sep 20 12:22:04 2012 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TEflb-00066C-V2 for pgsql-sql@postgresql.org; Thu, 20 Sep 2012 12:22:04 +0000 Received: from mx24.belgacom.be ([213.181.45.234]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TEflY-0003Ss-Jf for pgsql-sql@postgresql.org; Thu, 20 Sep 2012 12:22:02 +0000 X-IronPort-AV: E=Sophos;i="4.80,453,1344204000"; d="scan'208,217";a="25618688" Received: from unknown (HELO A03005.BGC.NET) ([10.120.129.161]) by mx24.belgacom.be with ESMTP; 20 Sep 2012 14:21:58 +0200 X-TM-IMSS-Message-ID: <03eb20b80000d239@belgacom.be> Received: from A04027.BGC.NET ([10.121.135.24]) by belgacom.be ([10.120.129.161]) with ESMTP (TREND IMSS SMTP Service 7.1; TLSv1/SSLv3 AES128-SHA (128/128)) id 03eb20b80000d239 ; Thu, 20 Sep 2012 14:21:57 +0200 Received: from A04059.BGC.NET ([10.121.135.30]) by A04027.BGC.NET ([10.121.135.24]) with mapi id 14.01.0355.002; Thu, 20 Sep 2012 14:21:57 +0200 From: "BACHELART PIERRE (CIS/SCC)" To: "BACHELART PIERRE (CIS/SCC)" , "pgsql-sql@postgresql.org" Subject: Re: matching a timestamp field Thread-Topic: matching a timestamp field Thread-Index: Ac2XHVl6Z/YUrpUBQZG3Rsr5m58VjgADIEcA Date: Thu, 20 Sep 2012 12:21:56 +0000 Message-ID: <3C56CD7AD881324BB4C60AA5CE2C5CC1172001FC@A04059.BGC.NET> References: <3C56CD7AD881324BB4C60AA5CE2C5CC11720019A@A04059.BGC.NET> In-Reply-To: <3C56CD7AD881324BB4C60AA5CE2C5CC11720019A@A04059.BGC.NET> Accept-Language: en-US, en-GB Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.111.9.140] Content-Type: multipart/alternative; boundary="_000_3C56CD7AD881324BB4C60AA5CE2C5CC1172001FCA04059BGCNET_" MIME-Version: 1.0 X-Pg-Spam-Score: -2.4 (--) X-Archive-Number: 201209/48 X-Sequence-Number: 36850 --_000_3C56CD7AD881324BB4C60AA5CE2C5CC1172001FCA04059BGCNET_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hello, The solution I just found on the Net (Thanks to Samuel Gendler) ansroc=3D# select * from s12hwdb where record::text ~ '2012-09-20 11:50:02'= 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) But I still can not find this in the doc. From: BACHELART PIERRE (CIS/SCC) [mailto:pierre.bachelart@belgacom.be] Sent: Thursday 20 September 2012 13:01 To: pgsql-sql@postgresql.org Subject: matching a timestamp field 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 ? 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=3D# 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=3D# \q psql (8.4.9) Type "help" for help. ansroc=3D# 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 n= eed to add explicit type casts. ansroc=3D# Pierre. +32 471 68 12 23 ________________________________ ***** Disclaimer ***** http://www.belgacom.be/maildisclaimer --_000_3C56CD7AD881324BB4C60AA5CE2C5CC1172001FCA04059BGCNET_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hello,

 

The solution I just fo= und on the Net (Thanks to Samuel Gendler)

 

ansroc=3D# select * fr= om s12hwdb where record::text ~ '2012-09-20 11:50:02' limit 5;

   host = ;  | exchange |   rit   |  board   = | var  | lceid | pceid | mnem  | eq | rtyp | rv |  cetype&nb= sp; |       record    &nb= sp;   | type | zone

----------+-------= ---+---------+----------+------+-------+-------+---= ----+----+------+----+----------+---------------------&= #43;------+------

and5032t | and5032t | = 01a0301 | 21122994 | ebjb | 0000  | 000c  | con3a | e  | ef0= 3 | 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)

 

But I still can not fi= nd this in the doc.

 

 

From: BACHELART PIERRE (CIS/SCC) [mailto:pierre.bachelart@b= elgacom.be]
Sent: Thursday 20 September 2012 13:01
To: pgsql-sql@postgresql.org
Subject: matching a timestamp field

 

Hello,

 

 

Why is my sql below accepted in 8.1.19 and refused i= n 8.4.9 ???

Is there something I have missed in the doc ?

 

 

Welcome to psql 8.1.19, the PostgreSQL interactive t= erminal.

Type:  \copyright for distribution terms

       \h for help wit= h SQL commands

       \? for help wit= h psql commands

       \g or terminate= with semicolon to execute query

       \q to quit=

 

ansroc=3D# select * from s12hwdb where record ~'2012= -09-20' limit 5;

   host   | exchange | &nbs= p; rit   |  board   | var  | lceid | pceid | = mnem  | eq | rtyp | rv |  cetype  |    &= nbsp;  record        | type | zone<= o:p>

----------+----------+---------+--------= --+------+-------+-------+-------+----+------+-= ---+----------+---------------------+------+------

and5032t | and5032t | 01a0301 | 21122994 | ebjb | 00= 00  | 000c  | con3a | e  | ef03 | b1 | plce#xfx | 2012-09-20= 11:50:02 | H    | a1

and5032t | and5032t | 01a0307 | 21406298 | aaca | 00= 00  | 000c  | mmca  | e  | ef03 | b1 | plce#xfx | 2012-= 09-20 11:50:02 | H    | a1

and5032t | and5032t | 01a0309 | 21406298 | aaca | 00= 00  | 000c  | mmca  | s  | ef03 | b1 | plce#xfx | 2012-= 09-20 11:50:02 | H    | a1

and5032t | and5032t | 01a0311 | 21407930 | aaaa | 00= 00  | 000c  | mmcb  | e  | ef03 | b1 | plce#xfx | 2012-= 09-20 11:50:02 | H    | a1

and5032t | and5032t | 01a0313 | 21407932 | abca | 00= 00  | 000c  | mcud  | e  | ef03 | b1 | plce#xfx | 2012-= 09-20 11:50:02 | H    | a1

(5 rows)

 

ansroc=3D# \q

 

 

 

psql (8.4.9)

Type "help" for help.

ansroc=3D# select * from s12hwdb where record ~'2012= -09-20' limit 5;

ERROR:  operator does not exist: timestamp with= out time zone ~ unknown

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

        &nbs= p;            &= nbsp;           &nbs= p;         ^

HINT:  No operator matches the given name and a= rgument type(s). You might need to add explicit type casts.

ansroc=3D#

 

 

 

 

Pierre.

+32 471 68 12 23

 

 


***** Disclaimer *****
http://www.belgacom.be/ma= ildisclaimer

--_000_3C56CD7AD881324BB4C60AA5CE2C5CC1172001FCA04059BGCNET_-- From akretschmer@spamfence.net Sat Sep 22 08:08:23 2012 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TFKlD-0004PQ-NG for pgsql-sql@postgresql.org; Sat, 22 Sep 2012 08:08:23 +0000 Received: from mailout01.ims-firmen.de ([213.174.32.96]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TFKlB-0000sF-KO for pgsql-sql@postgresql.org; Sat, 22 Sep 2012 08:08:23 +0000 Received: from mailin01.ims-firmen.de ([192.168.1.141]) by mailout01.ims-firmen.de with esmtp (envelope-from ) id 1TFKl9-0003MR-ik for pgsql-sql@postgresql.org; Sat, 22 Sep 2012 10:08:19 +0200 Received: from [213.174.32.254] (helo=a-kretschmer.de) by mailin01.ims-firmen.de with esmtpsa (TLSv1:AES256-SHA:256) (envelope-from ) id 1TFKl9-0002LQ-1w for pgsql-sql@postgresql.org; Sat, 22 Sep 2012 10:08:19 +0200 Received: from kretschmer by a-kretschmer.de with local (Exim 4.69) (envelope-from ) id 1TFKl7-0000t7-8j for pgsql-sql@postgresql.org; Sat, 22 Sep 2012 10:08:17 +0200 Date: Sat, 22 Sep 2012 10:08:17 +0200 From: Andreas Kretschmer To: pgsql-sql@postgresql.org Subject: Re: matching a timestamp field Message-ID: <20120922080817.GA2943@tux> References: <3C56CD7AD881324BB4C60AA5CE2C5CC11720019A@A04059.BGC.NET> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <3C56CD7AD881324BB4C60AA5CE2C5CC11720019A@A04059.BGC.NET> X-OS: Debian/GNU Linux - weil ich es mir Wert bin! X-GPG-Fingerprint: EE16 3C01 7B9C 10F7 2C8B 3B86 4DB3 D9EE 7F45 84DA X-Message-Flag: "Windows" is not the answer. "Windows" is the question and the answer is "no"! X-Lugdd: Gerd Kube X-Info: My name is root. Just root. And I am licensed to kill -9 User-Agent: Mutt/1.5.18 (2008-05-17) X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201209/49 X-Sequence-Number: 36851 BACHELART PIERRE (CIS/SCC) 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° From pavel.stehule@gmail.com Sat Sep 22 08:17:33 2012 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TFKu5-000880-34 for pgsql-sql@postgresql.org; Sat, 22 Sep 2012 08:17:33 +0000 Received: from mail-ey0-f174.google.com ([209.85.215.174]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TFKu0-00011B-0d for pgsql-sql@postgresql.org; Sat, 22 Sep 2012 08:17:32 +0000 Received: by eaac11 with SMTP id c11so1304216eaa.19 for ; Sat, 22 Sep 2012 01:17:26 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; bh=sxse/RQn8jAuQeDzED1nYwriCyF2aJTdB9jHME/NhjQ=; b=V2iC+VbCZwYn19SV4wmkiRZahi2LADmx//j+k324s97j9Hb0bc0jqb2GFAwFjUqPTx HJoa2l98IxBEk+gCkyA1RrFRX2+UoBEnZ3Z050qdAMwROgPsMp8UkUKGRaYUGsanLCmD TjAjlHmcJqyxPvgbk7RM8+EOrYjZ4uHf2QPfWuuULbUm70puDrQ19KT7TPintJCOWc2f bGsXJYh5UVvL7ENRNzU+qjq6tvWJr+bOoo84vGwWi9C6+qa0ICXGQXvMoUL6AQ+OykaS PFq4XkiJbbEFCsydOyi4HKbH767czjjDLhLQrWXpUj9szcDztSgqdK6suxyInHT5427i rcbQ== Received: by 10.14.179.137 with SMTP id h9mr8927441eem.22.1348301846482; Sat, 22 Sep 2012 01:17:26 -0700 (PDT) MIME-Version: 1.0 Received: by 10.14.1.7 with HTTP; Sat, 22 Sep 2012 01:16:46 -0700 (PDT) In-Reply-To: <3C56CD7AD881324BB4C60AA5CE2C5CC11720019A@A04059.BGC.NET> References: <3C56CD7AD881324BB4C60AA5CE2C5CC11720019A@A04059.BGC.NET> From: Pavel Stehule Date: Sat, 22 Sep 2012 10:16:46 +0200 Message-ID: Subject: Re: matching a timestamp field To: "BACHELART PIERRE (CIS/SCC)" Cc: "pgsql-sql@postgresql.org" Content-Type: text/plain; charset=UTF-8 X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201209/51 X-Sequence-Number: 36853 Hello 2012/9/20 BACHELART PIERRE (CIS/SCC) : > 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