CODEWITHSUNDEEP
postgresql
Earlz
Earlz

Reputation: 63845

Getting Postgres to truncate values if necessary?

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.

Upvotes: 5

Views: 8357

Answers (4)

Travis
Travis

Reputation: 71

Easiest is just substring

INSERT INTO mytable (data) VALUES (substring('123' from 1 for 2));

Upvotes: 1

Tometzky
Tometzky

Reputation: 23890

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)

Upvotes: 1

Bridgier
Bridgier

Reputation: 1

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

Upvotes: 0

Brock Batsell
Brock Batsell

Reputation: 5803

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?

Upvotes: 8

Related Questions