Getting Postgres to truncate values if necessary?

Question

Hello, 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.

Answer 1

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?

Answer 2

You could change the datatype to varchar, and then use a trigger to enforce the two char constraint.

Answer 3

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)