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

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(at)pgadmin(dot)org> wrote:

> Hi
>
> On Friday, December 2, 2016, Akshay Joshi <akshay(dot)joshi(at)enterprisedb(dot)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-9517Mobile: +91 976-788-8246*