Alter PostgreSQL Table on ObjectRocket

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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 -V in 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

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.