Re: [pgadmin-hackers] [pgAdmin4][Patch]: Fix RM1790 - [Web] Supportsetting a field's value to "null"

On Fri, Dec 23, 2016 at 11:11 AM, Surinder Kumar <surinder.kumar@enterprisedb.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.kumar@enterprisedb.com> wrote:

On Mon, Dec 5, 2016 at 11:09 PM, Dave Page <dpage@pgadmin.org> wrote:

Hi

On Friday, December 2, 2016, Surinder Kumar <surinder.kumar@enterprisedb.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