sql - PostgreSQL: On insert, combine two values into a 3rd column - Stack Overflow

admin管理员组

文章数量:1415420

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 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:

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:

• https://stackoverflow/a/79413015/14179793
• https://stackoverflow/a/79413372/14179793

VIEW Solution:

• https://stackoverflow/a/79412921/14179793

• sql
• postgresql

Share Improve this question Follow edited Feb 5 at 14:25 Cogito Ergo Sum asked Feb 4 at 19:49 Cogito Ergo SumCogito Ergo Sum 87222 gold badges1111 silver badges2424 bronze badges 8

• You could create a trigger. – jarlh Commented Feb 4 at 19:51
• 5 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 Commented Feb 4 at 19:51
• Do you need it only as a default, or would a computed GENERATED column work – Charlieface Commented Feb 4 at 19:51
• 1 I.e. what do you expect insert into test_table (column_1, column_2, combined) values ('Alpha', 'Beta', 'different') to insert? – jarlh Commented Feb 4 at 19:55
• 1 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 Commented Feb 4 at 20:02

 |  Show 3 more comments

3 Answers 3

Sorted by: Reset to default 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

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
);

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

本文标签： sqlPostgreSQL On insertcombine two values into a 3rd columnStack Overflow