pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feeddisplay to_timestamp in quotas or convert to char ?
11+ messages / 4 participants
[nested] [flat]
* display to_timestamp in quotas or convert to char ?
@ 2015-05-05 09:59 czezz <czezz@o2.pl>
2015-05-05 11:46 ` Re: display to_timestamp in quotas or convert to char ? Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 1 reply; 11+ messages in thread
From: czezz @ 2015-05-05 09:59 UTC (permalink / raw)
To: pgsql-admin
Hello,
This post might be unreadable in your mail client. If so try to copy it in to notepad as there is a long query statement.
I have following table in the DB.
Server2=# \d ss21_file
Table "srx.ss21_file"
Column | Type | Modifiers
-------------------+------------------------+-----------
value | character varying(30) |
fileindex | bigint |
filename | character varying(80) |
filetime | character varying(100) |
filesizeinrecords | bigint |
filesizeinbytes | bigint |
sourceid | character varying(300) |
Indexes:
"ss21_filetime" UNIQUE, btree (filetime)
When executing following query I get following error:
Server2=# SELECT value, fileIndex, fileName, fileTime, fileSizeInRecords, fileSizeInBytes, sourceID FROM ss21_file WHERE filetime < to_timestamp('2015-05-04 22:13:14.000', 'YYYY/MM/DD-HH24:MI:SS.FF3');
ERROR: operator does not exist: character varying < timestamp with time zone
LINE 1: ...ytes, sourceID FROM ss21_file WHERE filetime < to_times...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
My assumption is that, it is because "filetime" column is "character varying" and in WHERE condition value there is converted to timestamp.
When I execute this same query but instead to_timestamp I use regular string eg. '2015-05-04 22:13:14.000' (NOTE: single quotas) it works quite good.
If this is correct, is there any way to force to_"timestamp" function to be convertrd in to "character varying" or display its value in single quotas ?
Server2=# SELECT value, fileIndex, fileName, fileTime, fileSizeInRecords, fileSizeInBytes, sourceID FROM ss21_file WHERE filetime < '2015-05-04 22:13:14.000';
value | fileindex | filename | filetime | filesizeinrecords | filesizeinbytes | sourceid
----------------------+-----------+---------------------------------------+-------------------------+-------------------+-----------------+-----------------------------
-------
-903097975225368365 | 94859 | ss21_20141118020501_1166.DAT | 2014-11-18 02:05:30.109 | 2775 | 490560 | coll_SS21-Group_0_141627273
0_1634
-2781431802746610881 | 94868 | ss21_20141118042502_1175.DAT | 2014-11-18 04:25:30.154 | 1803 | 310688 | coll_SS21-Group_0_141628113
0_1643
-4731211533677294393 | 94878 | ss21_20141118065501_1185.DAT | 2014-11-18 06:55:30.380 | 14745 | 2640848 | coll_SS21-Group_0_141629013
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: display to_timestamp in quotas or convert to char ?
2015-05-05 09:59 display to_timestamp in quotas or convert to char ? czezz <czezz@o2.pl>
@ 2015-05-05 11:46 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2015-05-05 13:21 ` Re: display to_timestamp in quotas or convert to char ? Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-05 13:24 ` RE: display to_timestamp in quotas or convert to char ? czezz <czezz@o2.pl>
0 siblings, 2 replies; 11+ messages in thread
From: Albe Laurenz @ 2015-05-05 11:46 UTC (permalink / raw)
To: 'czezz *EXTERN*' <czezz@o2.pl>; pgsql-admin
czezz wrote:
> I have following table in the DB.
>
> Server2=# \d ss21_file
> Table "srx.ss21_file"
> Column | Type | Modifiers
> -------------------+------------------------+-----------
> value | character varying(30) |
> fileindex | bigint |
> filename | character varying(80) |
> filetime | character varying(100) |
> filesizeinrecords | bigint |
> filesizeinbytes | bigint |
> sourceid | character varying(300) |
> Indexes:
> "ss21_filetime" UNIQUE, btree (filetime)
>
> When executing following query I get following error:
>
> Server2=# SELECT value, fileIndex, fileName, fileTime, fileSizeInRecords, fileSizeInBytes, sourceID
> FROM ss21_file WHERE filetime < to_timestamp('2015-05-04 22:13:14.000', 'YYYY/MM/DD-HH24:MI:SS.FF3');
> ERROR: operator does not exist: character varying < timestamp with time zone
> LINE 1: ...ytes, sourceID FROM ss21_file WHERE filetime < to_times...
> ^
> HINT: No operator matches the given name and argument type(s). You might need to add explicit type
> casts.
>
> My assumption is that, it is because "filetime" column is "character varying" and in WHERE condition
> value there is converted to timestamp.
Right. SQL is a typed language, so every expression has a data type.
"filetime" is "character varying", and the result of "to_timestamp" is
"timestamp with time zone":
test=> \df to_timestamp
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+--------------+--------------------------+---------------------+--------
pg_catalog | to_timestamp | timestamp with time zone | double precision | normal
pg_catalog | to_timestamp | timestamp with time zone | text, text | normal
PostgreSQL cannot find an operator "<" with these argument types, even considering
implicit casts (see http://www.postgresql.org/docs/current/static/typeconv-func.html).
> When I execute this same query but instead to_timestamp I use regular string eg. '2015-05-04
> 22:13:14.000' (NOTE: single quotas) it works quite good.
That is because there is a comparison operator "text" < "text".
> If this is correct, is there any way to force to_"timestamp" function to be convertrd in to "character
> varying" or display its value in single quotas ?
You could explicitly case the result of "to_timestamp" to "text", like this:
... WHERE filetime < to_char(to_timestamp(...), '...')
But there are other solutions:
1) Define the "filetime" column as type "timestamp with time zone".
This is normally the best solution.
2) Explicitly cast "filetime" to "timestamp with time zone", like this:
... WHERE to_timestamp(filetime, ...) < to_timestamp(...)
This has the advantage of checking "filetime" for correctness.
Yours,
Laurenz Albe
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: display to_timestamp in quotas or convert to char ?
2015-05-05 09:59 display to_timestamp in quotas or convert to char ? czezz <czezz@o2.pl>
2015-05-05 11:46 ` Re: display to_timestamp in quotas or convert to char ? Albe Laurenz <laurenz.albe@wien.gv.at>
@ 2015-05-05 13:21 ` Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 0 replies; 11+ messages in thread
From: Scott Ribe @ 2015-05-05 13:21 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: czezz *EXTERN* <czezz@o2.pl>; pgsql-admin
On May 5, 2015, at 5:46 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
>
> This has the advantage of checking "filetime" for correctness.
And comparing correctly across different time zones, unlike converting them both to text…
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* RE: display to_timestamp in quotas or convert to char ?
2015-05-05 09:59 display to_timestamp in quotas or convert to char ? czezz <czezz@o2.pl>
2015-05-05 11:46 ` Re: display to_timestamp in quotas or convert to char ? Albe Laurenz <laurenz.albe@wien.gv.at>
@ 2015-05-05 13:24 ` czezz <czezz@o2.pl>
2015-05-05 13:33 ` Re: display to_timestamp in quotas or convert to char ? Igor Neyman <ineyman@perceptron.com>
2015-05-05 13:56 ` Re: display to_timestamp in quotas or convert to char ? Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 2 replies; 11+ messages in thread
From: czezz @ 2015-05-05 13:24 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: pgsql-admin
Hi, thank you for reply.
I have already tried to convert timestamp to char like you suggested but that lead to another error.
First, simply execution with NO conversion - to make sure it works like that:
Server2=# select to_timestamp('2015-05-04 22:13:14.000', 'YYYY/MM/DD-HH24:MI:SS.FF3');
to_timestamp
------------------------
2015-05-04 22:13:14+02
(1 row)
Secondly, to_char():
Server2=# select to_char(to_timestamp('2015-05-04 22:13:14.000', 'YYYY/MM/DD-HH24:MI:SS.FF3'));
ERROR: function to_char(timestamp with time zone) does not exist
LINE 1: select to_char(to_timestamp('2015-05-04 22:13:14.000', 'YYYY...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
And this HINT seems to be odd.
BR,
czezz
Dnia 5 maja 2015 13:46 Albe Laurenz <laurenz.albe@wien.gv.at> napisał(a):
> czezz wrote:
> > I have following table in the DB.
> >
> > Server2=# \d ss21_file
> > Table "srx.ss21_file"
> > Column | Type | Modifiers
> > -------------------+------------------------+-----------
> > value | character varying(30) |
> > fileindex | bigint |
> > filename | character varying(80) |
> > filetime | character varying(100) |
> > filesizeinrecords | bigint |
> > filesizeinbytes | bigint |
> > sourceid | character varying(300) |
> > Indexes:
> > "ss21_filetime" UNIQUE, btree (filetime)
> >
> > When executing following query I get following error:
> >
> > Server2=# SELECT value, fileIndex, fileName, fileTime, fileSizeInRecords, fileSizeInBytes, sourceID
> > FROM ss21_file WHERE filetime < to_timestamp('2015-05-04 22:13:14.000', 'YYYY/MM/DD-HH24:MI:SS.FF3');
> > ERROR: operator does not exist: character varying < timestamp with time zone
> > LINE 1: ...ytes, sourceID FROM ss21_file WHERE filetime < to_times...
> > ^
> > HINT: No operator matches the given name and argument type(s). You might need to add explicit type
> > casts.
> >
> > My assumption is that, it is because "filetime" column is "character varying" and in WHERE condition
> > value there is converted to timestamp.
>
> Right. SQL is a typed language, so every expression has a data type.
>
> "filetime" is "character varying", and the result of "to_timestamp" is
> "timestamp with time zone":
>
> test=> \df to_timestamp
> List of functions
> Schema | Name | Result data type | Argument data types | Type
> ------------+--------------+--------------------------+---------------------+--------
> pg_catalog | to_timestamp | timestamp with time zone | double precision | normal
> pg_catalog | to_timestamp | timestamp with time zone | text, text | normal
>
> PostgreSQL cannot find an operator "<" with these argument types, even considering
> implicit casts (see http://www.postgresql.org/docs/current/static/typeconv-func.html).
>
> > When I execute this same query but instead to_timestamp I use regular string eg. '2015-05-04
> > 22:13:14.000' (NOTE: single quotas) it works quite good.
>
> That is because there is a comparison operator "text" < "text".
>
> > If this is correct, is there any way to force to_"timestamp" function to be convertrd in to "character
> > varying" or display its value in single quotas ?
>
> You could explicitly case the result of "to_timestamp" to "text", like this:
> ... WHERE filetime < to_char(to_timestamp(...), '...')
>
>
> But there are other solutions:
>
> 1) Define the "filetime" column as type "timestamp with time zone".
> This is normally the best solution.
>
> 2) Explicitly cast "filetime" to "timestamp with time zone", like this:
> ... WHERE to_timestamp(filetime, ...) < to_timestamp(...)
> This has the advantage of checking "filetime" for correctness.
>
> Yours,
> Laurenz Albe
>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: display to_timestamp in quotas or convert to char ?
2015-05-05 09:59 display to_timestamp in quotas or convert to char ? czezz <czezz@o2.pl>
2015-05-05 11:46 ` Re: display to_timestamp in quotas or convert to char ? Albe Laurenz <laurenz.albe@wien.gv.at>
2015-05-05 13:24 ` RE: display to_timestamp in quotas or convert to char ? czezz <czezz@o2.pl>
@ 2015-05-05 13:33 ` Igor Neyman <ineyman@perceptron.com>
1 sibling, 0 replies; 11+ messages in thread
From: Igor Neyman @ 2015-05-05 13:33 UTC (permalink / raw)
To: czezz <czezz@o2.pl>; Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: pgsql-admin
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of czezz
Sent: Tuesday, May 05, 2015 9:24 AM
To: Albe Laurenz
Cc: pgsql-admin@postgresql.org
Subject: RE: [ADMIN]display to_timestamp in quotas or convert to char ?
Hi, thank you for reply.
I have already tried to convert timestamp to char like you suggested but that lead to another error.
First, simply execution with NO conversion - to make sure it works like that:
Server2=# select to_timestamp('2015-05-04 22:13:14.000', 'YYYY/MM/DD-HH24:MI:SS.FF3');
to_timestamp
------------------------
2015-05-04 22:13:14+02
(1 row)
Secondly, to_char():
Server2=# select to_char(to_timestamp('2015-05-04 22:13:14.000', 'YYYY/MM/DD-HH24:MI:SS.FF3'));
ERROR: function to_char(timestamp with time zone) does not exist LINE 1: select to_char(to_timestamp('2015-05-04 22:13:14.000', 'YYYY...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
And this HINT seems to be odd.
BR,
czezz
There is nothing odd about HINT.
to_char(timestamp, text) requires second parameter: format, which you didn't provide.
Regards,
Igor Neyman
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: display to_timestamp in quotas or convert to char ?
2015-05-05 09:59 display to_timestamp in quotas or convert to char ? czezz <czezz@o2.pl>
2015-05-05 11:46 ` Re: display to_timestamp in quotas or convert to char ? Albe Laurenz <laurenz.albe@wien.gv.at>
2015-05-05 13:24 ` RE: display to_timestamp in quotas or convert to char ? czezz <czezz@o2.pl>
@ 2015-05-05 13:56 ` Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-05 14:21 ` Re: display to_timestamp in quotas or convert to char ? czezz <czezz@o2.pl>
1 sibling, 1 reply; 11+ messages in thread
From: Scott Ribe @ 2015-05-05 13:56 UTC (permalink / raw)
To: czezz <czezz@o2.pl>; +Cc: Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-admin
On May 5, 2015, at 7:24 AM, czezz <czezz@o2.pl> wrote:
>
> have already tried to convert timestamp to char like you suggested but that lead to another error.
>
> First, simply execution with NO conversion - to make sure it works like that:
> Server2=# select to_timestamp('2015-05-04 22:13:14.000', 'YYYY/MM/DD-HH24:MI:SS.FF3');
> to_timestamp
> ------------------------
> 2015-05-04 22:13:14+02
> (1 row)
>
> Secondly, to_char():
> Server2=# select to_char(to_timestamp('2015-05-04 22:13:14.000', 'YYYY/MM/DD-HH24:MI:SS.FF3'));
> ERROR: function to_char(timestamp with time zone) does not exist
> LINE 1: select to_char(to_timestamp('2015-05-04 22:13:14.000', 'YYYY...
> ^
> HINT: No function matches the given name and argument types. You might need to add explicit type casts.
>
> And this HINT seems to be odd.
WHY are you still trying to convert to_char??? Convert the varchar field to_timestamp and be done with it.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: display to_timestamp in quotas or convert to char ?
2015-05-05 09:59 display to_timestamp in quotas or convert to char ? czezz <czezz@o2.pl>
2015-05-05 11:46 ` Re: display to_timestamp in quotas or convert to char ? Albe Laurenz <laurenz.albe@wien.gv.at>
2015-05-05 13:24 ` RE: display to_timestamp in quotas or convert to char ? czezz <czezz@o2.pl>
2015-05-05 13:56 ` Re: display to_timestamp in quotas or convert to char ? Scott Ribe <scott_ribe@elevated-dev.com>
@ 2015-05-05 14:21 ` czezz <czezz@o2.pl>
2015-05-05 14:47 ` Re: display to_timestamp in quotas or convert to char ? Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 11+ messages in thread
From: czezz @ 2015-05-05 14:21 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-admin
This is because this query is executed/hardcoded in application.
And by executing it like this I can prove devs that there is a problem.
Dnia 5 maja 2015 15:56 Scott Ribe <scott_ribe@elevated-dev.com> napisał(a):
> On May 5, 2015, at 7:24 AM, czezz <czezz@o2.pl> wrote:
> >
> > have already tried to convert timestamp to char like you suggested but that lead to another error.
> >
> > First, simply execution with NO conversion - to make sure it works like that:
> > Server2=# select to_timestamp('2015-05-04 22:13:14.000', 'YYYY/MM/DD-HH24:MI:SS.FF3');
> > to_timestamp
> > ------------------------
> > 2015-05-04 22:13:14+02
> > (1 row)
> >
> > Secondly, to_char():
> > Server2=# select to_char(to_timestamp('2015-05-04 22:13:14.000', 'YYYY/MM/DD-HH24:MI:SS.FF3'));
> > ERROR: function to_char(timestamp with time zone) does not exist
> > LINE 1: select to_char(to_timestamp('2015-05-04 22:13:14.000', 'YYYY...
> > ^
> > HINT: No function matches the given name and argument types. You might need to add explicit type casts.
> >
> > And this HINT seems to be odd.
>
> WHY are you still trying to convert to_char??? Convert the varchar field to_timestamp and be done with it.
>
>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: display to_timestamp in quotas or convert to char ?
2015-05-05 09:59 display to_timestamp in quotas or convert to char ? czezz <czezz@o2.pl>
2015-05-05 11:46 ` Re: display to_timestamp in quotas or convert to char ? Albe Laurenz <laurenz.albe@wien.gv.at>
2015-05-05 13:24 ` RE: display to_timestamp in quotas or convert to char ? czezz <czezz@o2.pl>
2015-05-05 13:56 ` Re: display to_timestamp in quotas or convert to char ? Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-05 14:21 ` Re: display to_timestamp in quotas or convert to char ? czezz <czezz@o2.pl>
@ 2015-05-05 14:47 ` Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-05 15:09 ` Re: display to_timestamp in quotas or convert to char ? czezz <czezz@o2.pl>
0 siblings, 1 reply; 11+ messages in thread
From: Scott Ribe @ 2015-05-05 14:47 UTC (permalink / raw)
To: czezz <czezz@o2.pl>; +Cc: Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-admin
On May 5, 2015, at 8:21 AM, czezz <czezz@o2.pl> wrote:
>
> This is because this query is executed/hardcoded in application.
> And by executing it like this I can prove devs that there is a problem.
So you were really looking for an explanation of why it didn’t work, in order to pass along to people whom you’re having trouble convincing? OK, didn’t get that from your post, now it makes sense. It just seemed odd that you seemed to be ignoring the most correct (and easy) solution ;-)
Anyway, now the we understand that… PostgreSQL used to be more aggressive about automatically casting text types to more specific types. I suspect, though I don’t recall for sure, that this query would have worked on some earlier versions. Later versions removed some of the automatic casting, because it was found that those casts caused problems—that sometimes they would cause accidental type mismatches to be executed instead of flagged, and produce incorrect results instead of an error message, so the decision was made that if you want to compare mismatched types, you have to be explicit about it.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: display to_timestamp in quotas or convert to char ?
2015-05-05 09:59 display to_timestamp in quotas or convert to char ? czezz <czezz@o2.pl>
2015-05-05 11:46 ` Re: display to_timestamp in quotas or convert to char ? Albe Laurenz <laurenz.albe@wien.gv.at>
2015-05-05 13:24 ` RE: display to_timestamp in quotas or convert to char ? czezz <czezz@o2.pl>
2015-05-05 13:56 ` Re: display to_timestamp in quotas or convert to char ? Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-05 14:21 ` Re: display to_timestamp in quotas or convert to char ? czezz <czezz@o2.pl>
2015-05-05 14:47 ` Re: display to_timestamp in quotas or convert to char ? Scott Ribe <scott_ribe@elevated-dev.com>
@ 2015-05-05 15:09 ` czezz <czezz@o2.pl>
2015-05-05 19:03 ` Re: display to_timestamp in quotas or convert to char ? Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 11+ messages in thread
From: czezz @ 2015-05-05 15:09 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-admin
Hi,
ya, that is the background of this story.
There is one internal app in my company that frequently runs into specific error - related to that query.
Its actually not Devs team who reject to look at this but support team (which is one step before devs)
So, now showing them this same query with added to_char() they should "shut-the-front-door" and send it to correction.
...though its really difficult with them.
Anyway thank you all for your help :)
BR,
czezz
Dnia 5 maja 2015 16:47 Scott Ribe <scott_ribe@elevated-dev.com> napisał(a):
> On May 5, 2015, at 8:21 AM, czezz <czezz@o2.pl> wrote:
> >
> > This is because this query is executed/hardcoded in application.
> > And by executing it like this I can prove devs that there is a problem.
>
> So you were really looking for an explanation of why it didn’t work, in order to pass along to people whom you’re having trouble convincing? OK, didn’t get that from your post, now it makes sense. It just seemed odd that you seemed to be ignoring the most correct (and easy) solution ;-)
>
> Anyway, now the we understand that… PostgreSQL used to be more aggressive about automatically casting text types to more specific types. I suspect, though I don’t recall for sure, that this query would have worked on some earlier versions. Later versions removed some of the automatic casting, because it was found that those casts caused problems—that sometimes they would cause accidental type mismatches to be executed instead of flagged, and produce incorrect results instead of an error message, so the decision was made that if you want to compare mismatched types, you have to be explicit about it.
>
>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: display to_timestamp in quotas or convert to char ?
2015-05-05 09:59 display to_timestamp in quotas or convert to char ? czezz <czezz@o2.pl>
2015-05-05 11:46 ` Re: display to_timestamp in quotas or convert to char ? Albe Laurenz <laurenz.albe@wien.gv.at>
2015-05-05 13:24 ` RE: display to_timestamp in quotas or convert to char ? czezz <czezz@o2.pl>
2015-05-05 13:56 ` Re: display to_timestamp in quotas or convert to char ? Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-05 14:21 ` Re: display to_timestamp in quotas or convert to char ? czezz <czezz@o2.pl>
2015-05-05 14:47 ` Re: display to_timestamp in quotas or convert to char ? Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-05 15:09 ` Re: display to_timestamp in quotas or convert to char ? czezz <czezz@o2.pl>
@ 2015-05-05 19:03 ` Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 11+ messages in thread
From: Scott Ribe @ 2015-05-05 19:03 UTC (permalink / raw)
To: czezz <czezz@o2.pl>; +Cc: pgsql-admin
On May 5, 2015, at 9:09 AM, czezz <czezz@o2.pl> wrote:
>
> ya, that is the background of this story.
> …
> ...though its really difficult with them.
Oh, I hate that—knowing exactly what a bug is, and the exact fix, and not being able to get the message past support!
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
* display to_timestamp in quotas or convert to char
@ 2015-05-05 11:26 czezz <czezz@o2.pl>
0 siblings, 0 replies; 11+ messages in thread
From: czezz @ 2015-05-05 11:26 UTC (permalink / raw)
To: pgsql-admin
Hello,
This post might be unreadable in your mail client. If so try to copy it in to notepad as there is a long query statement.
I have following table in the DB.
Server2=# \d ss21_file
Table "srx.ss21_file"
Column | Type | Modifiers
-------------------+------------------------+-----------
value | character varying(30) |
fileindex | bigint |
filename | character varying(80) |
filetime | character varying(100) |
filesizeinrecords | bigint |
filesizeinbytes | bigint |
sourceid | character varying(300) |
Indexes:
"ss21_filetime" UNIQUE, btree (filetime)
When executing following query I get following error:
Server2=# SELECT value, fileIndex, fileName, fileTime, fileSizeInRecords, fileSizeInBytes, sourceID FROM ss21_file WHERE filetime < to_timestamp('2015-05-04 22:13:14.000', 'YYYY/MM/DD-HH24:MI:SS.FF3');
ERROR: operator does not exist: character varying < timestamp with time zone
LINE 1: ...ytes, sourceID FROM ss21_file WHERE filetime < to_times...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
My assumption is that, it is because "filetime" column is "character varying" and in WHERE condition value there is converted to timestamp.
When I execute this same query but instead to_timestamp I use regular string eg. '2015-05-04 22:13:14.000' (NOTE: single quotas) it works quite good.
If this is correct, is there any way to force to_"timestamp" function to be convertrd in to "character varying" or display its value in single quotas ?
Server2=# SELECT value, fileIndex, fileName, fileTime, fileSizeInRecords, fileSizeInBytes, sourceID FROM ss21_file WHERE filetime < '2015-05-04 22:13:14.000';
value | fileindex | filename | filetime | filesizeinrecords | filesizeinbytes | sourceid
----------------------+-----------+---------------------------------------+-------------------------+-------------------+-----------------+-----------------------------
-------
-903097975225368365 | 94859 | ss21_20141118020501_1166.DAT | 2014-11-18 02:05:30.109 | 2775 | 490560 | coll_SS21-Group_0_141627273
0_1634
-2781431802746610881 | 94868 | ss21_20141118042502_1175.DAT | 2014-11-18 04:25:30.154 | 1803 | 310688 | coll_SS21-Group_0_141628113
0_1643
-4731211533677294393 | 94878 | ss21_20141118065501_1185.DAT | 2014-11-18 06:55:30.380 | 14745 | 2640848 | coll_SS21-Group_0_141629013
Best regards,
czezz
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2015-05-05 19:03 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-05-05 09:59 display to_timestamp in quotas or convert to char ? czezz <czezz@o2.pl>
2015-05-05 11:46 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2015-05-05 13:21 ` Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-05 13:24 ` czezz <czezz@o2.pl>
2015-05-05 13:33 ` Igor Neyman <ineyman@perceptron.com>
2015-05-05 13:56 ` Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-05 14:21 ` czezz <czezz@o2.pl>
2015-05-05 14:47 ` Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-05 15:09 ` czezz <czezz@o2.pl>
2015-05-05 19:03 ` Scott Ribe <scott_ribe@elevated-dev.com>
2015-05-05 11:26 display to_timestamp in quotas or convert to char czezz <czezz@o2.pl>
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