PostgreSQL: On insert, combine two values into a 3rd column

Question

I tried to do the following:

CREATE TABLE test_table 
(
    column_1 varchar(255),
    column_2 varchar(255),
    combined varchar(255) DEFAULT concat(column_1, '_', column_2)
);

It produces the following error:

ERROR: cannot use column reference in DEFAULT expression

This similar question uses an update query for the entire table: Insert value into a column in PostgreSQL

I would like to know if there is a way to do this as part of the insert?

With the following as an example of input and expected output:

insert into test_table ('column_1', 'column_2') 
values ('Alpha', 'Beta')

select * from test_table;
('Alpha', 'Beta', 'Alpha_Beta')

Notes:

Inputs are always strings
They will likely always be under 20 characters
I can't create a https://dbfiddle.uk/HuZKDYgb because I don't know how to create the table

Conclusion The answers by @JoeStefanelli and @Charlieface both answer the question with the original intention in mind of having a third column with the composite value. I think that the answer by @jarlh may actually be more optimal though? By using the view the duplicate information is not stored in the database. Also in my case I know that the inputs will never be null or empty, I did not think to include that in the initial question though as I was focused on the combined value solution.

GENERATED ALWAYS Solutions:

VIEW Solution:

https://stackoverflow.com/a/79412921/14179793

From here CREATE TABLE you are looking for GENERATED ALWAYS AS ( generation_expr ) STORED |. This works in all currently supported Postgres versions 13+. — Adrian Klaver
– Adrian Klaver, Commented Feb 4 at 19:51
Do you need it only as a default, or would a computed GENERATED column work — Charlieface
– Charlieface, Commented Feb 4 at 19:51
I.e. what do you expect insert into test_table (column_1, column_2, combined) values ('Alpha', 'Beta', 'different') to insert? — jarlh
– jarlh, Commented Feb 4 at 19:55
255 + 255 =510, that won't fit into 255. And besides that, why do you want to store something you already have? And what are your requirements for NULLs? — Frank Heikens
– Frank Heikens, Commented Feb 4 at 20:02

jarlh · Accepted Answer · 2025-02-04 20:09:54Z

5

You can create a VIEW that combines the columns:

CREATE TABLE test_base_table (
  column_1 varchar(255),
  column_2 varchar(255)
);

create view test_table
  as select column_1, column_2, column_1 || '_' || column_2 as combined
     from test_base_table;

insert into test_table (column_1, column_2) values ('Alpha', 'Beta');

select * from test_table;

column_1    column_2    combined
========    ========    ==========
Alpha       Beta        Alpha_Beta

Demo: https://dbfiddle.uk/VohrDmeq

answered Feb 4 at 20:09

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joe Stefanelli · Accepted Answer · 2025-02-04 21:02:35Z

5

You want to define that combined column as a generated column. NOTE: The CONCAT function is not immutable, so you have to roll your own with a CASE statement for the generated column.

CREATE TABLE test_table 
(
    column_1 varchar(255),
    column_2 varchar(255),
    combined varchar(255) GENERATED ALWAYS AS 
        (CASE WHEN column_1 IS NULL THEN column_2 
              WHEN column_2 IS NULL THEN column_1
              ELSE column_1 || '_' || column_2 END) STORED
);

edited Feb 4 at 21:02

answered Feb 4 at 20:54

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

2 Comments

Cogito Ergo Sum Feb 4 at 21:01

I had to change GENERATED ALWAYS AS concat(column_1, '_', column_2) STORED to GENERATED ALWAYS AS (collection_id || '_' || granule_id) STORED. The enclosing parentheses are required and concat is apparently not immutable.

Frank Heikens Feb 4 at 21:10

Advice: Make the combined a little longer. It doesn't work as expected with two strings of 255 characters. The total length can now be 511 characters and that doesn't fit. Write your test cases!

Charlieface · Accepted Answer · 2025-02-05 09:09:31Z

You can use concat_ws to simplify the expression, as it deals with nulls and inserts a separator only when necessary.

Unfortunately it's only marked as SAFE not IMMUTABLE so it wouldn't be allowed in a STORED column. But we can create an IMMUTABLE alias for it. Do not use any other data types apart from text, as there may be unstable conversions involved.

CREATE OR REPLACE FUNCTION immutable_concat_ws(text, VARIADIC text[])
  RETURNS text
  LANGUAGE internal IMMUTABLE PARALLEL SAFE AS
'text_concat_ws';

Then

CREATE TABLE test_table 
(
    column_1 varchar(255),
    column_2 varchar(255),
    combined varchar(511) GENERATED ALWAYS AS (
        immutable_concat_ws('_', column_1, column_2)
    ) STORED
);

db<>fiddle

Collectives™ on Stack Overflow

PostgreSQL: On insert, combine two values into a 3rd column

3 Answers 3

Comments

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Linked

Related