Error in changing column length in Postgres

Question

I am trying to change the column size from 100 to 150 varchar data type using following query:

alter table data_warehouse.tbl_abc
alter column first_nm varchar(150) null;

Getting the following error:

SQL Error [42601]: ERROR: syntax error at or near "varchar" Position: 77

Answer 1

The syntax is a bit different, so try this:

ALTER TABLE data_warehouse.tbl_abc
    ALTER COLUMN first_nm type varchar(120);

Answer 2

The error in your syntax is that you missed a TYPE keyword:

ALTER TABLE data_warehouse.tbl_abc
ALTER COLUMN first_nm TYPE varchar(150);

and if you have a NOT NULL constraint you want to remove, add a new ALTER COLUMN inside the same ALTER TABLE statement:

ALTER TABLE data_warehouse.tbl_abc
ALTER COLUMN first_nm TYPE varchar(150),
ALTER COLUMN first_nm DROP NOT NULL;

for reference look here: https://www.postgresql.org/docs/current/sql-altertable.html

Edit: as in the comment, if you have a view which involves the same column, drop it and re-create it under transaction:

BEGIN TRANSACTION;
DROP VIEW [...];
ALTER TABLE [...];
CREATE VIEW [...];
COMMIT;

Be aware that to alter a table, you must acquire an exclusive lock on it, so during the whole process, all the queries over the same table and on the views of the table are locked, also if they don't read from the altered column (because the whole table is locked) - use with caution in production environment