Saturday, April 18, 2009

Trimming a string column in PostgreSQL

In case this helps anyone... I had to figure it out recently and though it isn't hard, it isn't obvious either.

Here's how to truncate a string column in Postgres. Assume the column is of length M where M > N and we want it to be N with simple trim:

alter table T alter column C type varchar(N)
using substring(C from 1 for N);
The key is that C references both the column name (in the first instance) and the value (in the second instance).

No comments: