Generated sha256 column in Postgres from varchar/text column?

Question

I'm trying to create the following table in PostgreSQL 13:

CREATE TABLE cached (
  text VARCHAR NOT NULL,
  text_hash BYTEA GENERATED ALWAYS AS (sha256(convert_to(text, 'LATIN1'))) STORED PRIMARY KEY
);

However, I'm getting the following error:

generation expression is not immutable

I'm guessing that this is because convert_to is not immutable. How do I work around this? Is there a way of converting the text column to bytea in an immutable way?

Things I'd rather avoid:

Casting to text::bytea. It won't work correctly as explained here.
Using triggers.

convert_to(text, 'LATIN1') is as problematic as ::bytea. What if text uses a different collation? What if it's UTF8? That cast will mangle the text unless it's LATIN1 or all characters are in the US-ASCII range. Even English names can't be represented in the US-ASCII range, eg Charlotte Brontë — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 10, 2023 at 13:58
You could lie. Create a function(say conv_txt()) that does convert_to(text, 'LATIN1') and mark it IMMUTABLE and then do (sha256(conv_txt(text))). You will have to accept the consequences if the text strays from the expected. — user7070613
– user7070613, Commented Feb 10, 2023 at 16:25
Or not lie. Use digest from pgcrypto extension. As in text_hash BYTEA GENERATED ALWAYS AS (digest(text, 'sha256')) STORED PRIMARY KEY — user7070613
– user7070613, Commented Feb 10, 2023 at 17:26
As an aside, naming a column/table with a reserved word is a very poor decision. — Bohemian
– Bohemian ♦, Commented Feb 12, 2023 at 0:33

mwalter · Accepted Answer · 2023-02-11 22:12:47Z

3

CREATE OR REPLACE FUNCTION cripto(TEXT )
RETURNS TEXT AS 
$function$
DECLARE tText TEXT ; 

BEGIN
SELECT sha256(convert_to($1, 'LATIN1')) INTO tText;
RETURN tText ;

END ;
$function$ 
IMMUTABLE
LANGUAGE plpgsql ; 


CREATE TABLE cripto(
text TEXT , 
text_hash TEXT GENERATED ALWAYS AS ( cripto(text) ) STORED PRIMARY KEY 
);

answered Feb 11, 2023 at 22:12

mwalter

1325 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Daniel Over a year ago

Lol, you create an immutable wrapper around a stable function. But convert_to should be immutable anyway, so no problem here...

Collectives™ on Stack Overflow

Generated sha256 column in Postgres from varchar/text column?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related