0

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

Joe Taras
  • 15,166
  • 7
  • 42
  • 55
Abhinav Agrawal
  • 13
  • 4

2 Answers2

1

The syntax is a bit different, so try this:

ALTER TABLE data_warehouse.tbl_abc
    ALTER COLUMN first_nm type varchar(120);
Joe Taras
  • 15,166
  • 7
  • 42
  • 55
0

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

Marco Carlo Moriggi
  • 324
  • 4
  • 11
  • If this column is being used by a view, is it possible to do so? – Abhinav Agrawal Nov 06 '20 at 08:52
  • No... in this case you need to drop and re-create the view (for simple views) or altering materialized view (for materialized view) before. Do this under transaction to keep the view usable: `BEGIN TRANSACTION; DROP VIEW [...]; ALTER TABLE [...]; CREATE VIEW [...]; COMMIT;`. See http://www.expertphp.in/article/postgresql-how-to-alter-type-of-a-column-used-by-a-view-or-rule – Marco Carlo Moriggi Nov 06 '20 at 09:05