If I create a table mytable with column data as varchar(2) and then insert something like '123' into the column, postgres will give me an error for Value too long for type.
How can I have Postgres ignore this and truncate the value if necessary?
Also, I do not (when creating the query) know the actual size of the data column in mytable so I can't just cast it.
According to the postgres documentation, you have to explicitly cast it to achieve this behavior, as returning an error is a requirement of the SQL standard. Is there no way to inspect the table's schema before creating the query to know what to cast it to?
If one explicitly casts a value to character varying(n) or character(n), then an over-length value will be truncated to n characters without raising an error. But I still get error e.g. value too long for type character varying(50)..Use text type with trigger instead:
create table mytable (
data text
);
create or replace function mytable_data_trunc_trigger()
returns trigger language plpgsql volatile as $$
begin
NEW.data = substring(NEW.data for 2);
return NEW;
end;
$$;
create trigger mytable_data_truncate_trigger
before insert or update on mytable for each row
execute procedure mytable_data_trunc_trigger();
insert into mytable values (NULL),('1'),('12'),('123');
select * from mytable;
data ------ 1 12 12 (4 rows)
Easiest is just substring
INSERT INTO mytable (data) VALUES (substring('123' from 1 for 2));
You could change the datatype to varchar, and then use a trigger to enforce the two char constraint.
