Postgres Pro
Downloads
Home   >   mailing lists

Thread: You might need to add explicit type casts.

You might need to add explicit type casts.

From
kulmacet101@kulmacet.com
Date:
 I have a table running on Linux\Postgresql 8.3.4:

 CREATE TABLE "public"."active_sessions" (
   "id" VARCHAR(11) DEFAULT nextval('id_seq'::regclass) NOT NULL,
   "browser_type" VARCHAR(100),
   "session_id" VARCHAR(50),
   "ip_address" INET,
   "username" VARCHAR(50),
   "access_time" TIMESTAMP WITHOUT TIME ZONE,
   "user_rand" VARCHAR(15),
   "user_activity" TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT "active_sessions_pkey" PRIMARY KEY("id")
 ) WITHOUT OIDS;

 I can insert the TIMESTAMP('s) into this table without issue but when I
 update the new entry the following error is received:

 ERROR:  operator does not exist: character varying = integer at character
 75
 HINT:  No operator matches the given name and argument type(s). You might
 need to add explicit type casts.
 STATEMENT:  UPDATE active_sessions SET access_time = '12/28/2008 8:51:33
 PM' WHERE id = 469

 I have read several archives describing the 8.3 release notes and CASTing
 but I do not understand how to apply this information, this issue makes no
 sense. Insert = ok, Update = "BOOM!".

 I have tried this statement with CURRENT_TIMESTAMP, now() but neither
 works.

 This statement is created using PHP 5.2.6 on a Linux server using PDO and
 prepared statements

 Any help with this issue is appreciated.

 Thanks in advance,
 Kulmacet

Re: You might need to add explicit type casts.

From
Andrew McMillan
Date:
On Sun, 2008-12-28 at 21:30 -0600, kulmacet101@kulmacet.com wrote:
> I have a table running on Linux\Postgresql 8.3.4:
>
>  CREATE TABLE "public"."active_sessions" (
>    "id" VARCHAR(11) DEFAULT nextval('id_seq'::regclass) NOT NULL,
>    "browser_type" VARCHAR(100),
>    "session_id" VARCHAR(50),
>    "ip_address" INET,
>    "username" VARCHAR(50),
>    "access_time" TIMESTAMP WITHOUT TIME ZONE,
>    "user_rand" VARCHAR(15),
>    "user_activity" TIMESTAMP WITHOUT TIME ZONE,
>    CONSTRAINT "active_sessions_pkey" PRIMARY KEY("id")
>  ) WITHOUT OIDS;
>
>  I can insert the TIMESTAMP('s) into this table without issue but when I
>  update the new entry the following error is received:

Hi,

You need to:

UPDATE active_sessions SET access_time = '12/28/2008 8:51:33PM'::timestamp without time zone WHERE id = 469

or alternatively:

UPDATE active_sessions SET access_time = current_timestamp::timestamp without time zone WHERE id = 469

I'm surprised that:

UPDATE active_sessions SET access_time = current_timestamp WHERE id = 469

doesn't also work fine though - it should cast timestamp => timestamp
without time zone without any trouble.

Cheers,
                    Andrew.

------------------------------------------------------------------------
andrew (AT) morphoss (DOT) com                            +64(272)DEBIAN
                      You fill a much-needed gap.
------------------------------------------------------------------------

Re: You might need to add explicit type casts.

From
kulmacet101@kulmacet.com
Date:
Thank you for the quick response.

I also found that the WHERE statements were not properly quoted, a 101
mistake.

WHERE id = 469 => WHERE id = '469'

The update seems to be working as expected.

kulmacet

> On Sun, 2008-12-28 at 21:30 -0600, kulmacet101@kulmacet.com wrote:
>> I have a table running on Linux\Postgresql 8.3.4:
>>
>>  CREATE TABLE "public"."active_sessions" (
>>    "id" VARCHAR(11) DEFAULT nextval('id_seq'::regclass) NOT NULL,
>>    "browser_type" VARCHAR(100),
>>    "session_id" VARCHAR(50),
>>    "ip_address" INET,
>>    "username" VARCHAR(50),
>>    "access_time" TIMESTAMP WITHOUT TIME ZONE,
>>    "user_rand" VARCHAR(15),
>>    "user_activity" TIMESTAMP WITHOUT TIME ZONE,
>>    CONSTRAINT "active_sessions_pkey" PRIMARY KEY("id")
>>  ) WITHOUT OIDS;
>>
>>  I can insert the TIMESTAMP('s) into this table without issue but when I
>>  update the new entry the following error is received:
>
> Hi,
>
> You need to:
>
> UPDATE active_sessions SET access_time = '12/28/2008 8:51:33PM'::timestamp
> without time zone WHERE id = 469
>
> or alternatively:
>
> UPDATE active_sessions SET access_time = current_timestamp::timestamp
> without time zone WHERE id = 469
>
> I'm surprised that:
>
> UPDATE active_sessions SET access_time = current_timestamp WHERE id = 469
>
> doesn't also work fine though - it should cast timestamp => timestamp
> without time zone without any trouble.
>
> Cheers,
>                     Andrew.
>
> ------------------------------------------------------------------------
> andrew (AT) morphoss (DOT) com                            +64(272)DEBIAN
>                       You fill a much-needed gap.
> ------------------------------------------------------------------------
>
>
>

Re: You might need to add explicit type casts.

From
Raymond O'Donnell
Date:
On 29/12/2008 04:17, kulmacet101@kulmacet.com wrote:

> I also found that the WHERE statements were not properly quoted, a 101
> mistake.
>
> WHERE id = 469 => WHERE id = '469'

Yes, this commonly bites people moving from earlier versions of
PostgreSQL to 8.3 - a lot of automatic casts have been removed. The
original error message suggests that this was your problem all along.

I'm curious as to why you've defined the "id" column as VARCHAR when you
seem to expect it to hold integers....unless of course there will be
non-numeric characters there also?

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------