Thread: [GENERAL] Making NULLs visible.
Dear All,
Is there a way to make postgreSQL 'show' nulls rather than have them appear
as blank fields?
e.g.
create table test (
code int2 primary key,
name text
);
insert into test (code,name) values (1234,'one two three four');
insert into test (code) values (5678);
insert into test (code,name) values (8888,NULL);
insert into test (code,name) values (9999,'');
now...
brecard5=> select * from test where name is null;
code|name
----+----
5678|
8888|
(2 rows)
brecard5=> select * from test;
code|name
----+------------------
1234|one two three four
5678|
9999|
8888|
(4 rows)
Question 1) How do I get postgres to do this:
select * from test;
code|name
----+------------------
1234|one two three four
5678|NULL
9999|
8888|NULL
?????
also, if I use the extended output option:
brecard5=> \x
turned on expanded table representation
brecard5=> select * from test where name is null;
Field| Value
-- RECORD 0 --
code| 5678
-- RECORD 1 --
code| 8888
(2 rows)
brecard5=> select * from test;
Field| Value
-- RECORD 0 --
code| 1234
name| one two three four
-- RECORD 1 --
code| 5678
-- RECORD 2 --
code| 9999
-- RECORD 3 --
code| 8888
(4 rows)
Again, how do I get visible NULLs?
Finally, as a somewhat unrelated set of questions:
2) In the extended output format, why is it that with 'select * from test',
RECORD 2 (code = 9999), the field name is not shown? Afterall, the field
is not null, just empty (which I know shouldn't really happen in a well
designed db but...)?
3) How do I get the extended output to list ALL fields, irrespective of
whether they contain a value or are NULL?
thanks for your help,
Stuart.
+-------------------------+--------------------------------------+
| Stuart Rison | Ludwig Institute for Cancer Research |
+-------------------------+ 91 Riding House Street |
| Tel. (0171) 878 4041 | London, W1P 8BT, UNITED KINGDOM. |
| Fax. (0171) 878 4040 | stuart@ludwig.ucl.ac.uk |
+-------------------------+--------------------------------------+
> Dear All, > > Is there a way to make postgreSQL 'show' nulls rather than have them appear > as blank fields? > > code|name > ----+---- > 5678| > 8888| > (2 rows) > > brecard5=> select * from test; > code|name > ----+------------------ > 1234|one two three four > 5678| > 9999| > 8888| > (4 rows) I am always looking for suggestions on how to display nulls. It is on the TODO list. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> > Is there a way to make postgreSQL 'show' nulls rather than have them appear > > as blank fields? > I am always looking for suggestions on how to display nulls. It is on > the TODO list. I'd suggest a query option. The query option could filter the displayed data through a temporary table which would do a NULL to char() conversion as it were. similar to psql-> set query option id null as "NULL" then the interface would do the following if given a query query passed to psql select a, b, from mydata; psql would interpret as create table temp (a text, b text); and then run a query to populate temp casting the values of a b from mydata as necessary and taking null values and setting them to what the user specified (a good choice would be "NULL" of course). steve
> > > Is there a way to make postgreSQL 'show' nulls rather than have them appear > > > as blank fields? > > > I am always looking for suggestions on how to display nulls. It is on > > the TODO list. > > I'd suggest a query option. The query option could filter the displayed > data through a temporary table which would do a NULL to char() conversion > as it were. > > similar to > psql-> set query option id null as "NULL" > > then the interface would do the following if given a query > How do you feel about displaying nulls as \N, as we do in the COPY output? -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> How do you feel about displaying nulls as \N, as we do in the COPY > output? And when I put \N in a text field? How do I distinguish? We need some kind of escaping system here... like turn '\' into '\\' and then '\N' is unique and non-textual. :) Also allows us to output control characters, etc., in a manner that is completely parsable. Taral
[Charset iso-8859-1 unsupported, filtering to ASCII...] > > How do you feel about displaying nulls as \N, as we do in the COPY > > output? > > And when I put \N in a text field? How do I distinguish? We need some kind > of escaping system here... like turn '\' into '\\' and then '\N' is unique > and non-textual. :) Also allows us to output control characters, etc., in a > manner that is completely parsable. Yes, \ always outputs as \\, excepts someone changed it last week, and I am requesting a reversal. Do you like the \N if it is unique? -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> Yes, \ always outputs as \\, excepts someone changed it last week, and I > am requesting a reversal. Do you like the \N if it is unique? Well, it's certainly clear, but could be confused with \n (newline). Can we have \0 instead? Taral
[Charset iso-8859-1 unsupported, filtering to ASCII...] > > Yes, \ always outputs as \\, excepts someone changed it last week, and I > > am requesting a reversal. Do you like the \N if it is unique? > > Well, it's certainly clear, but could be confused with \n (newline). Can we > have \0 instead? Yes, but it is uppercase. \0 looks like an octal number to me, and I think we even output octals sometimes, don't we? -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Fri, 9 Oct 1998, Bruce Momjian wrote: > [Charset iso-8859-1 unsupported, filtering to ASCII...] > > > Yes, \ always outputs as \\, excepts someone changed it last week, and I > > > am requesting a reversal. Do you like the \N if it is unique? > > > > Well, it's certainly clear, but could be confused with \n (newline). Can we > > have \0 instead? > > Yes, but it is uppercase. \0 looks like an octal number to me, and I > think we even output octals sometimes, don't we? > my first suggestion may have been hare-brained, but why not just make the specifics of the output user-configurable. So if the user chooses \0, so be it, if the user chooses \N so be it, if the user likes NULL so be it. but the option would only have one value per database at any given point in time. so database x could use \N on tuesday and NULL on wednesday, but database x could never have two references to the characters(s) used to represent a null value. steve
Watching all this go by...as a guy who has to move alot of data
from legacy dbs to postgres, I've gotten used to \N being a null.
My vote, if I were allowed to cast one, would be to have one null
and that would be the COPY command null. I have no difficulty
distinguishing a null from a newline...
At the pgsql command prompt I would find seeing \N rather reassuring.
I've seen alot of these little guys.
---------- Sisters of Charity Medical Center ----------
Department of Psychiatry
----
Thomas Good <tomg@q8.nrnet.org>
Coordinator, North Richmond C.M.H.C. Information Systems
75 Vanderbilt Ave, Quarters 8 Phone: 718-354-5528
Staten Island, NY 10304 Fax: 718-354-5056
At 18:36 +0200 on 9/10/98, Bruce Momjian wrote: > I am always looking for suggestions on how to display nulls. It is on > the TODO list. Here is my suggestion, admittedly a bit cheeky: Implement an NVL function - one that dictates a NULL in a field and converts it to whatever value is required. Then, anybody who wants psql to display a null function can decide what is to be displayed on a per-query basis: SELECT a, nvl(b,'I am null!'), c FROM tab WHERE... Will give something like: a | ?column? | c ====|============|===== 10 | Moses | 100 20 | Solomon | 200 30 | I am null! | 300 40 | David | 400 50 | I am null! | 500 And so on... So, if someone wants \N, he'll put '\\N' in the NVL function, and if they want NULL, they'll put 'NULL', etc. NVL will be usefull for lots of stuff... Not only for this... Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
> At 18:36 +0200 on 9/10/98, Bruce Momjian wrote: > > > > I am always looking for suggestions on how to display nulls. It is on > > the TODO list. > > Here is my suggestion, admittedly a bit cheeky: > > Implement an NVL function - one that dictates a NULL in a field and > converts it to whatever value is required. > > Then, anybody who wants psql to display a null function can decide what is > to be displayed on a per-query basis: > > SELECT a, nvl(b,'I am null!'), c > FROM tab > WHERE... > > Will give something like: > > a | ?column? | c > ====|============|===== > 10 | Moses | 100 > 20 | Solomon | 200 > 30 | I am null! | 300 > 40 | David | 400 > 50 | I am null! | 500 > > And so on... So, if someone wants \N, he'll put '\\N' in the NVL function, > and if they want NULL, they'll put 'NULL', etc. > > NVL will be usefull for lots of stuff... Not only for this... Yes, this is a nice feature. Ingres has it as isnull(val, 'string'), as you demonstrated. I will add it to the TODO list. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026