How to Copy the Records from One Column in a PostgreSQL Table to Another

Introduction

When you create a table in PostgreSQL and define columns for it, you can’t always predict how requirements may evolve as time goes on. There may be situations where you need to copy data from one column to another within a table. Fortunately, this task is easy to accomplish using the UPDATE command in SQL. In this tutorial, we’ll show you how to create a SQL statement using UPDATE that will copy records from a column in a PostgreSQL table and insert them into another column.

Prerequisites

It’s helpful to follow along with the examples we’ll be showing you in this article, so be sure that the following prerequisites are in place:

• First, make sure that PostgreSQL is installed. To check if the status is active, use the service postgresql status command. You can then press CTRL + C to exit.

• You’ll also need to have psql, the command-line interface for PostgreSQL, installed on your machine. Simply type psql -V to verify that it’s installed and working properly.

Enter the ‘psql’ Command-line Interface for PostgreSQL

Throughout this tutorial, we’ll be interacting with our PostgreSQL database through psql. You can use the following command in a terminal window to access a PostgreSQL database using the psql command-line interface:

You’ll be prompted for the user’s password. Once you input the password and press Return, you should then have access to the database.

Connecting to a Database in ‘psql’

If you entered the psql interface without specifying a database, or if you just decide that you want to connect to another database instead, you can use the following command:

Return a List of All PostgreSQL Table Information

Once you’re inside the psql interface, you can use the \dt command to list all of the databases. You can also use the \d+ command, followed by a table name, to have psql return a list of a specific table’s column names:

This command should return a table that includes the Column and Type for all the table’s column names and their respective data types.

NOTE: The data type character varying is equivalent to VARCHAR.

Create a New PostgreSQL Table Column and Copy Records into It

Now, let’s create the new column into which we’ll be copying records.

Add a column to the PostgreSQL table

We’ll use the ALTER TABLE and ADD COLUMN SQL keywords to execute a SQL statement that will add our new column. Here’s an example of a SQL statement that will create a new column for some_table with a ‘character varying’ data type:

NOTE: Make sure that your new column’s data type matches the data type of the original column being copied.

You should get a response saying ALTER TABLE.

Use the ‘UPDATE’ SQL Keyword to Copy the Contents of a PostgreSQL Column

Next, we’ll copy the contents of an existing column into our newly-created column:

This statement should return a response saying UPDATE, followed by an integer value indicating the number of records that were copied (e.g. UPDATE 3).

Getting an Error after Executing the ‘UPDATE’ Statement

If one of the columns specified in the above SQL statement doesn’t exist, you’ll get an error:

You may also get an error stating: ERROR: null value in column "old_col" violates not-null constraint. This could mean that the data type for the new column doesn’t exactly match the old column’s data type. If you encounter this type of error, drop the column and try again.

Return All Record Data for the Table

We can use the SELECT keyword to have psql return all record data for the table. This will help us verify that the column of records was successfully copied:

You should now see a table of data that includes the new column named col_new with data copied into it:

Drop and Delete the New PostgreSQL Column

If you decide that you’d like to DROP a column in your table, you can use the ALTER TABLE and DROP COLUMN keywords. Here’s an example that shows how to drop our newly-created column and all of its data:

Conclusion

When you need to make changes to a table in your PostgreSQL database, it’s important to know which commands to use for the job. In this tutorial, we focused on the UPDATE and SET keywords, which can be used in tandem to copy records from a column in a PostgreSQL table and insert them into another column. Using the examples we provided throughout this article, you’ll be able to copy and move data in your own database environment.