Re: Casting varchar to interval.?

Поиск
Список
Период
Сортировка
Искать

Re: Casting varchar to interval.?

От:
Richard Huxton <dev@archonet.com>
Дата:
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

Casting varchar to interval.?

От:
"D. Dante Lorenso" <dante@lorenso.com>
Дата:
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

Re: Casting varchar to interval.?

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Richard Huxton  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

Re: Casting varchar to interval.?

От:
"Jim Wilson" <jimw@kelcomaine.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"  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-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?
FAQ