Casting data types in PostgreSQL

adamgeorge

God PostgreSQL is really starting to anoy me. Everytime I want to do something simple, I have to spend hours to trying to work out cause it's so damn fussy about everything.

I need to be able to execute a query like this:

SELECT lower(field) FROM TABLE;

Easy hey? Except field can be either a date, text/char/varchar or a numeric data type, which PostgreSQL is too dumb to handle.

I'm having trouble cause the stupid lower() function in PostgreSQL only takes strings as an argument. MySQL doesn't have this problem.

K, so how do I do a cast?

I know you can do this:

cast(field as TEXT)

but ... it doesn't work for numeric types! How clever!

If I use to_char(field) I get an error message for all the varchar fields, cause to_char() won't except strings as an argument~@!

This really anoys me, cause I can't work out how I can do this query so it will work for different data types. It doesn't have to cover all of them. Just the main ones that I mentioned.

Is there a way I can do a CASE statement to check the data type?

SELECT (CASE WHEN field is TEXT then lower(field) when NUMERIC then lower(to_char(field)) else lower(field) END) as field FROM table.

Surely this is possible. I've heard people knocking MySQL on this board, but at least MySQL will do all of this automatically for you.

PostgreSQL has been nothing but a pain since I've been using it. Unfortunatly I'm stuck with it for this project.

Any help would be much appreciated.

Thanks,
-Adam 🙂

ultraslacker

Use LOWER(fieldname::text)

chriskl

Of course, you could always use MySQL where you can happily insert NULL values into NOT NULL fields - how helpful! If PostgreSQL is being picky about something, then there's a REASON for it.

Of couse, PostgreSQL DOES allow you to LOWER() on most fields, I have no idea how old the version of PostgreSQL you are using is but in 7.3.2:

usa=# select lower('2003-01-01'::date);

lower

2003-01-01
(1 row)

usa=# select lower('2003'::numeric);

lower

2003
(1 row)

usa=# select lower(2003::numeric);

lower

2003
(1 row)

usa=# select lower(2003::integer);

lower

2003
(1 row)

What's your problem?

Also, all the casts work just fine:

usa=# select cast (1234213 as text);

text

1234213
(1 row)

usa=# select cast ('\1234213' as text);

text

S4213
(1 row)

usa=# select cast ('234213' as text);

text

234213
(1 row)

usa=# select cast ('234213'::numeric as text);

text

234213
(1 row)

usa=# select cast ('234213'::numeric as text);

Chris

adamgeorge

K, thanks guys.

To ultraslacker I've tried fieldname::TEXT. It's just another way of writing CAST(fieldname as TEXT). You still can't convert numeric types to strings.

Yes, this does work:

select lower(1234::text);

but this doesn't:

select lower(iPostCode::text) from table;
ERROR: Cannot cast type 'numeric' to 'text'

where iPostCode is NUMERIC(4,0) and that's what I need to be able to do.

And to chriskl well, I've tried your tests on my PostgreSQL and there must be a descrepency in our PostgreSQL versions becasuse I do not obtain the same results as you for these tests:

webdb=> select lower('1003'::numeric);
ERROR: Function 'lower(numeric)' does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts

webdb=> select lower(1003::numeric);
ERROR: Function 'lower(numeric)' does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
webdb=>

select cast ('234213'::numeric as text);
ERROR: Cannot cast type 'numeric' to 'text'

Interestingly enough select lower(2003::integer); actually works... which is strange. I might experiment with this further...

Anyway, as you can see this is the point of my frustration. On your version they must have fixed these problems up, because they obviously are problems and it's just really anoying to know I can't even do simple stuff like this without it cracking at me.

Anyway, this is the error message I'm getting from PostgreSQL for my query:

ERROR: Function 'lower(numeric)' does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts

So what can I do? The only answer I've seen on the web is to use to_char() but that won't accept a string as a parameter.

The query has to be able to handle both numeric types and strings.

hehe. Yeah, I know PostgreSQL is more advanced then MySQL etc, but at least when you want to do something simple, there's a simple solution to it in MySQL. My experience with PostgreSQL has not been the same.

anyway, thanks for your reply guys.

cya
-Adam 🙂

Sxooter

What version of Postgresql are you using, by the way? Unlike many products out there, updating to the latest version of postgresql is usually a very positive experience, as it seems to get better with each release.

If you've got a problem that's already been fixed by a new release, then don't bitch about the old problem, upgrade.

Surely this is possible. I've heard people knocking MySQL on this board, but at least MySQL will do all of this automatically for you.

Let me ask you a question. If you ask a database to do something, and the database isn't sure what you meant, is it better for the database to error out, or just take its best guess and hope that's what you want?

While it may be easier to develop with MySQL, it's harder to be sure that the data being put into it is coherent and correct.

A database that does as little error checking as MySQL is not suitable for storing data that HAS to be correct.

adamgeorge

Hmmm... well silly old me can't seem to find out what version it is other then it's from at least 2001.

And of course I would upgrade instead of going to all this trouble to find a solution, but I'm not in charge of the server. I've put in a request for a upgrade to the newest version of PostgreSQL but who knows if, or when they will do it.

Anyway, I acknowledge the problems with MySQL, but of course it's built this way for speed and efficency issues. Which is fine by me, as most of the time I'm not doing anything that requires great data integrety checking. If I require a database that does, then as in this case I'll use PostgreSQL or other commerical database.

Still an interesting point... as far as I'm awear MySQL is the more popular of the two... I'm sure there's a few issues there as to why, but ease of development use would have to be one of them. I've developed in Oracle and MS SQL Server as well, and never had as many problems as I have in PostgreSQL.

hehe. Apologies to those who love PostgreSQL. It is pretty good, it just gets me frustrated sometimes though.

-Adam 🙂

Sxooter

hehe. Apologies to those who love PostgreSQL. It is pretty good, it just gets me frustrated sometimes though.

I love it and it still frustrates the hell out of me sometimes. :-)

I wasn't trying to dog you out, just pointing out that you really should upgrade if you can. Note that all versions prior to 7.2.4 and 7.3.2 have data loss issues that can be triggered by certain very high traffic situations, so if that's not enough impetus to get your sys admin to upgrade I don't know what is. Pizza? That or coffee seem to fuel most SAs.

Plus, each major sub rev is about twice as fast as the previous version. i.e. 7.3 is about twice as fast as 7.2, which is about twice as fast as 7.1, etc...

Postgresql circa 2001 would likely be 7.0 or 7.1, both of which are really starting to show their age by now.

chriskl

I suggest upgrading.

The only other thing i can mention is that I can't see any way that a web app could be designed where calling lower(numeric) is anything but a bug!

Why on earth would you need to do it?

Chris

csn

Sub-script numbers? ;P