Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();

version

PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit

SELECT 1

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE EXTENSION

create table a_table ( normal_value text, secret_value bytea)

CREATE TABLE

insert into a_table (
  normal_value,
  secret_value
) values (
  'normal',
  pgp_sym_encrypt(0.123::text, 'secret_key')
)

INSERT 0 1

alter table a_table add secret_sym bytea

ALTER TABLE

update a_table set secret_sym = encrypt(cast(pgp_sym_decrypt(secret_value, 'secret_key') as bytea), 'secret_key'::bytea, 'aes-ecb/pad:pkcs'::text)

UPDATE 1

select * from a_table

normal_value
secret_value
secret_sym

normal
\xc30d04070302de219af661c36b5f77d236012d6175d08add3e09b969d07f392675dd245d7ce1eaea5c483b861b17fdfbd27503e81794de75badf0b3d7e326dd66c7815814ce044
\x30e9bfa342cd1ff84cd50724f7b4617b

SELECT 1