CODEWITHSUNDEEP
postgresqlvarchartruncate
William Jones
William Jones

Reputation: 18279

Postgres: Reduce varchar size and truncate

I currently have a Postgres 8.4 database that contains a varchar(10000) column. I'd like to change this into a varchar(255) and truncate any data that happens to be too long. How can I do this?

Upvotes: 9

Views: 6174

Answers (3)

mrjoltcola
mrjoltcola

Reputation: 20842

1) Update the column data using a substring method to truncate it

update t set col = substring(col from 1 for 255)

2) Then alter the table column

alter table t alter column col type varchar(255)

Docs here http://www.postgresql.org/docs/8.4/static/sql-altertable.html

Upvotes: 5

mkj
mkj

Reputation: 2821

Something like ALTER TABLE t ALTER COLUMN c TYPE VARCHAR(255) USING SUBSTR(c, 1, 255)

Upvotes: 18

Earlz
Earlz

Reputation: 63845

BEGIN;
UPDATE table SET column = CAST(column as varchar(255));
ALTER TABLE table ALTER COLUMN column TYPE varchar(255); --not sure on this line. my memory is a bit sketchy
COMMIT;

Upvotes: 1

Related Questions