0

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  |           |          | 

Query1:

alter table employeeloans drop column salary1, add column salary1 bigint;

Query2:

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.

  • 1
    ANSI SQL does not permit multiple operations in an ALTER TABLE command. – a_horse_with_no_name Aug 31 '18 at 5:58
  • @a_horse_with_no_name Thank you, if that is the case, how PSQL handle these type of commands internally , I would like to see the particular algorithm which does that, can you pls suggest anything reg. this? – Dinesh Kumar Aug 31 '18 at 6:12
  • 1
    Sorry, I can't help you with that. The authoritative source for that would be the source code: doxygen.postgresql.org/… – a_horse_with_no_name Aug 31 '18 at 6:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.