By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,903 Members | 1,067 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,903 IT Pros & Developers. It's quick & easy.

Casting varchar to interval.?

P: n/a
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...7/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
da***@lorenso.com


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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" <da***@lorenso.com> said:
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...7/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?

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #2

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #3

P: n/a
Richard Huxton <de*@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.