Обсуждение: Show NULL values AS not working

Hi Dave and team,<br /><br />Another slight bug (I think):<br /><br />I have a table which has a column called
"first_invalid"which should be a timestamp if the record is invalid, or NULL if it's valid.<br />In PgAdmin I have the
"ShowNULL values as <NULL>" option enabled, but it still displays a blank cell for this column. <br /><br />The
reasonwhy I think it may not be a bug, is I tried doing this:<br /><br />"SELECT COALESCE(first_invalid,
'<NULL>')AS first_invalid FROM table"<br /><br />but PostgreSQL threw it out because "<NULL>" is not a
validtimestamp - instead I had to do: <br /><br />"SELECT COALESCE(first_invalid, '1900-01-01') AS first_invalid FROM
table"<br/><br />to prove the column is in fact NULL, and I do get: "1900-01-01 00:00:00+00" for this record, but in
thedata entry grid, PgAdmin still displays it blank. <br /><br />Question: is this a bug, or intended behaviour?<br
/><br/>Thanks,<br /><br />Andy<br /> 

Hi guys,

A bit more info.

If I write a query manually (i.e. SELECT * FROM table) then the column shows up as "<NULL>" which is what I expect.
However, if I write click the table and hit the "View Data" menu option, the column is blank.

Andy.

One more thing. If the value is the empty string, then in the View Data shows it as ''

Hi Andy

On 14/01/2008, Andy Shellam <andy.shellam-lists@mailnetwork.co.uk> wrote:
> Hi Dave and team,
>
> Another slight bug (I think):
>
> I have a table which has a column called "first_invalid" which should be a
> timestamp if the record is invalid, or NULL if it's valid.
> In PgAdmin I have the "Show NULL values as <NULL>" option enabled, but it
> still displays a blank cell for this column.
>
> The reason why I think it may not be a bug, is I tried doing this:
>
> "SELECT COALESCE(first_invalid, '<NULL>') AS first_invalid FROM table"
>
> but PostgreSQL threw it out because "<NULL>" is not a valid timestamp -

Right - thats what I'd expect.

> instead I had to do:
>
> "SELECT COALESCE(first_invalid, '1900-01-01') AS first_invalid FROM table"
>
> to prove the column is in fact NULL, and I do get: "1900-01-01 00:00:00+00"
> for this record, but in the data entry grid, PgAdmin still displays it
> blank.

That doesn't prove it's null - it proves it's null OR 1900-01-01
00:00:00+00. However, the <NULL> output option is onlt for the Query
Tool. The data entry grid always displays an empty cell for NULL, ''
for a blank string, and \'\' for a pair or single quotes. From the
docs:

Show NULL values as <NULL>? - This option will cause NULL values to be
shown as <NULL> in the Query Tool's results grid.

http://www.pgadmin.org/docs/1.8/options-tab3.html

Regards, Dave.

Hi Dave

Ah right OK, I didn't realise the "Query" tab was just for SQL editor, I thought it was for the data entry grids as well.

Thanks for clearing that up.


Andy

On 14/01/2008, Andy Shellam <andy.shellam-lists@mailnetwork.co.uk> wrote:
> > That doesn't prove it's null - it proves it's null OR 1900-01-01
> > 00:00:00+00. However, the <NULL> output option is onlt for the Query
> > Tool. The data entry grid always displays an empty cell for NULL, ''
> > for a blank string, and \'\' for a pair or single quotes. From the
> > docs:
>
>
> I knew it was NULL cause the table only had 1 row in it, and I'd purposely
> set it to NULL through a query ;-)

My point is still valid though, and as you didn't mention those
details earlier... :-)

Regards, Dave