Set default value of one column to another column

Question

Is it possible in PostgreSQL to have a column's default value be equal to another column? This seems to be possible in SQLAlchemy and possibly other database frameworks, but ideally this would be done in pure SQL.

For example,

-- given
create table my_example(
    column_a int primary key,
    column_b int default column_a
);

-- when
insert into my_example (column_a) values (42);

-- then this should return 42
select column_b from my_example where column_a=42;

When I try this myself, I the create table statement throws this error:

[0A000] ERROR: cannot use column reference in DEFAULT expression

This indicates to me that this is not possible to do, but I'm wondering if there is there some other way to achieve this?

Answer 1

As far as I can tell this is only achievable using triggers. The column has to be created without using the default keyword, and then you have to trigger an update on each inserted row.

create table my_example(
    column_a int primary key,
    column_b int
);

create or replace function column_b_default()
returns trigger as $$
begin
    if new.column_b is null then
        new.column_b := new.column_a;
    end if;
    return new;
end;
$$ language plpgsql;

create trigger column_b_default_trigger
before insert on my_example
for each row
    execute procedure column_b_default();

Something else I tried, but is not really a solution to this in general, is to use generated always. This comes with a drawback that you cannot insert values directly into column_b so I can't consider this a drop-in replacement for the default keyword. But, it's much less verbose than creating a trigger so it might be useful in some cases.

create table my_example(
    column_a int primary key,
    column_b int generated always as (column_a) stored
);