Thread: what is the best way to concat fields that may contain null as if they were empty strings
what is the best way to concat fields that may contain null as if they were empty strings
From
Reid Thompson
Date:
In the case where a_text is null, I essentially want the same result as the case when a_text = ''.
would this:
select a_int || coalesce(a_text,'') from test1 where a_int = 1000002;
be the proper way?
postgres=# \d test1
Table "public.test1"
Column | Type | Modifiers
--------+-----------------------------+-------------------------------------------------------
a_int | integer | not null default nextval('test1_a_int_seq'::regclass)
a_text | character varying(200) |
dt | timestamp without time zone | default now()
Indexes:
"test1_pkey" PRIMARY KEY, btree (a_int)
postgres=# select count(*) from test1;
count
---------
1000000
(1 row)
postgres=# insert into test1(a_text) values('');
INSERT 0 1
postgres=# select max(a_int) from test1;
max
---------
1000001
(1 row)
postgres=# select a_int || a_text from test1 where a_int = 1000001;
?column?
----------
1000001
(1 row)
postgres=# insert into test1(a_text) values(null);
INSERT 0 1
postgres=# select a_int || a_text from test1 where a_int = 1000002;
?column?
----------
(1 row)
postgres=# select * from test1 where a_int >= 1000001;
a_int | a_text | dt
---------+--------+----------------------------
1000001 | | 2009-10-09 11:54:38.455556
1000002 | | 2009-10-09 11:56:00.37607
(2 rows)
Re: what is the best way to concat fields that may contain null as if they were empty strings
From
Andreas Kretschmer
Date:
Reid Thompson <reid.thompson@ateb.com> wrote: > In the case where a_text is null, I essentially want the same result as the case when a_text = ''. > > would this: > select a_int || coalesce(a_text,'') from test1 where a_int = 1000002; > be the proper way? Yes. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°