Re: [pgAdmin4][Patch]: Fix RM1790 - [Web] Support setting a field's value to "null"

Hi

On Friday, December 2, 2016, Surinder Kumar <surinder(dot)kumar(at)enterprisedb(dot)com>
wrote:

> Hi
>
> *Issue:*
> - On viewing table data, If we edit a column and set value of column(type:
> text) to "null", It always takes it as empty string. It doesn't honour null
> values.
>
> Solution:
> - Take a flag "*is_null*" for columns with data type 'text', then on GUI,
> whilst user edits a text field, an additional option with checkbox(is_null
> ?) is given to take null values. If checkbox is checked, on JS side we
> check "*is_null*" flag and pass field value to null if selected.
>
> Please find patch and review.
>

A nice solution, but there are some problems I think;

- How do I set a field that doesn't use the text editor to null? e.g. an
integer? If I try to set one to blank, I get an error that it's invalid
input syntax for an integer.

- When null values are first displayed, they are shown as blank entries. If
I then set one to null, it displays "null". It should always display
consistently - I'd suggest "[null]"

Whilst I like the way this works in part, I think it's going to be
inconsistent in the way it would be displayed. I think we need to follow
the pgAdmin III way of handling this. The docs say the following:

====
If an SQL NULL is to be written to the table, simply leave the field empty.
If you store a new row, this will let the server fill in the default value
for that column. If you store a change to an existing row, the value NULL
will explicitly be written.

...

If you want pgAdmin III to write an empty string to the table, you enter
the special string ‘’ (two single quotes) in the field. If you want to
write a string containing solely two single quotes to the table, you need
to escape these quotes, by typing \‘\’
====

In other words, if an empty value is included for a new row, that column
will be omitted from the INSERT statement, allowing the server to use a
default, or set it to blank.

For existing rows, an empty value for any data type is updated as NULL -
e.g. col = NULL.

For character/string types, if the user enters '', then an empty string is
written to the column when either inserting or updating.

If the user wishes to insert the literal string '' (i.e. 2 single quotes),
then \'\' must be entered, and pgAdmin converts that to ''.

To enter a literal string of \'\', then the user enters \\'\\', for \\'\\'
they enter \\\\'\\\\' and so on.

Thanks.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company