PostgreSQL ALTER

ALTER TABLE:

We want to add a column named color to our cars table.

When adding columns we must also specify the data type of the column. Our color column will be a string, and we specify string types with the VARCHAR keyword. we also want to restrict the number of characters to 25

postgres=# ALTER TABLE car 
ADD color VARCHAR(25); 

ALTER TABLE

UPDATE TABLE:

The UPDATE statement is used to modify the value(s) in existing records in a table.

postgres=# UPDATE car 
SET color = 'red'
WHERE brand = 'Volvo'; 

UPDATE 1

postgres=# select * from car;
 brand  |  model  | year | color 
--------+---------+------+-------
 Ford   | Mustang | 1964 | 
 BMW    | M1      | 1978 | 
 Toyota | Celica  | 1975 | 
 Volvo  | p1800   | 1968 | red
(4 rows)

ALTER COLUMN:

We want to change the data type of the year column of the cars table from INT to VARCHAR(4).

To modify a column, use the ALTER COLUMN statement and the TYPE keyword followed by the new data type:

postgres=# ALTER TABLE car 
ALTER COLUMN year TYPE VARCHAR(4); 

ALTER TABLE

We also want to change the maximum number of characters allowed in the color column of the cars table.

Use the same syntax as above, use the ALTER COLUMN statement and the TYPE keyword followed by the new data type:

postgres=# ALTER TABLE car 
ALTER COLUMN color TYPE VARCHAR(30);

ALTER TABLE

DROP COLUMN

We want to remove the column named color from the cars table.
To remove a column, use the DROP COLUMN statement:

postgres=# ALTER TABLE car 
DROP COLUMN color; 

ALTER TABLE

DELETE

The DELETE statement is used to delete existing records in a table.

postgres=# DELETE FROM car 
WHERE brand = 'Volvo'; 

DELETE 1

Delete All Records

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact.

The following SQL statement deletes all rows in the cars table, without deleting the table:

postgres=# DELETE FROM car; 

DELETE 3

DROP TABLE

The DROP TABLE statement is used to drop an existing table in a database.

postgres=# DROP TABLE car; 
DROP TABLE

Reference:

https://www.w3schools.com/postgresql/postgresql_drop_table.php