PostgreSQL: How to Append String to Column Value

Often in PostgreSQL you may want to append a string to a column value.

You can use the || operator with the following syntax to do so:

UPDATE athletes SET team = 'The_' || team;

This particular example will append the string ‘The_‘ to the beginning of each string in the team column of the athletes table.

Note: The UPDATE statement will update the existing values within your table. Feel free to use the SELECT statement with the || operator to instead display the existing values with the appended string.

The following example shows how to use this syntax in practice.

Example: How to Append String to Column Value in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER,
  team TEXT,
  role TEXT
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 'Guard');
INSERT INTO athletes VALUES (0002, 'Hawks', 'Forward');
INSERT INTO athletes VALUES (0003, 'Rockets', 'Forward');
INSERT INTO athletes VALUES (0004, 'Mavs', 'Guard');
INSERT INTO athletes VALUES (0005, 'Mavs', 'Center');
INSERT INTO athletes VALUES (0006, 'Celtics', 'Guard');
INSERT INTO athletes VALUES (0007, 'Lakers', 'Center');

-- view all rows in table
SELECT * FROM athletes;

 id |  team   |   role   
----+---------+----------
  1 | Mavs    | Guard
  2 | Hawks   | Forward
  3 | Rockets | Forward}
  4 | Mavs    | Guard
  5 | Mavs    | Center
  6 | Celtics | Guard
  7 | Lakers  | Center

Suppose that we would like to append the string ‘The_‘ to the beginning of each string in the team column.

We can use the following syntax to do so:

-- add 'The' to each string in team column
UPDATE athletes SET team = 'The_' || team;

-- view updated table
SELECT * FROM athletes;

 id |    team     |   role   
----+-------------+----------
  1 | The_Mavs    | Guard
  2 | The_Hawks   | Forward
  3 | The_Rockets | Forward}
  4 | The_Mavs    | Guard
  5 | The_Mavs    | Center
  6 | The_Celtics | Guard
  7 | The_Lakers  | Center

Notice that each string in the team column now has the text ‘The_‘ appended to the beginning of it.

Feel free to use similar syntax to append text to a string column in your own table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Extract Substring Before Specific Character
PostgreSQL: How to Extract Substring Between Two Characters
PostgreSQL: How to Check if String Contains Any Letters
PostgreSQL: How to Check if String Ends with Specific Value