Thread: Cast in PG 8.3

<br /><br /><font face="Courier New, Courier, monospace">Hi all,<br /><br /> I´m testing the new version of PostgreSQL
inwindows 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>CreateTable temp (id integer);<br /> insert into temp values (1215);<br /> insert into temp values (1216);<br />
insertinto temp values (1217);<br /> insert into temp values (1218);<br /> insert into temp values (1219);<br /> insert
intotemp values (1220);<br /> insert into temp values (1221);<br /> insert into temp values (1222);<br /> SELECT * FROM
TEMPWHERE 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
existmore in PG 8.3<br /><br /> CREATE CAST (int AS text) WITH FUNCTION text(int);<br /><br /> can everyone help me to
createa cast ou other solution ?<br /><br /> Thank´s <br /><br /><br /> Franklin<br /><br /><br /><br /><br /><br /><br
/><br/><br /><br /><br /></font> 

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

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

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

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

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.

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

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,
andchar(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.

Hi,

I have two postgresql functions. One function is calling another function
for certain value. I want that these two functions work under single
transaction so that even if the value gets generated in the second function
and the first function that calls the second function fails. Then the value
generated in the second function should also roll back.

Please let me know if we can execute two functions of postgresql in a single
transaction.

Thanks,
Jyoti Seth

Robins Tharakan  wrote:
> > It can be done, but it depends on how you are generating the value in the
> > first function.
> > If you sequences though you may have to take care of reverting it
> > yourself.

Sequences had been constructed in this manner not to cause pain for users -- think of it.
So i advise to avoid reverting sequences ANYWAY.
Even if you think that this would not cause a problem.


> > ---------- Forwarded message ----------
> > From: Jyoti Seth <jyotiseth2001@gmail.com>
> > Date: Feb 6, 2008 11:51 AM
> > Subject: [SQL] Multiple postgresql functions in a single transaction
> > To: pgsql-sql@postgresql.org
> >
> >
> > Hi,
> >
> > I have two postgresql functions. One function is calling another function
> > for certain value. I want that these two functions work under single
> > transaction so that even if the value gets generated in the second
> > function
> > and the first function that calls the second function fails. Then the
> > value
> > generated in the second function should also roll back.
> >
> > Please let me know if we can execute two functions of postgresql in a
> > single
> > transaction.
> >
> > Thanks,
> > Jyoti Seth
> >
> >
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
> >
> >