Presto: Support ALTER TABLE with [IF EXISTS]

[kokosing]

Having [IF EXISTS] for ALTER statement makes it very convenient for ETL scripts.

See how it is implemented in postgres: https://www.postgresql.org/docs/current/sql-altertable.html

Additionally Postgres defines:

    ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type ...
    DROP [ COLUMN ] [ IF EXISTS ] column_name ...

This issue was mentioned here: https://stackoverflow.com/q/59266938/217852

[KevinKobi]

@ankitdixit
is there any progress with this enhancement?

[ankitdixit]

@KevinKobi let me try and raise a PR.

[findepi]

@ankitdixit we should clear syntax-needs-review before commencing work on this.
@electrum ?

[ankitdixit]

@findepi I had already started working.
For syntax review is there any specified format?

[findepi]

I will defer to David and Martin on this.

[JeffryMAC]

This is kind of useful @ankitdixit @findepi what is the status?

[JeffryMAC]

any update?

[leetcode-1533]

Hi, @JeffryMAC @findepi it has been 2 months without reply. I am really interested in this, can I get assigned as my first task?

Suggested change to https://prestosql.io/docs/current/sql/alter-table.html:
1) ALTER TABLE [IF EXISTS] name RENAME TO new_name
2) ALTER TABLE [IF EXISTS] name ADD COLUMN [IF NOT EXISTS] column_name data_type [ COMMENT comment ] [ WITH ( property_name = expression [, ...] ) ]
3) ALTER TABLE [IF EXISTS] name DROP COLUMN [IF EXISTS] column_name
4) ALTER TABLE [IF EXISTS] name RENAME COLUMN [IF EXISTS] column_name TO new_column_name

Notice, I found for "ALTER TABLE [IF EXISTS] name RENAME TO new_name", postgres https://www.postgresql.org/docs/current/sql-altertable.html don't have a if statement for "new_name". So I followed the same convention(Same convention applied to "new_column_name"). Tested locally myself:

When "name" and "new_name" exist, execute: "alter table IF EXISTS test rename to testnewtable"
ERROR: relation "testnewtable" already exists

[findepi]

assigning to @electrum for syntax review.

[electrum]

Looks good to me.

[leetcode-1533]

thanks! working on it.

[leetcode-1533]

update: finished changing the AST and added the unit test, if working on ignoring the exceptions. Should be able to sent it in a day or two.

[kokosing]

@y1275963 Please make sure you sign CLA, please follow: https://github.com/prestosql/cla This is required in order to merge your changes.

[leetcode-1533]

pull request :https://github.com/prestosql/presto/pull/4651