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.
- 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.comTwitter: @pgsnake
EnterpriseDB UK:
http://www.enterprisedb.comThe Enterprise PostgreSQL Company