Re: [GENERAL] In a view, how do I cause fields with possible NULLs to be treated as a blank string in a replace operation?
On Mon, Jun 27, 2011 at 5:58 PM, Thom Brown t...@linux.com wrote: Try coalesce: http://www.postgresql.org/docs/9.0/static/functions-conditional.html#AEN15541 So if foo is a null value, and you used COALESCE(foo, 'bar'), the output would be 'bar', otherwise it would be whatever the value of foo is. On Mon, Jun 27, 2011 at 5:59 PM, Rick Genter rick.gen...@gmail.com wrote: Either use '' as some_type, or use COALESCE(some_type, ''). Thank you both, that worked fine. -- Regards, Asfand Qazi Team 87 HTGT Wellcome Trust Sanger Institute -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] In a view, how do I cause fields with possible NULLs to be treated as a blank string in a replace operation?
Hello, So I have am playing with a view to test the feasibility of a technique for storing some data. It basically goes something like this: CREATE VIEW formatted_table AS SELECT name, replace(some_template, '@', some_type) AS some_field FROM some_table; some_template is something like 'foo@bar' or 'foobar' (note the missing template character). some_type is a single letter like 'a' or 'b', or it can be NULL. The above view works fine for rows where some_type is a letter, and some_field ends up as 'fooabar' or whatever. However, when some_type is NULL, some_field ends up as NULL as well. I understand that this is expected behaviour, but how do I cause the view to treat a some_type of NULL as an empty string, so that some_field simply ends up as 'foobar'? Hope that was clear. -- 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] In a view, how do I cause fields with possible NULLs to be treated as a blank string in a replace operation?
On 27 June 2011 17:53, Asfand Qazi (Sanger Institute) aq2.san...@gmail.com wrote: Hello, So I have am playing with a view to test the feasibility of a technique for storing some data. It basically goes something like this: CREATE VIEW formatted_table AS SELECT name, replace(some_template, '@', some_type) AS some_field FROM some_table; some_template is something like 'foo@bar' or 'foobar' (note the missing template character). some_type is a single letter like 'a' or 'b', or it can be NULL. The above view works fine for rows where some_type is a letter, and some_field ends up as 'fooabar' or whatever. However, when some_type is NULL, some_field ends up as NULL as well. I understand that this is expected behaviour, but how do I cause the view to treat a some_type of NULL as an empty string, so that some_field simply ends up as 'foobar'? Hope that was clear. Try coalesce: http://www.postgresql.org/docs/9.0/static/functions-conditional.html#AEN15541 So if foo is a null value, and you used COALESCE(foo, 'bar'), the output would be 'bar', otherwise it would be whatever the value of foo is. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] In a view, how do I cause fields with possible NULLs to be treated as a blank string in a replace operation?
Either use '' as some_type, or use COALESCE(some_type, ''). On Mon, Jun 27, 2011 at 9:53 AM, Asfand Qazi (Sanger Institute) aq2.san...@gmail.com wrote: Hello, So I have am playing with a view to test the feasibility of a technique for storing some data. It basically goes something like this: CREATE VIEW formatted_table AS SELECT name, replace(some_template, '@', some_type) AS some_field FROM some_table; some_template is something like 'foo@bar' or 'foobar' (note the missing template character). some_type is a single letter like 'a' or 'b', or it can be NULL. The above view works fine for rows where some_type is a letter, and some_field ends up as 'fooabar' or whatever. However, when some_type is NULL, some_field ends up as NULL as well. I understand that this is expected behaviour, but how do I cause the view to treat a some_type of NULL as an empty string, so that some_field simply ends up as 'foobar'? Hope that was clear. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Rick Genter rick.gen...@gmail.com
