Alter The PostgreSQL Table Columns
Introduction to PostgreSQL Alter Table.
WRITERS: Please write introduction on altering the columns of a pre-existing PostgreSQL table.
All of the PostgreSQL statements for tables
The following is a list of SQL statements that you can use to modify a PostgreSQL table:
ADD– using the alter table add to create additional column in a table.
MODIFY– using the alter table alter to revise the column, and the data type.
DROP– using the alter table drop to delete the column from the table.
RENAME– using the alter table rename to change the column name of the given table.
This article will cover the
ALTER TABLE statement.
Create postgreSQL database
By using the command prompt (on windows), or terminal on (mac and linux), you can create a database in PostgreSQL. First, enter into the
psql command-line interface:
Once inside of the
psql interface use the following SQL statement to create the database:
Creating a table in a PostgreSQL database
- The syntax for the
psqlSQL command to create table is as follows:
Granting a user privileges to a PostgreSQL table
Use the following SQL statement to grant privilege to create a PostgreSQL table using a database:
Using the command
\lwill show you the list of all the databases that are already in postgreSQL.
Connecting to the database using the command:
In here, we are gonna use the database that we have created earlier:
NOTE: Use the only database that you have created.
Now that we have created a table, let’s proceed to the altering process.
Using the ‘ALTER TABLE’ command to add a column to the PostgreSQL table
The following is an example of using the
ALTER TABLE SQL command to add a
VARCHAR column to the table:
NOTE That you can also insert multiple columns in the table that you want.
Return a list of the PostgreSQL table’s columns
Now we can use the
\d+ command to check to make sure that the
ALTER TABLE command worked properly by having it return all of the column names:
The above code should return a table response including the new column data type that looks something like this:
Adding a ‘TIMESTAMP’ column to pre-existing PostgreSQL table
Here’s how to add a timestamp column to pre-existing PostgreSQL table:
Adding multiple columns to a PostgreSQL table
Dropping a column from a PostgreSQL table
postgres=# ALTER TABLE employee ALTER COLUMN join_date TYPE DATE;
NOTE That you can also modify multiple columns in a table.
postgres=# ALTER TABLE employee DROP COLUMN emp_contact;
Drop multiple columns in the table using the syntax `ALTER TABLE table_name DROP COLUMN column_name1, column_name2;
postgres=# ALTER TABLE employee RENAME emp_name TO emp_fullname;
** You can also rename your table to a new one using the “ALTER TABLE table_name RENAME TO new_table_name;
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started