pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
You might need to add explicit type casts.
4+ messages / 3 participants
[nested] [flat]

* You might need to add explicit type casts.
@ 2008-12-29 03:30  kulmacet101@kulmacet.com
  0 siblings, 1 reply; 4+ messages in thread

From: kulmacet101@kulmacet.com @ 2008-12-29 03:30 UTC (permalink / raw)
  To: pgsql-php@postgresql.org.

 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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: You might need to add explicit type casts.
@ 2008-12-29 04:07  Andrew McMillan <andrew@morphoss.com>
  parent: kulmacet101@kulmacet.com
  0 siblings, 0 replies; 4+ messages in thread

From: Andrew McMillan @ 2008-12-29 04:07 UTC (permalink / raw)
  To: kulmacet101@kulmacet.com; +Cc: pgsql-php@postgresql.org.

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: You might need to add explicit type casts.
@ 2008-12-29 04:17  kulmacet101@kulmacet.com
  0 siblings, 1 reply; 4+ messages in thread

From: kulmacet101@kulmacet.com @ 2008-12-29 04:17 UTC (permalink / raw)
  To: Andrew McMillan <andrew@morphoss.com>; +Cc: pgsql-php

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: You might need to add explicit type casts.
@ 2008-12-29 11:21  Raymond O'Donnell <rod@iol.ie>
  parent: kulmacet101@kulmacet.com
  0 siblings, 0 replies; 4+ messages in thread

From: Raymond O'Donnell @ 2008-12-29 11:21 UTC (permalink / raw)
  To: kulmacet101@kulmacet.com; +Cc: Andrew McMillan <andrew@morphoss.com>; pgsql-php

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2008-12-29 11:21 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-12-29 03:30 You might need to add explicit type casts. kulmacet101@kulmacet.com
2008-12-29 04:07 ` Andrew McMillan <andrew@morphoss.com>
2008-12-29 04:17 Re: You might need to add explicit type casts. kulmacet101@kulmacet.com
2008-12-29 11:21 ` Raymond O'Donnell <rod@iol.ie>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox