When using the database postgresql, sometimes you will encounter the situation of field length expansion. Since there is already data, you can only modify the field length, and you cannot delete it and then add it.
It can be done using the following method
ALTER TABLE your_table_name alter COLUMN your_column_name type character varying(3000);
Through the above sentence, you can change the length of the corresponding field in your table to 3000.
Supplement: Performance of PostgreSQL character type length change
background
Businesses sometimes encounter the problem that the length of character fields in the table is not enough, and they need to modify the table definition. However, there is already a lot of data in the table. Will modifying the field length cause application blockage?
Test verification
A small test was made, as follows
Create a table and insert 1000w of data
postgres=# create table tbx1(id int,c1 char(10),c2 varchar(10)); CREATE TABLE postgres=# insert into tbx1 select id ,'aaaaa','aaaaa' from generate_series(1,10000000) id; INSERT 0 10000000
Change the varchar type length
postgres=# alter table tbx1 alter COLUMN c2 type varchar(100); ALTER TABLE Time: 1.873 ms postgres=# alter table tbx1 alter COLUMN c2 type varchar(99); ALTER TABLE Time: 12815.678 ms postgres=# alter table tbx1 alter COLUMN c2 type varchar(4); ERROR: value too long for type character varying(4) Time: 5.328 ms
Change the char type length
postgres=# alter table tbx1 alter COLUMN c1 type char(100); ALTER TABLE Time: 35429.282 ms postgres=# alter table tbx1 alter COLUMN c1 type char(6); ALTER TABLE Time: 20004.198 ms postgres=# alter table tbx1 alter COLUMN c1 type char(4); ERROR: value too long for type character(4) Time: 4.671 ms
Change the char type, varchar and text type to convert each other
alter table tbx1 alter COLUMN c1 type varchar(6); ALTER TABLE Time: 18880.369 ms postgres=# alter table tbx1 alter COLUMN c1 type text; ALTER TABLE Time: 12.691 ms postgres=# alter table tbx1 alter COLUMN c1 type varchar(20); ALTER TABLE Time: 32846.016 ms postgres=# alter table tbx1 alter COLUMN c1 type char(20); ALTER TABLE Time: 39796.784 ms postgres=# alter table tbx1 alter COLUMN c1 type text; ALTER TABLE Time: 32091.025 ms postgres=# alter table tbx1 alter COLUMN c1 type char(20); ALTER TABLE Time: 26031.344 ms
Define changed data
After the definition is changed, the data location has not changed, that is, no new tuple is generated
postgres=# select ctid,id from tbx1 limit 5; ctid | id -------+---- (0,1) | 1 (0,2) | 2 (0,3) | 3 (0,4) | 4 (0,5) | 5 (5 rows)
Except for varchar expansion, each tuple generates a WAL record.
$ pg_xlogdump -f -s 3/BE002088 -n 5 rmgr: Heap len (rec/tot): 3/ 181, tx: 1733, lsn: 3/BE002088, prev 3/BE001FB8, desc: INSERT off 38, blkref #0: rel 1663/13269/16823 blk 58358 rmgr: Heap len (rec/tot): 3/ 181, tx: 1733, lsn: 3/BE002140, prev 3/BE002088, desc: INSERT off 39, blkref #0: rel 1663/13269/16823 blk 58358 rmgr: Heap len (rec/tot): 3/ 181, tx: 1733, lsn: 3/BE0021F8, prev 3/BE002140, desc: INSERT off 40, blkref #0: rel 1663/13269/16823 blk 58358 rmgr: Heap len (rec/tot): 3/ 181, tx: 1733, lsn: 3/BE0022B0, prev 3/BE0021F8, desc: INSERT off 41, blkref #0: rel 1663/13269/16823 blk 58358 rmgr: Heap len (rec/tot): 3/ 181, tx: 1733, lsn: 3/BE002368, prev 3/BE0022B0, desc: INSERT off 42, blkref #0: rel 1663/13269/16823 blk 58358
in conclusion
Varchar expands capacity, and varchar transfers to text only need to modify the metadata and completes in milliseconds.
The time required for other conversions is related to the amount of data. 1000w of data is 10~40 seconds, but the data file is not changed, just a check is done.
If the definition length is not enough to accommodate existing data during shrinking, the error is reported.
It is not recommended to use the char type, except for burying pits, this item is almost useless. This item is not only applicable to PG, but all relational databases should be applicable.
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.