SHA256

Compute the SHA-256 hash of a string and return it as a 64-character lowercase hexadecimal string.

Category: miscReturns: STRINGDialect: PostgreSql

Syntax

SHA256(str)

Description

## Overview Computes the SHA-256 cryptographic hash of the UTF-8-encoded input string and returns the digest as a lowercase hexadecimal string. SHA-256 produces a 256-bit (32-byte) digest and is the most commonly used member of the SHA-2 family for general fingerprinting, change detection, and content-addressed storage. ## Algorithm and output - Returns a 64-character lowercase hexadecimal string (`[0-9a-f]`). - The digest is 256 bits (32 bytes) long. - Collision resistance is approximately 2^128 operations; second-preimage resistance is approximately 2^256. - Compare digests hex-for-hex; direct equality with `=` is collation-safe because the output is ASCII. ## Behavior - Returns NULL if the input is NULL. - Deterministic: equal inputs produce equal outputs. - Side effect free. - Hashes the UTF-8 encoding of the input string. ## Compatibility - PG-compat alias for the standard SHA-256 primitive.

Parameters

Name	Type	Description
`str`		Specifies the input string to hash. The input is hashed as its UTF-8 byte representation.

Examples

-- Hash a literal
SELECT SHA256('hello');

-- Build a fingerprint column for change detection
SELECT customer_id, SHA256(CONCAT_WS('|', first_name, last_name, email)) AS fingerprint
FROM crm.catalog.customers;

-- Use as a deterministic hash bucket for partitioning
SELECT SUBSTR(SHA256(user_id), 1, 2) AS bucket, COUNT(*)
FROM crm.catalog.customers
GROUP BY bucket;

-- Verify equal inputs produce equal digests
SELECT SHA256('test') = SHA256('test') AS same;  -- true

-- NULL propagation
SELECT SHA256(NULL);  -- NULL

Pitfalls

SHA-256 returns a 64-character hex string, not raw bytes. If you need a shorter bucket id for partitioning, take a prefix with SUBSTR; do not truncate to bytes outside hex boundaries.
SHA-256 is not appropriate for password storage. Use a KDF (Argon2, bcrypt, scrypt) for credentials; SHA-256 is too fast for modern offline attacks.
Hex comparisons are case-sensitive in some contexts. The output is always lowercase hex; if you compare against an externally supplied digest, normalize the other side with LOWER.
NULL propagates: `SHA256(NULL)` is NULL, not a hash of the empty string. Apply `COALESCE(str, '')` upstream if you want a stable digest for missing inputs.