Reputation: 414

Difference between "DROP COLUMN" and "DROP" keywords in PostgreSQL

I just noticed you can write both in PostgreSQL. Is there any difference or is it just a "simplified" syntax. As far as I know, both of these does exactly the same.

ALTER TABLE table DROP my_column;

ALTER TABLE table DROP COLUMN my_column;

EDIT: I've searched around for this, but couldn't find anything. That includes the documentation.

Upvotes: 1

Answers (3)

Vivek S.

Reputation: 21905

Both syntax have same meaning,its your wish to choose among the two syntax, for the below example Keyword COLUMN can used for more clarity.

ALTER TABLE table DROP COLUMN col1, 
                  DROP COLUMN col2, 
                  DROP COLUMN col3, 
                       .... 
                  DROP COLUMN col_n;

The document itself is described that the keyword COLUMN is optional, because as per the documentation

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] name
    RENAME TO new_name
ALTER TABLE [ IF EXISTS ] name
    SET SCHEMA new_schema
ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
    SET TABLESPACE new_tablespace [ NOWAIT ]

where action is one of:

    ADD [ COLUMN ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]

FYI, keywords written inside [] indicates optional

ex. DROP [ COLUMN ] here COLUMN is optional

Upvotes: 3