pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmatching a timestamp field
4+ messages / 3 participants
[nested] [flat]
* matching a timestamp field
@ 2012-09-20 11:01 BACHELART PIERRE (CIS/SCC) <pierre.bachelart@belgacom.be>
2012-09-20 12:21 ` Re: matching a timestamp field BACHELART PIERRE (CIS/SCC) <pierre.bachelart@belgacom.be>
2012-09-22 08:08 ` Re: matching a timestamp field Andreas Kretschmer <akretschmer@spamfence.net>
2012-09-22 08:16 ` Re: matching a timestamp field Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 3 replies; 4+ messages in thread
From: BACHELART PIERRE (CIS/SCC) @ 2012-09-20 11:01 UTC (permalink / raw)
To: pgsql-sql
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=# 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: matching a timestamp field
2012-09-20 11:01 matching a timestamp field BACHELART PIERRE (CIS/SCC) <pierre.bachelart@belgacom.be>
@ 2012-09-20 12:21 ` BACHELART PIERRE (CIS/SCC) <pierre.bachelart@belgacom.be>
2 siblings, 0 replies; 4+ messages in thread
From: BACHELART PIERRE (CIS/SCC) @ 2012-09-20 12:21 UTC (permalink / raw)
To: BACHELART PIERRE (CIS/SCC) <pierre.bachelart@belgacom.be>; pgsql-sql
Hello,
The solution I just found on the Net (Thanks to Samuel Gendler)
ansroc=# 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=# 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: matching a timestamp field
2012-09-20 11:01 matching a timestamp field BACHELART PIERRE (CIS/SCC) <pierre.bachelart@belgacom.be>
@ 2012-09-22 08:08 ` Andreas Kretschmer <akretschmer@spamfence.net>
2 siblings, 0 replies; 4+ messages in thread
From: Andreas Kretschmer @ 2012-09-22 08:08 UTC (permalink / raw)
To: pgsql-sql
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°
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: matching a timestamp field
2012-09-20 11:01 matching a timestamp field BACHELART PIERRE (CIS/SCC) <pierre.bachelart@belgacom.be>
@ 2012-09-22 08:16 ` Pavel Stehule <pavel.stehule@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Pavel Stehule @ 2012-09-22 08:16 UTC (permalink / raw)
To: BACHELART PIERRE (CIS/SCC) <pierre.bachelart@belgacom.be>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-09-22 08:16 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-09-20 11:01 matching a timestamp field BACHELART PIERRE (CIS/SCC) <pierre.bachelart@belgacom.be>
2012-09-20 12:21 ` BACHELART PIERRE (CIS/SCC) <pierre.bachelart@belgacom.be>
2012-09-22 08:08 ` Andreas Kretschmer <akretschmer@spamfence.net>
2012-09-22 08:16 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox