I was just experimenting with ALTER TABLE commands in psql. I came across a fact that we can give multiple alter clause within same ALTER TABLE command. Like
ALTER TABLE EmployeeLoans add col1, add col2, drop col0,...
That it optimise row re-writes especially when we are adding multiple columns with default values.
I tried a query on the following table:
Table "db145004db.employeeloans" Column | Type | Collation | Nullable | Default ------------+---------+-----------+----------+--------- empid | integer | | not null | empname | citext | | | empsurname | citext | | | salary1 | bigint | | |
alter table employeeloans drop column salary1, add column salary1 bigint;
alter table employeeloans add column salary1 bigint, drop column salary1;
I was expecting that the second query should throw an error, that the column 'salary' already exists. But, postgres doesn't.
Similiarly, while adding unique-constraint on a column that is going to be dropped
Query3: alter table employeeloans add constraint salary1_unique unique(salary1), drop column salary1; ERROR: column "salary1" named in key does not exist
By seeing this error, I interpret that column salary1 is dropped before creating the unique key;
So,I was wondering what we will be the execution plan/order while giving multiple alters in same SQL command. Is this behaviour compatible with ANSI standard (if any). Any detailed (referring postgres source code) or "explain me like i'm 5" answers are welcomed.