Discovering the ALTER TABLE command and INSERT statement with examples in PostgreSQL.

This blog post will demonstrate basic concepts of the ALTER TABLE command for changing an existing PostgreSQL table. INSERT statements will be introduced as well to populate an empty table with mock data. Visit Part 1 of this series for a refresher of the material covered up to this point.

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, instruction and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

I will be using Xubuntu Linux 16.04.2 LTS (Xenial Xerus), PostgreSQL 9.6.2 and pdAdmin for these exercises.

Regarding ALTER TABLE, I will touch base on a couple of the more commonly used commands to get you going. The plethora of options available for the ALTER TABLE command is too exhaustive to be covered in one post. Again, I cannot emphasize enough just how marvelous the PostgreSQL documentation is. I encourage you to refer to it for any questions and researching other available options.

So, I can change my table?

To edit or change an existing table, you use the ALTER TABLE command.

In the previous post of the series, I demonstrated creating a table named galaxy_user shown below:

Suppose the galaxy_coins column now needs to represent a sort of ‘game currency’ and must support decimal values instead of whole numbers. The ALTER TABLE command enables you to change the galaxy_coins column’s data type from INTEGER to a decimal value type. I will use the REAL data type for this hypothetical example.

First, you must specify which table you are changing using the following syntax:

ALTER TABLE table_name;

The syntax to change an existing columns’ data type appears below:

ALTER COLUMN column_name TYPE new_type;

Both statements can be used together in a single command.

The above snippet displays the ALTER TABLE and ALTER COLUMN commands grouped into one command to change (alter) the galaxy_coins column to type REAL.

I can verify the new table schema using the psql command ‘\d galaxy_user’ (‘\d’ means describe table where galaxy_user is the table name) in the Linux terminal:

The important thing to take away from the above code snippet is the REAL data type now shown for the galaxy_coins column where before it was of type INTEGER.

For the curious….

A few of the more common uses of the ALTER TABLE command that may interest you are listed below:

Not satisfied with your table name? This syntax will handle that for you.

ALTER TABLE current_table_name RENAME TO new_table_name;

Need another column? Try this command.

ALTER TABLE table_name ADD new_column_name DATA TYPE;

Don’t need that column so bad after all? Not to worry.

ALTER TABLE table_name DROP COLUMN column_to_drop;

---

But this table is empty!

Until now, the galaxy_user table has been empty. Just sitting there with no purpose other than existing. Time to change that.

As with the ALTER TABLE command, the INSERT statement has numerous available options, with only just a couple of them being covered here. For more information, consult the online INSERT documentation.

To populate a table with data, you use the INSERT statement. The basic syntax looks like this:

INSERT INTO table_name(column_name,column_name,column_name...) -- a list of column names
VALUES(column_value,column_value,column_value...); -- a list of values to insert into the columns

---

SQL INFORMATION: In SQL, two dashes or hyphens together is called a comment. Comments are ignored by SQL. From the above code snippet:

-- a list of column names 
-- a list of values to insert into the columns

these two comments will not be executed. These are single line comments, handy for documenting your code with details or instructions.

---

The below snippet demonstrates this syntax and inserts a row into the galaxy_user table:

Something worth mentioning from the above snippet. The galaxy_user table has 5 columns, however, the screenshot above shows values being inserted into only 4 of them. The column not specified is the user_id column which, is of data type SERIAL. An explanation on why I only inserted into 4 columns, excluding the user_id column, is quoted directly from the online documentation on SERIAL data types.

“To insert the next value of the sequence into the serial column, specify that the serial column should be assigned its default value. This can be done either by excluding the column from the list of columns in the INSERT statement or through the use of the DEFAULT key word."

Since I excluded the user_id column, from the list of columns in the INSERT statement, the next auto-incremented value is inserted in sequential order. Less work for you the developer. Woot woot!

Executing this INSERT statement in pgAdmin, reports back one row affected as depicted in the snippet below:

Only one row at a time?

Booooooring…

Inserting multiple rows simultaneously is a breeze. Simply separate the individual groups of values with commas. Here I will insert 4 more records in one INSERT statement:

Displayed below are the results:

A handy tidbit…

If you want an immediate output returned of useful information, you can use the RETURNING clause in the INSERT statement. Just specify which columns you want to be returned upon successful completion.

When executing the above INSERT statement including the RETURNING clause, you are returned the specified columns. I specified all the table columns in this example:

---

About those constraints…

Now I will test the UNIQUE and NOT NULL constraints, implemented during table creation, with some inserts that purposely violates them.

INSERT INTO galaxy_user(user_name,pass_word,email,galaxy_coins)
VALUES ('just_cause','SlimTime','Slimtime2@email.com',2.50);

Since the pass_word column value of ‘SlimTime’ already exists in another row, attempting this INSERT command will test the UNIQUE constraint.

As expected, the UNIQUE constraint prohibits a duplicate value from being inserted as shown by the error below:

ERROR:  duplicate key value violates unique constraint "galaxy_user_pass_word_key"
DETAIL:  Key (pass_word)=(SlimTime) already exists.

********** Error **********

ERROR: duplicate key value violates unique constraint "galaxy_user_pass_word_key"
SQL state: 23505
Detail: Key (pass_word)=(SlimTime) already exists.

What if I just leave the pass_word column value blank?

INSERT INTO galaxy_user(user_name,email,galaxy_coins) -- Removed the pass_word column from the list of columns
VALUES ('just_cause','time2@email.com',2.50); -- Attempting the INSERT without a value in the list of values for the password column.

By excluding the pass_word column from the column list, INSERT will attempt to fill the column with a default value. Since no DEFAULT value was specified for this column during table creation, a NULL value will be attempted instead. That will not work as is clearly shown below by the NOT NULL constraint catching the attempt:

ERROR:  null value in column "pass_word" violates not-null constraint
DETAIL:  Failing row contains (7, just_cause, null, time2@email.com, 2.5).

********** Error **********

ERROR: null value in column "pass_word" violates not-null constraint
SQL state: 23502
Detail: Failing row contains (7, just_cause, null, time2@email.com, 2.5).

---

Finishing up…

ALTER TABLE is a fundamental command for customizing existing database tables. Along with INSERT, becoming familiar with their use and many options will heighten and build up vital PostgreSQL skills.

Looking forward in the series, I will delve into reading the data present in our table with the SELECT statement and WHERE clause, while changing column values with the UPDATE command. Be sure and subscribe for more great PostgreSQL content so you don’t miss out!

---

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

To receive notifications for the latest post from this blog via email, please subscribe by clicking the ‘Follow Digital Owl’s Prose’ button in the sidebar!

---

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

---

Updated: 10/09/17
07/08/2018