Increasing the size of character varying type in postgres without data loss

I need to increase the size of a character varying(60) field in a postgres database table without data loss.

I have this command

alter table client_details alter column name set character varying(200);

will this command increase the the field size from 60 to 2开发者_Python百科00 without data loss?

The correct query to change the data type limit of the particular column:

ALTER TABLE client_details ALTER COLUMN name TYPE character varying(200);

Referring to this documentation, there would be no data loss, alter column only casts old data to new data so a cast between character data should be fine. But I don't think your syntax is correct, see the documentation I mentioned earlier. I think you should be using this syntax :

ALTER [ COLUMN ] column TYPE type [ USING expression ]

And as a note, wouldn't it be easier to just create a table, populate it and test :)

Yes. But it will rewrite this table and lock it exclusively for duration of rewriting — any query trying to access this table will wait until rewrite finishes.

Consider changing type to text and using check constraint for limiting size — changing constraint would not rewrite or lock a table.

you can use this below sql command
ALTER TABLE client_details ALTER COLUMN name TYPE varchar(200)

From PostgreSQL 9.2 Relase Notes E.15.3.4.2

Increasing the length limit for a varchar or varbit column, or removing the limit altogether, no longer requires a table rewrite.

Changing the Column Size in Postgresql 9.1 version

During the Column chainging the varchar size to higher values, table re write is required during this lock will be held on table and user table not able access till table re-write is done.

Table Name :- userdata Column Name:- acc_no

ALTER TABLE userdata ALTER COLUMN acc_no TYPE varchar(250);