I believe that Postgres, following the SQL standard, silently truncates longer strings when using varchar(n), which is probably not a desirable result if you're using it for validation.
> I believe that Postgres, following the SQL standard, silently truncates longer strings when using varchar(n)
AFAIK Postgres has always triggered an error in that case, though it was apparently not documented in 7.1 here is the documentation from 7.2:
> Both of these types can store strings up to n characters in length. An attempt to store a longer string into a column of these types will result in an error
What you may be referring to is the following note:
> unless the excess characters are all spaces, in which case the string will be truncated to the maximum length. (This somewhat bizarre exception is required by the SQL standard.)
So the SQL standard requires silently stripping (truncating spaces) from the end until the value fits or no more spaces can be truncated.
You might be thinking of older versions on MySQL. PostgreSQL never had this behavior of silently altering on write. It goes in the database as provided, or it raises an error. Period.
> PostgreSQL never had this behavior of silently altering on write. It goes in the database as provided, or it raises an error. Period.
That is not actually true but the GP might have gotten their wires crossed a bit between MySQL's behaviour of strait truncating and the SQL Standard's behaviour (which postgres implements) of truncating trailing spaces if they go beyond the specified length limit.
So per-spec "xxx" will fail to insert in a varchar(2), but "xx " will succeed returning just "xx" when fetching.
AFAIK Postgres has always triggered an error in that case, though it was apparently not documented in 7.1 here is the documentation from 7.2:
> Both of these types can store strings up to n characters in length. An attempt to store a longer string into a column of these types will result in an error
What you may be referring to is the following note:
> unless the excess characters are all spaces, in which case the string will be truncated to the maximum length. (This somewhat bizarre exception is required by the SQL standard.)
So the SQL standard requires silently stripping (truncating spaces) from the end until the value fits or no more spaces can be truncated.