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