w3resource

PostgreSQL Alter Table: Alter a table to add a new column


2. Write a SQL statement to add a column region_id to 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)  |

Now execute the following statement.

Sample Solution:

Code:

ALTER TABLE locations
ADD region_id  INT;

Output:

See the structure of the table after alteration.

postgres=# \d locations

     Column     |         Type          | M
----------------+-----------------------+--
 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      | integer               |

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

Previous: Write a SQL statement to rename the table countries to country_new.
Next: Write a SQL statement to change the data type of the column region_id to text in the table locations.

What is the difficulty level of this exercise?



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/postgresql-exercises/alter-table/alter-table-exercise-2.php