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

On Fri, Dec 23, 2016 at 11:11 AM, Surinder Kumar <
surinder(dot)kumar(at)enterprisedb(dot)com> wrote:

> Hi Dave,
>
> Please find updated patch.
>
> *Changes implemented:*
>
> 1) To enter an empty string in string/character type, user need to enter
> '' (two single quotes).
> 2) To enter null values in Integer/String type, user need to keep the
> field blank.
> 3) Null values will be represented as *[null]*.
>
> Please find attached patch and review.
>
> On Fri, Dec 9, 2016 at 2:23 PM, Surinder Kumar <
> surinder(dot)kumar(at)enterprisedb(dot)com> wrote:
>
>>
>>
>> On Mon, Dec 5, 2016 at 11:09 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>
>>> 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.
>>>
>> ​It seems possible by writing custom editor which will convert empty
>> string to null before save operation.​
>>
> ​Now If you set blank for integer field, field will set to null.
>
>>
>>> - 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]"
>>>
>> ​Ok. But the issue is if we display "[null]" in cell for null entry. How
>> would we distinguish If it is user entered string(as user can also enter
>> "[null]") or it represent null value ?​ (Ashesh's concern)
>>
> ​Now null values will be represented in field as [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 \‘\’
>>>
>> ​To write an empty string, now user can enter ''​ (two single quotes), it
> will be treated as empty string.
>
>> ====
>>>
>>> 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.
>>>
>> ​If a user enters a literal string \'\', this value is escaped by adding
> slashes on python side and unescaped by removing added slashes when
> returned to display.​
> the entered values are already escaped, user need not to escape values.
>
>> ​This behaviour seems to working wine in pgAdmin3 query tool but not on
>> viewing data by right click context menu.​​
>>
>
>> *​In view data:​*
>> ​When user enter literal strings like '', ​\'\'
>> ​ &
>> \\'\\', it displays these strings as it is after saving. It seems
>> conversion doesn't happen.
>> so which one is correct to follow in pgAdmin4?
>>
>>
>>>
>>> Thanks.
>>>
>>>
>>> --
>>> Dave Page
>>> Blog: http://pgsnake.blogspot.com
>>> Twitter: @pgsnake
>>>
>>> EnterpriseDB UK: http://www.enterprisedb.com
>>> The Enterprise PostgreSQL Company
>>>
>>>
>>
>