How to Use Bit String Data Types in PostgreSQL

Bit strings don’t come up in day-to-day application development nearly as often as text or numeric types, but when you need them, nothing else really substitutes. PostgreSQL’s bit string types let you store and manipulate raw sequences of binary digits — 1s and 0s — directly in the database, with proper type safety and a full set of bitwise operators. This article covers everything from the basic syntax to real use cases like permission flags, hardware identifiers, and fixed-width binary encodings.

What Are Bit String Data Types?

PostgreSQL provides two bit string types:

• bit(n) — a fixed-length bit string of exactly n bits. Every value stored must be exactly that length; shorter values get padded (in some contexts) or rejected, and longer values will error out or be truncated depending on how you’re inserting them.
• bit varying(n), often written varbit(n) — a variable-length bit string that can hold anywhere from 0 up to n bits.

Both types store literal sequences of 0s and 1s — not integers, not booleans, and not byte arrays like bytea. A bit string is its own distinct thing, with its own comparison and manipulation operators.

Basic Syntax

CREATE TABLE bit_examples (
    id serial PRIMARY KEY,
    fixed_flag bit(8),
    variable_flag bit varying(16)
);

If you omit the length for bit varying, it can hold a string of any length. Omitting the length for plain bit defaults to bit(1).

CREATE TABLE minimal_bits (
    single_bit bit,          -- equivalent to bit(1)
    flexible_bits bit varying -- unlimited length
);

Bit String Literals

Bit string literals use a B prefix followed by the binary digits in single quotes:

SELECT B'1010'::bit(4);
-- 1010

SELECT B'101'::bit(8);
-- 10100000   (padded with zeros on the right to reach length 8)

Note that behavior carefully: when you cast a bit string literal to a longer fixed-length bit(n), PostgreSQL pads with zeros on the right, not the left. This is a common source of confusion for people expecting numeric-style left-padding.

INSERT INTO bit_examples (fixed_flag) VALUES (B'11001100');
INSERT INTO bit_examples (variable_flag) VALUES (B'101');

If you try to insert a value longer than the declared length for bit(n), Postgres will raise an error rather than silently truncating:

SELECT B'111111111'::bit(8);
-- ERROR:  bit string length 9 does not match type bit(8)

For bit varying(n), values longer than n are rejected too, but shorter values are simply stored as-is without padding.

Converting Between Bit Strings and Other Types

You can cast between bit strings and integers directly, which is one of the more genuinely useful features here:

SELECT 5::bit(8);
-- 00000101

SELECT B'00000101'::integer;
-- 5

This makes bit strings a convenient way to visualize or manipulate the binary representation of an integer, or to pack a fixed-width integer value into a binary column layout.

Casting between bit(n) and bit varying(n) is also straightforward:

SELECT B'1010'::bit(4)::bit varying(8);
-- 1010

Bitwise Operators

PostgreSQL supports the full standard set of bitwise operators on bit strings:

SELECT B'1100' & B'1010';   -- AND -> 1000
SELECT B'1100' | B'1010';   -- OR  -> 1110
SELECT B'1100' # B'1010';   -- XOR -> 0110
SELECT ~B'1100';            -- NOT -> 0011  (for bit(4))

Bit shifting:

SELECT B'10000000' << 2;    -- left shift  -> 00000000
SELECT B'00000001' >> 1;    -- right shift -> 00000000 (for bit(8), shifted out bits are lost)

Concatenation:

SELECT B'1010' || B'0101';
-- 10100101

One important rule: for AND, OR, and XOR, both operands generally need to be the same length, or Postgres will raise an error. Explicit casting to a common length resolves this:

SELECT B'101'::bit(8) & B'11001100'::bit(8);

Length and Bit-Counting Functions

SELECT length(B'10110');          -- 5
SELECT bit_length(B'10110');      -- 5
SELECT octet_length(B'10110000'); -- 1

Counting set bits (population count) is available via bit_count in modern PostgreSQL versions:

SELECT bit_count(B'10110101');
-- 5

For older versions without bit_count, you can approximate this with a cast through bytea and manual computation, but if you’re on PostgreSQL 14+ the built-in function is by far the cleanest approach.

Practical Use Cases

1. Compact Permission and Feature Flags

Bit strings are a natural fit for storing a fixed set of boolean flags in a compact form, where each bit position represents one permission or feature:

CREATE TABLE user_permissions (
    user_id integer PRIMARY KEY,
    -- bit 0: read, bit 1: write, bit 2: delete, bit 3: admin
    permissions bit(4) NOT NULL DEFAULT B'0000'
);

-- Grant read and write to user 1
UPDATE user_permissions
SET permissions = permissions | B'0011'
WHERE user_id = 1;

-- Check if user has delete permission (bit 2)
SELECT (permissions & B'0100') = B'0100' AS can_delete
FROM user_permissions
WHERE user_id = 1;

