On 27 June 2011 17:53, Asfand Qazi (Sanger Institute) <aq2.sanger@xxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
