Compute SHA-256 in PostgreSQL: digest, pgcrypto, and when to hash in the DB
PostgreSQL has a builtin MD5 function and a richer hash family in the pgcrypto extension. The choice between them is mostly about whether the hash is for security or just for fingerprinting, and whether the database is the right place to compute it at all.
MD5 is built in, everything else needs pgcrypto
md5(text) returns a 32-character lowercase hex string and ships in core Postgres with no extension.
SELECT md5('hello world');
-- 5eb63bbbe01eeed093cb22bb8f5acdc3For SHA-1, SHA-224, SHA-256, SHA-384 and SHA-512, you need the pgcrypto extension and its digest function:
```sql CREATE EXTENSION IF NOT EXISTS pgcrypto;
SELECT digest('hello world', 'sha256'); -- bytea: \xb94d27b9934d3e08a52e52d7da7dabfac484efe37a5380ee9088f7ace2efcde9 ```
digest always returns bytea (raw bytes). To get a hex string out the door, wrap it with encode.
encode and the bytea round-trip
encode(bytea, 'hex') turns raw bytes into a lowercase hex string, the form most APIs and storage layers expect:
SELECT encode(digest('hello world', 'sha256'), 'hex');
-- b94d27b9934d3e08a52e52d7da7dabfac484efe37a5380ee9088f7ace2efcde9For binary storage, leave it as bytea and save the 16 or 32 bytes directly. A SHA-256 hex string is 64 characters; the raw bytea is 32 bytes. That is half the storage and identical lookup speed once you index it. Convert to hex only at the API boundary.
encode also supports 'base64' and 'escape' if your downstream consumer prefers those forms. decode(text, 'hex') is the inverse: it parses a hex string back into bytea for comparison.
MD5 is fine for fingerprints, not for security
MD5 is broken as a cryptographic hash. Collisions are computationally cheap. Do not use it for password hashing, signature verification, or any context where an attacker could craft a colliding input.
MD5 is still fine for non-adversarial fingerprinting: detecting whether two rows are byte-identical, deduping log lines, building a checksum column to short-circuit equality checks. The probability of accidental collision over any realistic dataset is still effectively zero.
For passwords, neither MD5 nor SHA-256 is the answer. Use crypt(password, gen_salt('bf', 12)) from pgcrypto, which is bcrypt with a cost factor. Or, better, do password hashing in the application with argon2 and store only the resulting digest. Doing it in the database means the plaintext password reaches the database, which expands your blast radius if the DB is breached or its query log leaks.
When to hash in the DB at all
Hash in the database when the hash is part of the data: a content-addressed lookup column, a deterministic dedupe key, a CHECK constraint that the row body matches a stored fingerprint.
Hash in the application when the hash is part of an authentication or signing flow: passwords, JWT signatures, HMAC of an API request body, content of an end-to-end encrypted message. The database should never see the plaintext, and computing the hash there means it does.
A useful rule of thumb: if the input to the hash is also stored in the same table, computing the hash in the DB is fine. If the input never gets stored at all, the DB has no business seeing it.
gen_random_uuid is in pgcrypto too, but it is not a hash. It generates a random UUID v4. Do not confuse it with digest when reading other people's migrations.
Working example
sql-- Enable pgcrypto for SHA family
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- Add a SHA-256 fingerprint column to a documents table
ALTER TABLE documents ADD COLUMN body_sha256 bytea;
-- Backfill: hash the existing body bytes
UPDATE documents
SET body_sha256 = digest(body, 'sha256');
-- Index it for fast equality lookup
CREATE INDEX documents_body_sha256_idx ON documents (body_sha256);
-- Find duplicates by content
SELECT body_sha256, count(*)
FROM documents
GROUP BY body_sha256
HAVING count(*) > 1;
-- Read a hash back out as hex for an API response
SELECT id, encode(body_sha256, 'hex') AS body_sha256_hex
FROM documents
WHERE id = $1;Just need the result?
When you have a string in your clipboard and just want to compare its SHA-256 against a value in a deployment manifest, opening psql is overkill. The browser-based hash generator at aldeacode.com computes MD5, SHA-1, SHA-256 and SHA-512 with the Web Crypto API, never sends the input to a server, and gives you the hex digest in one paste.
Open SHA Hash Generator →Frequently asked questions
Why does digest return something that starts with \x?
That is psql's default representation of bytea. The actual stored value is the raw bytes; the \x prefix is just how the client prints them. Wrap the call in encode(digest(...), 'hex') to get a clean hex string, or in encode(..., 'base64') for base64.
Can I use MD5 for hashing user passwords?
No. MD5 is fast and collision-prone, exactly the wrong properties for a password hash. Use crypt(password, gen_salt('bf', 12)) from pgcrypto if you must hash in the DB, or hash with argon2 in the application before the value ever reaches the database.
Is computing SHA-256 in the database slower than in the app?
On a per-row basis, comparable. The cost difference is dominated by the round-trip to the application, not the hash itself. Compute in the DB when you avoid a round-trip (a fingerprint column on INSERT, a CHECK constraint), in the app when you avoid sending plaintext over the wire (passwords, secrets, request signatures).