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

Thanks, patch applied.

On Fri, Dec 9, 2016 at 6:37 AM, Akshay Joshi <akshay(dot)joshi(at)enterprisedb(dot)com>
wrote:

> 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-9517 <+91%2020%203058%209517>Mobile: +91 976-788-8246*
>

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

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