In practice, many teams prefer boolean columns or an integer bitmask for readability, but genuine bit-string columns are useful when you need to enforce a fixed width at the schema level or when working with data that’s inherently bit-oriented, like protocol flags mirrored from an external binary format.

2. Fixed-Width Binary Codes

Some domains — telecom, networking hardware, industrial protocols — represent identifiers or status codes as raw binary sequences of a known fixed width. Storing these as bit(n) preserves the exact binary layout without any ambiguity that could come from storing them as integers with implicit sign or endianness assumptions.

CREATE TABLE device_status_codes (
    device_id integer,
    status_code bit(16) NOT NULL
);

3. Masking and Filtering Operations

Bitwise AND against a mask is a common pattern for checking whether specific flags are set, especially when migrating logic from systems that already used bitmasking (many legacy applications and hardware interfaces do):

SELECT status_code & B'0000000000000111' AS relevant_flags
FROM device_status_codes
WHERE device_id = 42;

Bit Strings vs. Alternatives

It’s worth being honest about when bit strings are the right tool versus when something else is a better fit:

• Boolean columns are almost always more readable for a small, fixed number of independent flags where you’ll query each flag individually. is_admin boolean, can_delete boolean — clear, indexable, and self-documenting.
• Integer bitmasks (a plain integer used with bitwise operators) are more common in application code and are easier to work with from most programming languages, though they lose the strict fixed-width guarantee that bit(n) gives you at the schema level.
• bytea is the better choice for arbitrary binary blobs (files, hashes, encrypted payloads) where you’re not doing bit-level logical operations.
• Bit string types earn their place specifically when you need exact bit-level control, fixed-width enforcement at the schema level, or you’re mirroring an external binary protocol precisely.

Indexing Considerations

Bit string columns can be indexed with a standard B-tree index like most other types:

CREATE INDEX idx_status_code ON device_status_codes (status_code);

This supports equality and ordering comparisons efficiently. However, if your primary query pattern is “does this bit-mask match this pattern” (bitwise AND against a mask), a plain B-tree index won’t help with that kind of partial match — you’d need to structure separate boolean or indexed columns for the specific flags you filter on frequently, or consider a different indexing strategy such as expression indexes on extracted bits.

Troubleshooting Common Issues

“Bit string length does not match type” errors. This is the most common error with bit(n) columns — the value you’re inserting isn’t exactly n bits long. Explicitly cast or pad the value before inserting, and remember padding happens on the right, not the left.

Unexpected results from bitwise operators on mismatched lengths. AND/OR/XOR require matching lengths. Cast both operands to a shared length explicitly rather than relying on implicit behavior.

Confusing right-padding with left-padding. When casting a shorter bit string to a longer fixed length, Postgres pads zeros on the right. If you actually want the equivalent of a zero-padded integer representation (padding on the left), cast through an integer instead: lpad_value::bit(n) won’t do what you expect — cast the source integer to bit(n) directly.

Confusing bit strings with bytea. They look similar conceptually but are entirely different types with different literal syntax (B'...' vs E'\\x...' or '\x...'), different functions, and different casting rules. Don’t expect functions written for bytea to work on bit strings or vice versa.

Best Practices

• Use bit(n) when the width is truly fixed and meaningful (a defined-length protocol field); use bit varying(n) when the width can vary but has a sensible upper bound.
• Prefer boolean columns for a small, static set of independently-queried flags — reach for bit strings when you specifically need bit-level operations or a fixed binary layout.
• Always specify explicit lengths in casts and comparisons to avoid the “length mismatch” class of errors.
• Document what each bit position means directly in your schema comments (COMMENT ON COLUMN ...), since bit positions are not self-explanatory the way named boolean columns are.
• If bit-level filtering is a frequent query pattern and performance matters, benchmark against splitting into separate indexed boolean columns — it’s often faster and easier to reason about at scale.

Working with Bit Strings in Functions

Bit manipulation logic often ends up inside PL/pgSQL functions, especially when you’re implementing something like a permission check that needs to be reused across many queries rather than repeated inline everywhere.

CREATE OR REPLACE FUNCTION has_permission(p_permissions bit(8), p_bit_position integer)
RETURNS boolean AS $$
DECLARE
    mask bit(8);
BEGIN
    mask := (1::bit(8)) << (8 - p_bit_position - 1);
    RETURN (p_permissions & mask) = mask;
END;
$$ LANGUAGE plpgsql;

SELECT has_permission(B'10110000', 1);
-- checks whether bit position 1 (0-indexed from the left) is set -> true

Wrapping bit logic in a function like this means the bit-position arithmetic lives in exactly one place, rather than being copy-pasted (and potentially miscounted) across every query that needs to check a flag.

Combining Bit Strings with Generated Columns

Modern PostgreSQL supports generated columns, which pair nicely with bit strings when you want a readable boolean derived from a packed bit field without repeating the extraction logic everywhere:

CREATE TABLE feature_flags (
    account_id integer PRIMARY KEY,
    flags bit(8) NOT NULL DEFAULT B'00000000',
    beta_enabled boolean GENERATED ALWAYS AS ((flags & B'00000001') = B'00000001') STORED
);

