Thread: [pgAdmin4][Patch]: RM #1994 Insert / update are truncating if column is character

Hi

On Friday, December 2, 2016, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

Hi All

Please find the attached patch to fix the RM #1994 Insert / update are truncating if column is character. 

Issue: Create any column with datatype as character and provide some length (10), now view the data in pgAdmin4 and try to insert/update that column it gets truncated to one character. In insert/update query we have type casted the value with data type like "col_val::character" but not provided the length while type casting it, so it is truncated to one character. 

Solution: We can solve this in many ways(server side, client side), but for server side it requires too much of code changes. So instead of doing that I have fixed this at client side in "sqleditor.js" file from where we send the modified/inserted data to the server. Following is the fix:

If datatype is "character" or "character varying" then check for it's internal_size(length) and update the data type string as well as column label to be shown on column headers.

After this fix if user inserts more character than the specified length it will be truncated to that length and saved it into the database.    

Please review it.

 The problem with that approach is that casting the data will cause strings that are excessively long to be silently truncated - for example;

Manually:

postgres=# insert into char_table (char1, char10) values ('abc', 'abcdefghi');

ERROR:  value too long for type character(1)

In the editor:

2016-12-05 13:23:57,443: SQL pgadmin: Execute (void) for server #1 - CONN:7981668 (Query-id: 5028718):

INSERT INTO public.char_table (

char1, char10) VALUES (

'abc'::character(1), 'abcdefghi'::character(10));

2016-12-05 13:23:57,444: SQL pgadmin: Execute (void) for server #1 - CONN:7981668 (Query-id: 5003297):

COMMIT;

We need to avoid casting the data as it will silently truncate data and prevent the user seeing the error they should see.

Thanks.

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

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

Hi Dave

I have remove the type casting while inserting/updating data. Attached is the patch file to fixe the issue, I have tested this patch with following data types:

"serial, integer, interger[], numeric, numeric[], bit, character, character[], character varying, character varying[], jsonb, timestamp with timezone, boolean"

Please review it.   

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

Hi

On Friday, December 2, 2016, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

Hi All

Please find the attached patch to fix the RM #1994 Insert / update are truncating if column is character. 

Issue: Create any column with datatype as character and provide some length (10), now view the data in pgAdmin4 and try to insert/update that column it gets truncated to one character. In insert/update query we have type casted the value with data type like "col_val::character" but not provided the length while type casting it, so it is truncated to one character. 

Solution: We can solve this in many ways(server side, client side), but for server side it requires too much of code changes. So instead of doing that I have fixed this at client side in "sqleditor.js" file from where we send the modified/inserted data to the server. Following is the fix:

If datatype is "character" or "character varying" then check for it's internal_size(length) and update the data type string as well as column label to be shown on column headers.

After this fix if user inserts more character than the specified length it will be truncated to that length and saved it into the database.    

Please review it.

 The problem with that approach is that casting the data will cause strings that are excessively long to be silently truncated - for example;

Manually:

postgres=# insert into char_table (char1, char10) values ('abc', 'abcdefghi');

ERROR:  value too long for type character(1)

In the editor:

2016-12-05 13:23:57,443: SQL pgadmin: Execute (void) for server #1 - CONN:7981668 (Query-id: 5028718):

INSERT INTO public.char_table (

char1, char10) VALUES (

'abc'::character(1), 'abcdefghi'::character(10));

2016-12-05 13:23:57,444: SQL pgadmin: Execute (void) for server #1 - CONN:7981668 (Query-id: 5003297):

COMMIT;

We need to avoid casting the data as it will silently truncate data and prevent the user seeing the error they should see.

Thanks.

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

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

--

Akshay Joshi

Principal Software Engineer 

Phone: +91 20-3058-9517
Mobile: +91 976-788-8246