Skip to content

Instantly share code, notes, and snippets.

Created September 21, 2011 05:47
Show Gist options
  • Save anonymous/1231347 to your computer and use it in GitHub Desktop.
Save anonymous/1231347 to your computer and use it in GitHub Desktop.
Varchar to integer on the fly in Postgres
db=> CREATE FUNCTION toint(varchar)
RETURNS integer
STRICT IMMUTABLE LANGUAGE SQL AS
'SELECT cast($1 as integer);';
db=> \d public.items
Column | Type
-----------------------+------------------------
...
description | character varying(255)
...
lastvalue | character varying(255)
...
db=> select sum(lastvalue) from public.items where description like '%YOUR ITEM%' and lastvalue is not null;
ERROR: function sum(character varying) does not exist
LINE 1: select sum(lastvalue) from public.items where description li...
db=> select sum(toint(lastvalue)) from public.items where description like '%YOUR ITEM%' and lastvalue is not null;
sum
-------
100000
(1 row)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment