To change an integer flag to boolean in PostgreSQL, run ALTER TABLE users ALTER COLUMN is_active TYPE boolean USING is_active <> 0, but only after removing what ties the column to integers, such as its default, a CHECK (is_active IN (0, 1)), or a partial index or view with WHERE is_active = 1. Each one stops the statement with its own error, and the CHECK and the partial index fail with the same error, which names neither of them.

That covers the statement. The decision is whether to run it in place at all. The type change rewrites the whole table under an ACCESS EXCLUSIVE lock, and the moment it commits, a query that still sends the integer 1 fails. On a table with millions of rows, or an application deployed separately from its migrations, adding a new boolean column and swapping it in is the safer route. Everything below was run on PostgreSQL 18.3 in a throwaway container.

Why integer flags end up needing this migration

Integer flags usually come from somewhere else: a schema ported from MySQL, where BOOLEAN is an alias for TINYINT(1), a Rails app that started on MySQL, or a column someone added as smallint years ago “in case we need a third state”. It works, and it stays that way until someone wants the column to read like what it is. Here is the table used in every example:

CREATE TABLE users (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL,
    is_active integer NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1))
);
CREATE INDEX users_active_email_idx ON users (email) WHERE is_active = 1;
CREATE VIEW active_users AS SELECT id, email FROM users WHERE is_active = 1;

How do I change a column from integer to boolean in Postgres?

Try the obvious statement and PostgreSQL refuses four times, once for the missing cast and then once per object in the way:

The first error is the missing USING clause: there is no automatic conversion from integer to boolean, so you have to say how. The second is the one that surprises people, because the USING clause is right. PostgreSQL converts the column’s default separately and does not apply your USING expression to it, so DEFAULT 1 has no way to become a boolean. The third is the view, covered in detail in changing a column type used by a view.

The fourth comes from both the CHECK constraint and the partial index. PostgreSQL rebuilds each of them against the new type, and is_active IN (0, 1) and WHERE is_active = 1 now compare a boolean with an integer. The error does not name the constraint or the index, and dropping only the CHECK produces the same message from the index.

The migration that works drops every one of them, changes the type, and puts back boolean versions:

BEGIN;
DROP VIEW active_users;
DROP INDEX users_active_email_idx;
ALTER TABLE users DROP CONSTRAINT users_is_active_check;
ALTER TABLE users ALTER COLUMN is_active DROP DEFAULT;
ALTER TABLE users ALTER COLUMN is_active TYPE boolean USING is_active <> 0;
ALTER TABLE users ALTER COLUMN is_active SET DEFAULT true;
CREATE INDEX users_active_email_idx ON users (email) WHERE is_active;
CREATE VIEW active_users AS SELECT id, email FROM users WHERE is_active;
COMMIT;

The CHECK does not come back: a boolean column already holds only two values, which is the reason for the migration. A view also loses its grants when dropped, so restore those after recreating it.

USING is_active::boolean or is_active <> 0?

Use is_active <> 0. PostgreSQL has a cast from integer to boolean, but none from smallint or bigint, so a smallint column such as is_admin cannot use the cast form:

SELECT 1::smallint::boolean;
-- ERROR:  cannot cast type smallint to boolean

So USING is_admin::boolean works on an integer column and fails on a smallint one. Comparing with zero works on every width, and it gives the same answer as the integer cast: 0 becomes false and any other value, including 2 and -1, becomes true. If your data has values other than 0 and 1, decide what they mean before the migration, because after it they are indistinguishable from 1.

ORMs write the cast form. Django’s PostgreSQL backend appends USING "is_active"::boolean whenever a field’s database type changes, so turning an IntegerField into a BooleanField works, and turning a SmallIntegerField into one fails with the error above; write that migration as RunSQL. Rails’ change_column :users, :is_active, :boolean emits no USING at all unless you pass using: "is_active <> 0", or cast_as: :boolean, which writes USING CAST(... AS boolean) and has the same smallint problem. Passing default: true in the same call does not help: Rails adds SET DEFAULT true to the same ALTER TABLE, and PostgreSQL 18.3 still failed on the old default. default: nil does work: the DROP DEFAULT it adds to the same statement runs before the type change, and change_column_default can set true afterwards.

How to find everything that depends on the column

The error messages arrive one at a time, so list the dependents first. The objects PostgreSQL tracks are recorded in pg_depend against the column’s attribute number, and pg_describe_object names each one:

SELECT DISTINCT pg_describe_object(d.classid, d.objid, d.objsubid) AS dependent
FROM pg_depend d
JOIN pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
WHERE d.refclassid = 'pg_class'::regclass
  AND d.refobjid = 'users'::regclass
  AND a.attname = 'is_active'
ORDER BY 1;

On the example table it returned the partial index, the column default, users_is_active_check, the view, and on PostgreSQL 18 also users_is_active_not_null, the constraint 18 now records for NOT NULL. That one survives the type change untouched. Everything else on the list needs a boolean rewrite or a drop, and so would a row-level security policy or extended statistics on the column, which show up here too. Views built on top of active_users do not appear, since they depend on the view rather than the column; walk them with the recursive query in the view post linked above. Two kinds of code are not tracked at all: a PL/pgSQL function body with WHERE is_active = 1, which lets the type change through and fails the next time it runs, and your application’s queries.

Does converting the column lock the table?

