Casting varchar to interval.?
I was hoping to cast a varchar to an interval.
How is this done? Nobody has provided an answer to
this questing when asked in the past:
http://archives.postgresql.org/pgsql-general/2002-07/msg01482.php
I have a table 'config' that contains:
CREATE TABLE "public"."config" (
"config_key" VARCHAR(64) NOT NULL,
"config_value" VARCHAR(256) NOT NULL,
PRIMARY KEY("config_key")
) WITH OIDS;
So I can do this:
# SELECT config_value
# FROM config
# WHERE config_key = 'commission_withhold_interval';
config_value
--------------
1 month
(1 row)
But I can't do this:
# SELECT config_value::interval
# FROM config
# WHERE config_key = 'commission_withhold_interval';
ERROR: cannot cast type character varying to interval
Why doesn't that work?
Dante
----------
D. Dante Lorenso
dante@lorenso.com
Is this a bug? You can work around it by wrapping the varying in a trim()
function (e.g. select trim(config_value)::interval from config). I've solved
another suspicious looking issue with that same kind of workaround.
Best regards,
Jim Wilson
"D. Dante Lorenso" <dante@lorenso.com> said:
Show quoted text
I was hoping to cast a varchar to an interval.
How is this done? Nobody has provided an answer to
this questing when asked in the past:http://archives.postgresql.org/pgsql-general/2002-07/msg01482.php
I have a table 'config' that contains:
CREATE TABLE "public"."config" (
"config_key" VARCHAR(64) NOT NULL,
"config_value" VARCHAR(256) NOT NULL,
PRIMARY KEY("config_key")
) WITH OIDS;So I can do this:
# SELECT config_value
# FROM config
# WHERE config_key = 'commission_withhold_interval';
config_value
--------------
1 month
(1 row)But I can't do this:
# SELECT config_value::interval
# FROM config
# WHERE config_key = 'commission_withhold_interval';ERROR: cannot cast type character varying to interval
Why doesn't that work?
On Monday 26 January 2004 14:43, Jim Wilson wrote:
Is this a bug? You can work around it by wrapping the varying in a trim()
function (e.g. select trim(config_value)::interval from config). I've
solved another suspicious looking issue with that same kind of workaround.
The trim() is probably a coincidence (although I haven't tested extensively).
The issue is that there is a cast from text => interval but not from varchar.
The solution is to do something like:
config_value::text::interval
This might be fixed in 7.4.1, but you'll need to check.
--
Richard Huxton
Archonet Ltd
Richard Huxton <dev@archonet.com> writes:
The trim() is probably a coincidence (although I haven't tested extensively).
The issue is that there is a cast from text => interval but not from varchar.
The solution is to do something like:
config_value::text::interval
The reason trim() works is that its result is of type text. A simple
cast is a more efficient solution though.
This might be fixed in 7.4.1, but you'll need to check.
No, the cast still isn't there. You could add it yourself though, see
CREATE CAST.
regards, tom lane