How to Copy the Records from One Column in a PostgreSQL Table to Another
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.
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 statuscommand. 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 -Vto 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:
psql someUserName -h 127.0.0.1 -d some_database
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
Type for all the table’s column names and their respective data types.
NOTE: The data type
character varying is equivalent to
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:
ALTER TABLE some_table
ADD COLUMN col_NEW VARCHAR (64);
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
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:
UPDATE some_table SET col_NEW = col_str;
This statement should return a response saying
UPDATE, followed by an integer value indicating the number of records that were copied (e.g.
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:
ERROR: COLUMN "int_col" does NOT exist
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:
SELECT * FROM some_table;
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:
ALTER TABLE some_table
DROP COLUMN col_NEW;
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
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started