Yes, for the whole rewrite. integer and boolean are stored differently, so PostgreSQL writes a new copy of the table and rebuilds every index on it, holding ACCESS EXCLUSIVE the entire time: no reads, no writes. On 5,000,000 rows on a laptop, with emails like [email protected], the TYPE change took 15.4 seconds and recreating the partial index another 4.4. Nothing could read users for those 20 seconds.

It does not buy much space either. The table itself, without its indexes, went from 365 MB to 357 MB, because a 4-byte integer and a 1-byte boolean often land in the same aligned row slot. The reason to migrate is a column that says what it means, not storage.

The other cost arrives at commit. Once the column is boolean, the old application code fails on every query that sends a number:

SELECT count(*) FROM users WHERE is_active = 1;
-- ERROR:  operator does not exist: boolean = integer

INSERT INTO users (email, is_active) VALUES ('[email protected]', 1);
-- ERROR:  column "is_active" is of type boolean but expression is of type integer

A literal '1' in quotes still works, because PostgreSQL reads an untyped string as a boolean. A parameter typed as an integer does not: PREPARE q(int) AS SELECT count(*) FROM users WHERE is_active = $1 fails the same way. Whether your application hits this depends on the driver, since some send parameters as typed integers and some as untyped text, so test it rather than assume. Either way, the in-place change and the application change have to ship as one.

In place, new column, or keep the integer?

When the table is large, or the application deploys separately from its migrations, add the boolean as a new column and swap it in at the end:

ALTER TABLE users ADD COLUMN active boolean;
ALTER TABLE users ALTER COLUMN active SET DEFAULT true;

-- keep the new column in step with whatever the old code writes
CREATE FUNCTION users_sync_active() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NEW.active := NEW.is_active <> 0;
    RETURN NEW;
END $$;
CREATE TRIGGER users_sync_active BEFORE INSERT OR UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION users_sync_active();

-- backfill in batches, with VACUUM users between them
UPDATE users SET active = (is_active <> 0) WHERE id BETWEEN 1 AND 100000;
CREATE INDEX CONCURRENTLY users_active_email_idx_new ON users (email) WHERE active;
ALTER TABLE users ADD CONSTRAINT users_active_not_null NOT NULL active NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_active_not_null;

SET lock_timeout = '2s';
BEGIN;
DROP TRIGGER users_sync_active ON users;
DROP FUNCTION users_sync_active();
DROP VIEW active_users;
ALTER TABLE users DROP COLUMN is_active;
ALTER TABLE users RENAME COLUMN active TO is_active;
ALTER TABLE users RENAME CONSTRAINT users_active_not_null TO users_is_active_not_null;
ALTER INDEX users_active_email_idx_new RENAME TO users_active_email_idx;
CREATE VIEW active_users AS SELECT id, email FROM users WHERE is_active;
COMMIT;

The trigger matters: without it, old code inserting is_active = 0 would get active = true from the default, and rows it updates after their batch would go stale. The slow parts, the backfill and the index build, run without blocking readers, but each batch writes a new version of every row it touches, so the table grows until VACUUM catches up, and the dropped column’s space is only reclaimed by a later rewrite. The final transaction took about 60 ms on the same 5,000,000 rows, since dropping a column and renaming are catalogue changes, and the old column’s CHECK and partial index go with it. The NOT NULL ... NOT VALID form is PostgreSQL 18; on 12 to 17, prove it with a CHECK constraint as described in adding a NOT NULL column to a large table. What this route shortens is the lock, not the cut-over. After the swap, is_active is boolean, so code that still sends 1 fails exactly as it would in place, and code already moved to active loses that name. The simplest version keeps the new name: move the application to active, then drop is_active and skip the renames.

Keeping the integer is a real option. With CHECK (is_active IN (0, 1)) the database enforces the same two values, and if a third state is plausible, an enum or a lookup table is the better target than boolean anyway.

How Schemity plans and reviews the change

The point of the table above is to see the impact of every change before it runs, and none of it is visible in a one-line migration. Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git.

Change is_active from INTEGER to BOOLEAN in a diagram connected to the database, and the field dialog clears the 1 default, because it is not a valid boolean, and offers TRUE and FALSE in its place.

Saving the field also removes users_is_active_check, because 0 and 1 are not values a boolean can hold. The migration SQL it plans drops that constraint and the old default first, converts with USING "is_active" <> 0, which works on SMALLINT and BIGINT columns too, and sets the new default last.

Impact analysis then reports the cost before anything runs: a type change whose values may not survive the cast, a rewrite of users with its row estimate and size on disk, the lock that holds back reads and writes while it happens, and active_users as a view the database will refuse the change for.

Two limits are worth knowing. Impact analysis lists views, materialized views, triggers, functions and generated columns that read the column, but not a partial index whose predicate compares it with a number, so run the pg_depend query above as well. And Schemity’s own migration is the in-place route without dropping the view or the index, so on this table the planned SQL still stops at both. For a large table, use the findings as the cue to write the new-column version in your migration tool, then paste it into the SQL migration drawer to have it analysed against the connected database without running it.

Which migration to write

• Small table, application and migration deployed together: in place, in one transaction, with the default dropped first and USING col <> 0.
• Large or busy table, or separate deploys: a new boolean column kept in sync by a trigger, batched backfill, an index built concurrently, and a swap of about 60 ms.
• A flag that might grow a third value: keep the integer with its CHECK, or move to an enum, not to boolean.