Postgres Pro
Downloads
Home   >   mailing lists

Thread: Newbie question about casting literals - oracle/postgres

Newbie question about casting literals - oracle/postgres

From
"Naeem Bari"
Date:

Ok,

 

I have a query that runs fine in oracle:

 

   select driver_id, 'GREEN' as color, pos_date, pos_lat, pos_lon

   from driver_pos

   where driver_id = 1

   order by pos_date

 

The only way this works in postgres is by casting ‘GREEN’ to text using ‘GREEN’::text

 

The problem is then this does not work with oracle. Since my software has to support both databases, I am left in a bit of a bind. Any ideas on how to make postgres accept ‘GREEN’ as text without my having to spell it out by casting?

 

Thanks!

naeem

Re: Newbie question about casting literals - oracle/postgres

From
"Joshua D. Drake"
Date:

The only way this works in postgres is by casting ‘GREEN’ to text using ‘GREEN’::text

 

The problem is then this does not work with oracle. Since my software has to support both databases, I am left in a bit of a bind. Any ideas on how to make postgres accept ‘GREEN’ as text without my having to spell it out by casting?

You could create separate views for both. That means you would have to keep seperate views for each database but the SQL in your code would be the same.

Sincerely,

Joshua D. Drake



 

Thanks!

naeem



-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

Re: Newbie question about casting literals - oracle/postgres

From
Dianne Yumul
Date:
hi,

I think it works for me.  what version of postgres do you have? maybe
you just need to upgrade : )

test=# select cust_id, 'TEST' as test, cust_address from customers;
   cust_id   | test |     cust_address
------------+------+----------------------
  1000000001 | TEST | 200 Maple Lane
  1000000002 | TEST | 333 South Lake Drive
  1000000003 | TEST | 1 Sunny Place
  1000000004 | TEST | 829 Riverside Drive
  1000000005 | TEST | 4545 53rd Street
  1000000006 | TEST |
(6 rows)

test=# select version();
                                                             version
------------------------------------------------------------------------
--------------------------------------------------------
  PostgreSQL 7.3.7 on powerpc-unknown-linux-gnu, compiled by GCC gcc
(GCC) 3.2.3 20030422 (Gentoo Linux 1.4 3.2.3-r4, propolice)
(1 row)

hth,

dianne

On Oct 26, 2004, at 6:24 PM, Naeem Bari wrote:

> Ok,
>
>  
>
> I have a query that runs fine in oracle:
>
>  
>
>    select driver_id, 'GREEN' as color, pos_date, pos_lat, pos_lon
>
>    from driver_pos
>
>    where driver_id = 1
>
>    order by pos_date
>
>  
>
> The only way this works in postgres is by casting ‘GREEN’ to text
> using ‘GREEN’::text
>
>  
>
> The problem is then this does not work with oracle. Since my software
> has to support both databases, I am left in a bit of a bind. Any ideas
> on how to make postgres accept ‘GREEN’ as text without my having to
> spell it out by casting?
>
>  
>
> Thanks!
>
> naeem

Re: Newbie question about casting literals - oracle/postgres

From
Alvaro Herrera
Date:
On Tue, Oct 26, 2004 at 08:24:56PM -0500, Naeem Bari wrote:

> The only way this works in postgres is by casting 'GREEN' to text using
> 'GREEN'::text
>
> The problem is then this does not work with oracle.

So use a standards-conformant cast, like

cast('GREEN' as text)

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Those who use electric razors are infidels destined to burn in hell while
we drink from rivers of beer, download free vids and mingle with naked
well shaved babes." (http://slashdot.org/comments.pl?sid=44793&cid=4647152)