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

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