Created
September 21, 2011 05:47
-
-
Save anonymous/1231347 to your computer and use it in GitHub Desktop.
Varchar to integer on the fly in Postgres
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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