In Postgres, perform a partial match on an encrypted column value

In Postgres, you can execute a partial match on an encrypted column value. This will, however, necessitate the usage of a trigram index and the pg_trgm extension. Here's an illustration:

-- Enable the pg_trgm extension
CREATE EXTENSION pg_trgm;

-- Create a table with an encrypted column
CREATE TABLE encrypted_data (
  id SERIAL PRIMARY KEY,
  encrypted_column TEXT ENCRYPTED USING 'aes'
);

-- Insert some encrypted data
INSERT INTO encrypted_data (encrypted_column)
VALUES ('encrypted value 1'), ('encrypted value 2');

-- Create a trigram …

column database encrypted partial postgres programming value webdev