Having md5(NULL) return a non-NULL value in postgresql I have a table with the structure (content_md5 UUID, content TEXT), where I'd like to use content_md5 (which is the value of md5(content)) as the primary key, and use it as a foreign key in other tables.

This is a "static" table, where the content (some largish documents) would be referred to by their md5 value for simplicity, and to prevent duplication in the table (which wouldn't be a given with a simple SERIAL PKEY).

However, content can be NULL, which is different from an empty value to declare a non-existing content field in the referencing table.
Since md5(NULL) returns NULL, and NULL is not allowed in a primary key constraint, I'd like to have a way of having md5(NULL) return all zeros instead of NULL.  

Example:

-- setup
CREATE TABLE example (content_md5 UUID PRIMARY KEY, content TEXT);
CREATE TABLE test (id SERIAL PRIMARY KEY, tags TEXT, content_md5 UUID REFERENCES example(content_md5) ON DELETE RESTRICT);
INSERT INTO  example VALUES ('00000000000000000000000000000000'::uuid, NULL);

-- usage
INSERT INTO example VALUES (md5('some text')::uuid, 'some text');
INSERT INTO test (tags, content_md5) VALUES ('some content defining tags', md5('some text')::uuid);

SELECT tags, content FROM test LEFT JOIN example USING (content_md5);

-- QUESTION: Having an md5-like function to return zero-filled "md5"/uuid?
INSERT INTO example VALUES (md5(NULL)::uuid, NULL); -- ignored, because already existing record
INSERT INTO test (tags, content_md5) VALUES ('non-existing-document', md5(NULL)::uuid);


Is it possible to somehow cast the returned value to a zero-filled string, create a custom function based on md5() which replaces NULL with 00000000000000000000000000000000, or some other way to achieve this result?

/edit: Or perhaps I don't need any NULL values in this table, and can just set the referencing foreign key column to NULL to achieve the same result?
I suggest this alternative design:

-- setup
CREATE TABLE example (content_id serial PRIMARY KEY, content text);
CREATE TABLE test (id serial PRIMARY KEY, tags TEXT, content_id int REFERENCES example);

CREATE UNIQUE INDEX ON example ((md5(content)::uuid)) INCLUDE (content_id); -- !

-- usage
INSERT INTO example(content) VALUES (NULL);        -- allowed multiple times
INSERT INTO example(content) VALUES ('some text');

INSERT INTO test (tags, content_id)
SELECT 'some content defining tags', content_id
FROM   example
WHERE  md5(content)::uuid = md5('some text')::uuid;


db&lt;>fiddle here

Major points

Use a serial column (content_id) as surrogate PK of table example - and as FK reference everywhere. 4 bytes instead of 16.

Enforce uniqueness with a unique index on the expression md5(example)::uuid. Be aware that hash collisions are possible (even if very unlikely while your table isn't huge). 

While being at it, add the serial PK column to the index with an INCLUDE clause (Postgres 11 or later) to make it a covering index for fast index-only lookup.

As opposed to a PK column, this allows NULL, and NULL is not considered to be a duplicate of NULL, which should cover your use case. See:


Allow null in unique column


In Postgres 10 or older don't add content_id to the index. Then you don't get index-only scans, of course:

CREATE UNIQUE INDEX ON example ((md5(content)::uuid));


Unless you want to allow only a single instance of NULL, which could be enforced with a function like you posted (introducing the risk of a collision - even if unlikely) or a tiny partial index in addition to the one above:

CREATE UNIQUE INDEX ON example (content_id)
WHERE md5(content)::uuid IS NULL;


See:


Create unique constraint with null columns


Do not store the md5 value as table column (redundantly) at all.



If you want to keep using the function you posted in your answer, consider optimizing it:

CREATE OR REPLACE FUNCTION pg_temp.md5zero(data text)
  RETURNS uuid PARALLEL SAFE IMMUTABLE LANGUAGE sql AS
$func$
SELECT COALESCE(md5(data)::uuid, '00000000000000000000000000000000')
$func$


Faster, and can be inlined. See:


Query slow when using function in the WHERE clause