Now beta_enabled reads as a normal, self-explanatory boolean column in every query, while the underlying storage stays compact as a packed bit field. This is a genuinely nice middle ground when you want the storage efficiency of bit packing but the readability of named boolean columns for the fields you query most often.

Bit Strings and Binary Protocol Interop

One place bit strings earn their keep in real production systems is when a Postgres database needs to store data that mirrors an external binary protocol exactly — telecom signaling data, industrial control system status words, or hardware register dumps are common examples. In these cases, the exact bit layout matters, and converting to and from integers can introduce subtle bugs around sign extension or byte ordering that bit strings avoid entirely, since they represent the raw bit pattern with no numeric interpretation attached.

CREATE TABLE plc_status_words (
    reading_id serial PRIMARY KEY,
    device_id integer NOT NULL,
    status_word bit(16) NOT NULL,
    recorded_at timestamptz NOT NULL DEFAULT now()
);

-- Extract specific status flags by bit position
SELECT device_id,
       substring(status_word FROM 1 FOR 1) = B'1' AS running,
       substring(status_word FROM 2 FOR 1) = B'1' AS fault,
       substring(status_word FROM 3 FOR 1) = B'1' AS maintenance_mode
FROM plc_status_words
ORDER BY recorded_at DESC
LIMIT 10;

The substring() function works on bit strings the same way it does on text, letting you pull out specific bit ranges by position — genuinely useful when a single status word packs multiple independent flags and multi-bit fields together, as is common in real hardware status registers.

Converting Bit Strings for Reporting and Display

Raw bit strings aren’t especially readable in a report or dashboard, so it’s common to build a small helper that turns a packed bit field into a human-friendly list of active flags:

CREATE TABLE feature_bits (
    bit_position integer PRIMARY KEY,
    feature_name text NOT NULL
);

INSERT INTO feature_bits VALUES
(0, 'dark_mode'), (1, 'beta_access'), (2, 'advanced_search'), (3, 'export_tools');

CREATE OR REPLACE FUNCTION describe_flags(p_flags bit(8))
RETURNS text[] AS $$
DECLARE
    result text[] := '{}';
    fb record;
BEGIN
    FOR fb IN SELECT * FROM feature_bits ORDER BY bit_position LOOP
        IF substring(p_flags FROM fb.bit_position + 1 FOR 1) = B'1' THEN
            result := array_append(result, fb.feature_name);
        END IF;
    END LOOP;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

SELECT describe_flags(B'10100000');
-- {dark_mode,advanced_search}

This kind of translation function is genuinely worth writing once a bit-flag column becomes something non-technical stakeholders need to read — a raw 10100000 means nothing to a support agent, but {dark_mode,advanced_search} does.

Bit Strings vs. Integer Bitmasks in Application Code

It’s worth being explicit about a practical trade-off many teams run into: application languages generally work with bitmasks as plain integers (using &, |, ^, <<, >> operators that most languages already support natively on integer types), not as bit(n) values. If your bit-flag logic largely lives in application code and the database is just persisting the result, storing the value as a plain integer with the same bitwise semantics is often simpler end-to-end — you avoid a type conversion at the application/database boundary, and most ORMs and drivers have no native concept of a Postgres bit type, whereas mapping an integer bitmask is completely ordinary.

Where bit(n) genuinely earns its place over a plain integer bitmask is when the fixed width itself is a meaningful constraint you want the database to enforce (protecting against a mistakenly oversized value being stored), or when you’re directly mirroring a binary protocol where the bit-level representation, not its integer interpretation, is the thing that matters. If neither of those applies, a plain integer column with documented bit meanings is often the more practical, more portable choice.

A Quick Reference for Common Bit Operations

Keeping a small cheat sheet handy is genuinely useful once you’re working with bit strings regularly, since the operator symbols aren’t always self-explanatory at a glance:

-- Setting a specific bit to 1 (e.g., bit position 3 in an 8-bit string)
SELECT set_bit(B'00000000', 3, 1);
-- 00010000

-- Clearing a specific bit
SELECT set_bit(B'11111111', 3, 0);
-- 11101111

-- Reading a specific bit's value
SELECT get_bit(B'10110000', 1);
-- 0

set_bit() and get_bit() are often more readable than manually constructing masks with shift and AND/OR operators, especially for one-off bit manipulation in ad-hoc queries or simple scripts, and they’re worth defaulting to unless you specifically need the composability that raw bitwise operators give you inside larger expressions.

Wrapping Up

Bit string types are a specialized tool, and most PostgreSQL developers will go a long time without needing them directly. But for the specific cases where you genuinely need exact, fixed-width binary data with real bitwise operations — permission flags mirrored from an external system, protocol fields, or hardware status codes — bit and bit varying give you precise, type-safe control that plain integers or booleans can’t fully replicate. Know when to reach for them, and just as importantly, know when a simpler boolean column will serve you better.