w3resource

PostgreSQL Alter Table: Alter a table to drop a column from it


4. Write a SQL statement to drop the column city from the table locations.

Here is the structure of the table locations.

postgres=# \d locations
     Column     |         Type          | Modifiers
----------------+-----------------------+-----------
 location_id    | numeric(4,0)          |
 street_address | character varying(40) |
 postal_code    | character varying(12) |
 city           | character varying(30) |
 state_province | character varying(25) |
 country_id     | character varying(2)  |
 region_id      | text                  |

Now execute the following statement.

Sample Solution:

Code:

ALTER TABLE locations
DROP city;

Output:

Now see the structure of the table locations after alteration.

     Column     |         Type          | Modifiers
----------------+-----------------------+-----------
 location_id    | numeric(4,0)          |
 street_address | character varying(40) |
 postal_code    | character varying(12) |
 state_province | character varying(25) |
 country_id     | character varying(2)  |
 region_id      | text                  |

The above result shows that there is no 'city' column; it has been dropped from the table locations.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a SQL statement to change the data type of the column region_id to text in the table locations.
Next: Write a SQL statement to change the name of the column state_province to state, keeping the same size and data type and size

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.