Postgres: Can I bypass the error "cannot insert into generated column" using a PostgreSQL INSTEAD OF INSERT rule?

user_1282498 0
Viewed 550

I know this isn't pretty but it would be helpful to bypass the error for insert into a generated column in Postgres. Let's say, we have a table like so:

create table testing (
    id int primary key,
    fullname_enc bytea,
    fullname text generated always as (pgp_sym_decrypt(fullname_enc, 'key')) stored
);

A query like the following returns the expected error: ERROR: cannot insert into column "fullname" DETAIL: Column "fullname" is a generated column.

insert into testing(id, fullname) values (3, 'John Doe');

I want to create a rule on this table on INSERTs like:

create rule encrypter as on insert to testing DO INSTEAD insert into testing (id, fullname_enc) values (new.id, pgp_sym_encrypt(new.fullname, 'key'));

Since we rewrite the query, I was naively thinking if this would not result in the error from the engine but it still does. Any idea how this could be achieved?

The reason for asking this is migration to PostgreSQL 12.

generated-columns rules postgresql
1 Answers

This cannot be achieved, and if it could be achieved somehow, that would be a bug that needs to be fixed. Otherwise, restoring from a dump would change the values.

I think that what you need is a BEFORE trigger that sets fullname.

I hope that this is a mock example and not something that is intended to improve security.

user_64643080
Related