Re: [GENERAL] coalesce function
Chris Angelico wrote > On Fri, Jun 21, 2013 at 7:36 AM, David Johnston < > polobo@ > > wrote: >> SELECT input >> FROM ( SELECT unnest($1) AS input ) src >> WHERE input IS NOT NULL AND input <> '' >> LIMIT 1; > > Does this guarantee the order of the results returned? Using LIMIT > without ORDER BY is something I've learned to avoid. > > ChrisA I have thought about this and while I'm not 100% positive on the guarantee the fact the input data is small means the planner should not be re-ordering "src" in order to apply the where clause (if it would anyway...I think re-ordering may only happen during joins). To my knowledge the result of unnest returns in the same order as the array so "src" already has an implicit "ORDER BY" attached to it. It is only when return physical relation data is the order undefined. Arrays and "VALUES" both are returned in the order defined. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/coalesce-function-tp5760161p5760342.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] coalesce function
On Fri, Jun 21, 2013 at 7:36 AM, David Johnston wrote: > SELECT input > FROM ( SELECT unnest($1) AS input ) src > WHERE input IS NOT NULL AND input <> '' > LIMIT 1; Does this guarantee the order of the results returned? Using LIMIT without ORDER BY is something I've learned to avoid. ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] coalesce function
itishree sukla wrote > Hi All, > > I am using coalesce(firstname,lastname), to get the result if first name > is > 'NULL' it will give me lastname or either way. I am having data like > instead of NULL, blank null ( i mean something like '' ) for which > coalesce is not working, is there any workaround or other function > available in postgresql, please do let me know. > > > Regards, > Itishree This is the solution I am currently using in my work: Runs in 9.0 CREATE OR REPLACE FUNCTION coalesce_emptystring(VARIADIC in_ordered_actual varchar[]) RETURNS varchar AS $$ SELECT input FROM ( SELECT unnest($1) AS input ) src WHERE input IS NOT NULL AND input <> '' LIMIT 1; $$ LANGUAGE sql STABLE ; Same usage syntax as the built-in COALESCE but skips NULL and the empty-string. Note a string with only whitespace (i.e., ' ') is not considered empty. The problem with the "CASE" example provided is that while it works in the specific case you are solving it does not readily generalize to more than 2 inputs. Are you positive the "lastname" will always have a value? You should consider a last-resort default to ensure that the column never returns a NULL. coalesce_emptystring(firstname, lastname, 'Name Unknown') -- View this message in context: http://postgresql.1045698.n5.nabble.com/coalesce-function-tp5760161p5760205.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] coalesce function
Hi, http://www.postgresql.org/docs/9.1/static/functions-conditional.html describes NULLIF, when combined with COALESCE it should answer your request. HTH Kind regards/met vriendelijke groet, Serge Fonville http://www.sergefonville.nl Convince Microsoft! They need to add TRUNCATE PARTITION in SQL Server https://connect.microsoft.com/SQLServer/feedback/details/417926/truncate-partition-of-partitioned-table 2013/6/20 itishree sukla > Hi All, > > I am using coalesce(firstname,lastname), to get the result if first name > is 'NULL' it will give me lastname or either way. I am having data like > instead of NULL, blank null ( i mean something like '' ) for which > coalesce is not working, is there any workaround or other function > available in postgresql, please do let me know. > > > Regards, > Itishree >
Re: [GENERAL] coalesce function
Torsdag 20. juni 2013 21.45.02 skrev itishree sukla: > Hi All, > > I am using coalesce(firstname,lastname), to get the result if first name is > 'NULL' it will give me lastname or either way. I am having data like > instead of NULL, blank null ( i mean something like '' ) for which > coalesce is not working, is there any workaround or other function > available in postgresql, please do let me know. CASE WHEN firstname NOT IN (NULL, '') THEN firstname ELSE lastname END; regards, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] coalesce function
Hi All, I am using coalesce(firstname,lastname), to get the result if first name is 'NULL' it will give me lastname or either way. I am having data like instead of NULL, blank null ( i mean something like '' ) for which coalesce is not working, is there any workaround or other function available in postgresql, please do let me know. Regards, Itishree
Re: [GENERAL] COALESCE function
Based on the below each row could end up returning a different data type compared to a previous row for that column. SELECT COALESCE( CAST(f.number as varchar(100)) , f.name) FROM Whatever f.name is set to in terms of the max length of varchar, if any, is what f.number should be cast to. mike On Sun, 2006-12-31 at 00:44 -0500, Kevin Hunter wrote: > Hello All, > > Attempting to select two different column types with COALESCE returns > this error: > > ERROR: COALESCE types smallint and character varying cannot be matched > > Attempting the same thing with a CASE statement returns a similar error: > > ERROR: CASE types smallint and character varying cannot be matched > > I also checked Oracle's NVL command, and it throws a similar error. > Clearly, I'm not supposed to intermix two different column types into a > SELECT statement. This is because the engine needs to return a set > given the criteria, and it's difficult to do that with criteria that > /depends on the data/, yes? > > Could someone explain a/the more formal reason why I can't do what I'm > trying to do? > > The relevant part of my SELECT statement: > > SELECT >..., >COALESCE(f.number, f.name), >... > FROM >..., >field AS f, >... > WHERE > ... > ; > > f.number ∈ SMALLINT > f.name ∈ CHARACTER VARYING > > Thank you in advance! > > Kevin > > P.S. If something gets lost in bit/encoding translation, ∈ = "Element Of" > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] COALESCE function
Hello All, Attempting to select two different column types with COALESCE returns this error: ERROR: COALESCE types smallint and character varying cannot be matched Attempting the same thing with a CASE statement returns a similar error: ERROR: CASE types smallint and character varying cannot be matched I also checked Oracle's NVL command, and it throws a similar error. Clearly, I'm not supposed to intermix two different column types into a SELECT statement. This is because the engine needs to return a set given the criteria, and it's difficult to do that with criteria that /depends on the data/, yes? Could someone explain a/the more formal reason why I can't do what I'm trying to do? The relevant part of my SELECT statement: SELECT ..., COALESCE(f.number, f.name), ... FROM ..., field AS f, ... WHERE ... ; f.number ∈ SMALLINT f.name ∈ CHARACTER VARYING Thank you in advance! Kevin P.S. If something gets lost in bit/encoding translation, ∈ = "Element Of" ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
