You might need to add explicit type casts.

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 =3D integer at charac=
ter
75
HINT: No operator matches the given name and argument type(s). You migh=
t
need to add explicit type casts.
STATEMENT: UPDATE active_sessions SET access_time =3D '12/28/2008 8:51:=
33
PM' WHERE id =3D 469

I have read several archives describing the 8.3 release notes and CASTin=
g
but I do not understand how to apply this information, this issue makes =
no
sense. Insert =3D ok, Update =3D "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 an=
d
prepared statements

Any help with this issue is appreciated.

Thanks in advance,
Kulmacet



--=20
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: You might need to add explicit type casts.

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.
------------------------------------------------------------ ------------



--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: You might need to add explicit type casts.

Thank you for the quick response.

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

WHERE id =3D 469 =3D> WHERE id =3D '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 =3D '12/28/2008 8:51:33PM'::time=
stamp
> without time zone WHERE id =3D 469
>
> or alternatively:
>
> UPDATE active_sessions SET access_time =3D current_timestamp::timestamp
> without time zone WHERE id =3D 469
>
> I'm surprised that:
>
> UPDATE active_sessions SET access_time =3D current_timestamp WHERE id =3D=
469
>
> doesn't also work fine though - it should cast timestamp =3D> timestamp
> without time zone without any trouble.
>
> Cheers,
> Andrew.
>
> ------------------------------------------------------------ -----------=
-
> andrew (AT) morphoss (DOT) com +64(272)DEBIA=
N
> You fill a much-needed gap.
> ------------------------------------------------------------ -----------=
-
>
>
>



--=20
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: You might need to add explicit type casts.

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
------------------------------------------------------------ ------

--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php