Thread
-
[GENERAL] Making NULLs visible.
Stuart Rison <stuart@ludwig.ucl.ac.uk> — 1998-10-09T09:53:34Z
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 | +-------------------------+--------------------------------------+
-
Re: [GENERAL] Making NULLs visible.
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-10-09T16:36:54Z
> 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
-
Re: [GENERAL] Making NULLs visible.
Statistical Solutions <statsol@statsol.com> — 1998-10-09T19:03:48Z
> > 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
-
Re: [GENERAL] Making NULLs visible.
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-10-09T20:14:01Z
> > > 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
-
RE: [GENERAL] Making NULLs visible.
Taral <taral@mail.utexas.edu> — 1998-10-09T20:40:14Z
> 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
-
Re: [GENERAL] Making NULLs visible.
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-10-09T21:33:30Z
[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
-
RE: [GENERAL] Making NULLs visible.
Taral <taral@mail.utexas.edu> — 1998-10-09T21:39:41Z
> 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
-
Re: [GENERAL] Making NULLs visible.
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-10-09T21:42:36Z
[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
-
Re: [GENERAL] Making NULLs visible.
Herouth Maoz <herouth@oumail.openu.ac.il> — 1998-10-13T13:51:45Z
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
-
Re: [GENERAL] Making NULLs visible.
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-10-13T15:21:38Z
> 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