Thread: [GENERAL] Making NULLs visible.

[GENERAL] Making NULLs visible.

From
Stuart Rison
Date:
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.

From
Bruce Momjian
Date:
> 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.

From
Steve Doliov
Date:
> > 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.

From
Bruce Momjian
Date:
> > > 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.

From
"Taral"
Date:
> 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.

From
Bruce Momjian
Date:
[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.

From
"Taral"
Date:
> 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.

From
Bruce Momjian
Date:
[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.

From
Steve Doliov
Date:
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



Re: [GENERAL] Making NULLs visible.

From
Thomas Good
Date:
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


Re: [GENERAL] Making NULLs visible.

From
Herouth Maoz
Date:
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.

From
Bruce Momjian
Date:
> 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