pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Cast in PG 8.3
8+ messages / 6 participants
[nested] [flat]
* Cast in PG 8.3
@ 2008-02-05 19:22 Franklin Haut <franklin.haut@gmail.com>
2008-02-05 19:18 ` Re: Cast in PG 8.3 Jaime Casanova <systemguards@gmail.com>
2008-02-05 21:13 ` Re: Cast in PG 8.3 Tom Lane <tgl@sss.pgh.pa.us>
2008-02-05 22:39 ` Re: Cast in PG 8.3 Volkan YAZICI <yazicivo@ttmail.com>
0 siblings, 3 replies; 8+ messages in thread
From: Franklin Haut @ 2008-02-05 19:22 UTC (permalink / raw)
To: pgsql-sql
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
<br>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
<font face="Courier New, Courier, monospace">Hi all,<br>
<br>
I´m testing the new version of PostgreSQL in windows plataform but i
have a problem.<br>
<br>
<br>
<b>In PG 8.2 </b><br>
<br>
Create Table temp (id int4);<br>
insert into temp values (1215);<br>
insert into temp values (1216);<br>
insert into temp values (1217);<br>
insert into temp values (1218);<br>
insert into temp values (1219);<br>
insert into temp values (1220);<br>
insert into temp values (1221);<br>
insert into temp values (1222);<br>
SELECT * FROM TEMP WHERE id ilike ('%122%');<br>
<br>
return ok, 3 rows (1220, 1221 and 1222)<br>
<br>
<br>
<br>
<b>In PG 8.3<br>
<br>
</b>Create Table temp (id integer);<br>
insert into temp values (1215);<br>
insert into temp values (1216);<br>
insert into temp values (1217);<br>
insert into temp values (1218);<br>
insert into temp values (1219);<br>
insert into temp values (1220);<br>
insert into temp values (1221);<br>
insert into temp values (1222);<br>
SELECT * FROM TEMP WHERE id ilike ('%122%');<br>
<br>
<b>Error:</b><br>
operator does not exist: integer ~~* unknown at character 31<br>
HINT: No operator matches the given name and argument type(s). <br>
You might need to add explicit type casts.<br>
<br>
<br>
I Know that changing the SQL command to :<br>
</font><font face="Courier New, Courier, monospace">SELECT * FROM TEMP
WHERE CAST(id AS TEXT) ilike ('%122%'); <br>
work´s but for now isn't possible... :(<br>
<br>
</font><font face="Courier New, Courier, monospace"><br>
I Tries create a cast but the function text doesn't exist more in PG
8.3<br>
<br>
CREATE CAST (int AS text) WITH FUNCTION text(int);<br>
<br>
can everyone help me to create a cast ou other solution ?<br>
<br>
Thank´s <br>
<br>
<br>
Franklin<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
</font>
</body>
</html>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Cast in PG 8.3
2008-02-05 19:22 Cast in PG 8.3 Franklin Haut <franklin.haut@gmail.com>
@ 2008-02-05 19:18 ` Jaime Casanova <systemguards@gmail.com>
2008-02-05 19:39 ` Re: Cast in PG 8.3 Li, Jingfa <jinli@paypal.com>
2008-02-05 20:12 ` Re: Cast in PG 8.3 Markus Bertheau <mbertheau.pg@googlemail.com>
2 siblings, 2 replies; 8+ messages in thread
From: Jaime Casanova @ 2008-02-05 19:18 UTC (permalink / raw)
To: Franklin Haut <franklin.haut@gmail.com>; +Cc: pgsql-sql
On Feb 5, 2008 2:22 PM, Franklin Haut <franklin.haut@gmail.com> wrote:
>
>
> Hi all,
>
> I´m testing the new version of PostgreSQL in windows plataform but i have a
> problem.
>
>
> In PG 8.2
>
> Create Table temp (id int4);
> insert into temp values (1215);
> insert into temp values (1216);
> insert into temp values (1217);
> insert into temp values (1218);
> insert into temp values (1219);
> insert into temp values (1220);
> insert into temp values (1221);
> insert into temp values (1222);
> SELECT * FROM TEMP WHERE id ilike ('%122%');
>
> return ok, 3 rows (1220, 1221 and 1222)
>
>
>
> In PG 8.3
>
> Create Table temp (id integer);
> insert into temp values (1215);
> insert into temp values (1216);
> insert into temp values (1217);
> insert into temp values (1218);
> insert into temp values (1219);
> insert into temp values (1220);
> insert into temp values (1221);
> insert into temp values (1222);
> SELECT * FROM TEMP WHERE id ilike ('%122%');
>
> Error:
> operator does not exist: integer ~~* unknown at character 31
> HINT: No operator matches the given name and argument type(s).
> You might need to add explicit type casts.
>
did you read the release notes?
http://www.postgresql.org/docs/current/static/release-8-3.html
"""
Non-character data types are no longer automatically cast to TEXT (Peter, Tom)
"""
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Cast in PG 8.3
2008-02-05 19:22 Cast in PG 8.3 Franklin Haut <franklin.haut@gmail.com>
2008-02-05 19:18 ` Re: Cast in PG 8.3 Jaime Casanova <systemguards@gmail.com>
@ 2008-02-05 19:39 ` Li, Jingfa <jinli@paypal.com>
2008-02-05 19:46 ` Re: Cast in PG 8.3 Jaime Casanova <systemguards@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Li, Jingfa @ 2008-02-05 19:39 UTC (permalink / raw)
To: Jaime Casanova <systemguards@gmail.com>; Franklin Haut <franklin.haut@gmail.com>; +Cc: pgsql-sql
check the typo -- "ilike"
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Jaime Casanova
Sent: Tuesday, February 05, 2008 11:19 AM
To: Franklin Haut
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Cast in PG 8.3
On Feb 5, 2008 2:22 PM, Franklin Haut <franklin.haut@gmail.com> wrote:
>
>
> Hi all,
>
> I´m testing the new version of PostgreSQL in windows plataform but i
> have a problem.
>
>
> In PG 8.2
>
> Create Table temp (id int4);
> insert into temp values (1215);
> insert into temp values (1216);
> insert into temp values (1217);
> insert into temp values (1218);
> insert into temp values (1219);
> insert into temp values (1220);
> insert into temp values (1221);
> insert into temp values (1222);
> SELECT * FROM TEMP WHERE id ilike ('%122%');
>
> return ok, 3 rows (1220, 1221 and 1222)
>
>
>
> In PG 8.3
>
> Create Table temp (id integer);
> insert into temp values (1215);
> insert into temp values (1216);
> insert into temp values (1217);
> insert into temp values (1218);
> insert into temp values (1219);
> insert into temp values (1220);
> insert into temp values (1221);
> insert into temp values (1222);
> SELECT * FROM TEMP WHERE id ilike ('%122%');
>
> Error:
> operator does not exist: integer ~~* unknown at character 31
> HINT: No operator matches the given name and argument type(s).
> You might need to add explicit type casts.
>
did you read the release notes?
http://www.postgresql.org/docs/current/static/release-8-3.html
"""
Non-character data types are no longer automatically cast to TEXT (Peter, Tom) """
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Cast in PG 8.3
2008-02-05 19:22 Cast in PG 8.3 Franklin Haut <franklin.haut@gmail.com>
2008-02-05 19:18 ` Re: Cast in PG 8.3 Jaime Casanova <systemguards@gmail.com>
2008-02-05 19:39 ` Re: Cast in PG 8.3 Li, Jingfa <jinli@paypal.com>
@ 2008-02-05 19:46 ` Jaime Casanova <systemguards@gmail.com>
2008-02-05 20:07 ` Re: Cast in PG 8.3 Li, Jingfa <jinli@paypal.com>
0 siblings, 1 reply; 8+ messages in thread
From: Jaime Casanova @ 2008-02-05 19:46 UTC (permalink / raw)
To: Li, Jingfa <jinli@paypal.com>; +Cc: Franklin Haut <franklin.haut@gmail.com>; pgsql-sql
On Feb 5, 2008 2:39 PM, Li, Jingfa <jinli@paypal.com> wrote:
> check the typo -- "ilike"
>
that's not a typo, it's "case insensitive LIKE"
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Cast in PG 8.3
2008-02-05 19:22 Cast in PG 8.3 Franklin Haut <franklin.haut@gmail.com>
2008-02-05 19:18 ` Re: Cast in PG 8.3 Jaime Casanova <systemguards@gmail.com>
2008-02-05 19:39 ` Re: Cast in PG 8.3 Li, Jingfa <jinli@paypal.com>
2008-02-05 19:46 ` Re: Cast in PG 8.3 Jaime Casanova <systemguards@gmail.com>
@ 2008-02-05 20:07 ` Li, Jingfa <jinli@paypal.com>
0 siblings, 0 replies; 8+ messages in thread
From: Li, Jingfa @ 2008-02-05 20:07 UTC (permalink / raw)
To: Jaime Casanova <systemguards@gmail.com>; +Cc: Franklin Haut <franklin.haut@gmail.com>; pgsql-sql
the column is an integer, no sense for case sensitive or insensitive...
change it to 'like', you'll get what you want.
-----Original Message-----
From: Jaime Casanova [mailto:systemguards@gmail.com]
Sent: Tuesday, February 05, 2008 11:47 AM
To: Li, Jingfa
Cc: Franklin Haut; pgsql-sql@postgresql.org
Subject: Re: [SQL] Cast in PG 8.3
On Feb 5, 2008 2:39 PM, Li, Jingfa <jinli@paypal.com> wrote:
> check the typo -- "ilike"
>
that's not a typo, it's "case insensitive LIKE"
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying to
produce bigger and better idiots.
So far, the universe is winning."
Richard Cook
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Cast in PG 8.3
2008-02-05 19:22 Cast in PG 8.3 Franklin Haut <franklin.haut@gmail.com>
2008-02-05 19:18 ` Re: Cast in PG 8.3 Jaime Casanova <systemguards@gmail.com>
@ 2008-02-05 20:12 ` Markus Bertheau <mbertheau.pg@googlemail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Markus Bertheau @ 2008-02-05 20:12 UTC (permalink / raw)
To: Jaime Casanova <systemguards@gmail.com>; +Cc: Franklin Haut <franklin.haut@gmail.com>; pgsql-sql
2008/2/6, Jaime Casanova <systemguards@gmail.com>:
> did you read the release notes?
Obviously he did:
> I Know that changing the SQL command to :
> SELECT * FROM TEMP WHERE CAST(id AS TEXT) ilike ('%122%');
> work´s but for now isn't possible... :(
>
>
> I Tries create a cast but the function text doesn't exist more in PG 8.3
>
> CREATE CAST (int AS text) WITH FUNCTION text(int);
>
> can everyone help me to create a cast ou other solution ?
So the question stands.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Cast in PG 8.3
2008-02-05 19:22 Cast in PG 8.3 Franklin Haut <franklin.haut@gmail.com>
@ 2008-02-05 21:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2008-02-05 21:13 UTC (permalink / raw)
To: Franklin Haut <franklin.haut@gmail.com>; +Cc: pgsql-sql
Franklin Haut <franklin.haut@gmail.com> writes:
> Create Table temp (id int4);
> insert into temp values (1215);
> insert into temp values (1216);
> insert into temp values (1217);
> insert into temp values (1218);
> insert into temp values (1219);
> insert into temp values (1220);
> insert into temp values (1221);
> insert into temp values (1222);
> SELECT * FROM TEMP WHERE id ilike ('%122%');
>
> return ok, 3 rows (1220, 1221 and 1222)
That usage is simply bizarre. Why don't you define the column as text,
instead, if that's the type of query you intend to perform on it?
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Cast in PG 8.3
2008-02-05 19:22 Cast in PG 8.3 Franklin Haut <franklin.haut@gmail.com>
@ 2008-02-05 22:39 ` Volkan YAZICI <yazicivo@ttmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Volkan YAZICI @ 2008-02-05 22:39 UTC (permalink / raw)
To: Franklin Haut <franklin.haut@gmail.com>; +Cc: pgsql-sql
Franklin Haut <franklin.haut@gmail.com> writes:
> I Tries create a cast but the function text doesn't exist more in PG 8.3
sql-createcast.html tells that
"It is normally not necessary to create casts between user-defined
types and the standard string types (text, varchar, and
char(n)). PostgreSQL will automatically handle a cast to a string
type by invoking the other type's output function, ..."
Therefore, this might help you:
test=# SELECT typoutput FROM pg_type WHERE typname = 'int4';
typoutput
-----------
int4out
(1 row)
Regards.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2008-02-05 22:39 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-05 19:22 Cast in PG 8.3 Franklin Haut <franklin.haut@gmail.com>
2008-02-05 19:18 ` Jaime Casanova <systemguards@gmail.com>
2008-02-05 19:39 ` Li, Jingfa <jinli@paypal.com>
2008-02-05 19:46 ` Jaime Casanova <systemguards@gmail.com>
2008-02-05 20:07 ` Li, Jingfa <jinli@paypal.com>
2008-02-05 20:12 ` Markus Bertheau <mbertheau.pg@googlemail.com>
2008-02-05 21:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-02-05 22:39 ` Volkan YAZICI <yazicivo@ttmail.com>
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