Alter PostgreSQL Table on ObjectRocket
Introduction
When you create a table in PostgreSQL, its initial definition doesn’t need to be set in stone. You can make a variety of changes to the table’s structure: adding columns, removing columns, changing a column’s data type and much more. In this article, we’ll show you how to alter a PostgreSQL table on ObjectRocket and provide some simple examples to get you started.
Prerequisites
Before you begin this tutorial, make sure that the following prerequisites are in place: * You’ll need to have PostgreSQL 11 installed and configured on your machine.
- To verify that the Postgres CLI is installed and working properly, type
psql -Vin your terminal.
- You’ll also need to create an instance of Postgres for your ObjectRocket account. This can be done using the ObjectRocket Mission Control panel.
PostgreSQL ALTER TABLE
The PostgreSQL statement ALTER TABLE allows us to modify the structure and definition of a PostgreSQL table. You can alter a Postgres table on ObjectRocket in the following ways:
A table column data type can be altered, with all constraints and indexes associated with the table converted automatically to the specified new type.
A column constraint can be added.
A new column can be added.
A column can be dropped, with associated constraints and indexes dropped automatically.
Shown below is the basic syntax of the ALTER TABLE command:
1 | ALTER TABLE action |
The action specified after the ALTER TABLE keywords can be any one of the following:
- changing a column data type, renaming, adding and dropping a column
- setting a default value for a table column
- adding column constraints
PostgreSQL ALTER TABLE Example
Before we can dive into our examples, we’ll need to connect to our PostgreSQL server instance on ObjectRocket using the following command:
1 | psql -h ingress.w98sujpz.launchpad.objectrocket.cloud -U pguser -p 4149 -d PostgreSQL |
After you execute the above command, you’ll be prompted for the password of the specified user.
Now that we’re connected to Postgres on ObjectRocket, let’s take a moment to review the structure of the sample table we’ll be working with in our examples:
1 2 3 4 5 6 7 8 9 | Table "public.item" Column | Type | Collation | Nullable | Default ----------+-----------------------+-----------+----------+---------------------------------- id | integer | | not null | nextval('item_id_seq'::regclass) sku | character varying(50) | | not null | price | integer | | | quantity | integer | | not null | Indexes: "item_pkey" PRIMARY KEY, btree (id) |
Adding New Table Column
To add a new column to the table shown above, we’ll use the following statement:
1 | ALTER TABLE item ADD COLUMN category VARCHAR (100); |
This statement will add a new column called category with a data type of VARCHAR.
To verify that the column was added, we can use the psql command \d followed by the table name item. The result looks like this:
1 2 3 4 5 6 7 8 9 10 11 | inventory=# \d item Table "public.item" Column | Type | Collation | Nullable | Default ----------+------------------------+-----------+----------+---------------------------------- id | integer | | not null | nextval('item_id_seq'::regclass) sku | character varying(50) | | not null | price | integer | | | quantity | integer | | not null | category | character varying(100) | | | Indexes: "item_pkey" PRIMARY KEY, btree (id) |
Removing Table Column
In the previous example, we showed you how to add a column. Now, let’s try to remove the category column.
We can use the statement shown below to remove a PostgreSQL table column:
1 | ALTER TABLE item DROP COLUMN category; |
The result will look something like this:
1 2 3 4 5 6 7 8 | Column | Type | Collation | Nullable | Default ----------+-----------------------+-----------+----------+---------------------------------- id | integer | | not null | nextval('item_id_seq'::regclass) sku | character varying(50) | | not null | price | integer | | | quantity | integer | | not null | Indexes: "item_pkey" PRIMARY KEY, btree (id) |
We can see that the category column was indeed removed.
Change Column Type
We can change the sku column data type from VARCHAR(50) to CHARACTER(50) using the following command:
1 | ALTER TABLE item ALTER COLUMN sku TYPE CHARACTER(50); |
The output should look something like this:
1 2 3 4 5 6 7 8 | Column | Type | Collation | Nullable | Default ----------+---------------+-----------+----------+---------------------------------- id | integer | | not null | nextval('item_id_seq'::regclass) sku | character(50) | | not null | price | integer | | | quantity | integer | | not null | Indexes: "item_pkey" PRIMARY KEY, btree (id) |
Notice that the sku column’s data type was changed to CHARACTER(50) as expected.
Add Constraint to a Column
We can even add a constraint to a column using the ALTER TABLE command. Let’s say that we want to add a NOT NULL constraint to the price column. We can use the following command:
1 | ALTER TABLE item ALTER COLUMN price SET NOT NULL; |
Our result should look like this:
1 2 3 4 5 6 7 8 9 | Table "public.item" Column | Type | Collation | Nullable | Default ----------+---------------+-----------+----------+---------------------------------- id | integer | | not null | nextval('item_id_seq'::regclass) sku | character(50) | | not null | price | integer | | not null | quantity | integer | | not null | Indexes: "item_pkey" PRIMARY KEY, btree (id) |
Conclusion
After you create a table in PostgreSQL, you may find yourself needing to change it in some way. Fortunately, you can make a number of modifications to an existing table with the help of the ALTER TABLE command. In this article, we showed you how to alter a Postgres table on ObjectRocket. With our examples to guide you, you’ll be able to modify tables in your own PostgreSQL environment.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started