# PostgreSQL: Changing an Integer Column to Boolean - In Place or a New Column, and the Four Errors on the Way

> Change it in place with `ALTER COLUMN ... TYPE boolean USING is_active <> 0`, after dropping the integer default and anything that compares the column with a number: a `CHECK`, a partial index, a view. The in-place change rewrites the table under a lock that blocks reads, and any query that still binds the integer `1` fails the moment it commits. On a large or busy table, add a new boolean column, keep it in sync while you backfill it, and switch over to it in a transaction that takes milliseconds.

Source: https://schemity.com/blog/postgres-change-integer-column-to-boolean/

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:

```sql
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:

| Statement | Error |
|---|---|
| `ALTER COLUMN is_active TYPE boolean` | `column "is_active" cannot be cast automatically to type boolean` |
| `... TYPE boolean USING is_active::boolean` | `default for column "is_active" cannot be cast automatically to type boolean` |
| The same, after `DROP DEFAULT` | `cannot alter type of a column used by a view or rule` |
| The same, after `DROP VIEW active_users` | `operator does not exist: boolean = integer` |

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](https://schemity.com/blog/postgres-alter-column-type-used-by-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:

```sql
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:

```sql
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:

```sql
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 `user123@example.com`, 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:

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

INSERT INTO users (email, is_active) VALUES ('a@example.com', 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:

```sql
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](https://schemity.com/blog/postgres-add-not-null-column-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.

| | In place | New column and swap | Keep the integer |
|---|---|---|---|
| Lock | `ACCESS EXCLUSIVE` for the rewrite, 20 s on 5M rows here | About 60 ms at the swap | None |
| Old application code | Fails at commit | Keeps working until the cut-over | Unaffected |
| Steps | One transaction | Backfill, dual writes, swap | None |
| Fits | Small tables, one deploy for app and schema | Large or busy tables | A column that may need a third state |

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](https://schemity.com/blog/postgres-enum-vs-check-constraint-vs-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.

![Schemity's field dialog for users.is_active after its type is switched from INTEGER to BOOLEAN: the Default dropdown is open with No default, TRUE and FALSE, and the integer default 1 is gone; behind it the canvas still shows is_active as INTEGER with default 1 and one check constraint, next to the active_users view](https://schemity.com/images/blog/postgres-change-integer-column-to-boolean/field-dialog-boolean-default.webp)

Saving the field also removes `users_is_active_check`, because `0` and `1` are not values a boolean can hold. The [migration SQL](https://schemity.com/doc/migration-sql-diff/) 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.

![Schemity's Findings view for is_active changed from INTEGER to BOOLEAN with default TRUE: the planned migration drops users_is_active_check, drops the default, alters the column TYPE BOOLEAN USING "is_active" <> 0 and sets DEFAULT TRUE; planned changes list the altered column and the dropped check constraint, lint has no findings, and impact has 4](https://schemity.com/images/blog/postgres-change-integer-column-to-boolean/preview-integer-to-boolean.webp)

[Impact analysis](https://schemity.com/doc/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.

![Schemity's Impact drawer with 4 findings for the same change: values of users.is_active may not survive the cast on ~5M rows, a rewrite of users of ~5M rows and 695 MB on disk, the type change holding back reads and writes to users, and the view active_users depending on users.is_active so the database refuses the change; the canvas shows users with is_active BOOLEAN TRUE next to the active_users view](https://schemity.com/images/blog/postgres-change-integer-column-to-boolean/impact-integer-to-boolean.webp)

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](https://schemity.com/blog/postgres-unique-constraint-vs-unique-index/), 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.

## Frequently asked questions

### How do I convert a smallint column to boolean in PostgreSQL?

Use `ALTER TABLE users ALTER COLUMN is_admin TYPE boolean USING is_admin <> 0`. PostgreSQL has a cast from `integer` to `boolean` but none from `smallint` or `bigint`, so `USING is_admin::boolean` fails with `cannot cast type smallint to boolean`. Comparing with zero works for every integer width and maps every non-zero value to true, which is what the `integer` cast does.

### Why does ALTER COLUMN TYPE boolean fail with default for column cannot be cast automatically?

The column still has its integer default, such as `DEFAULT 1`, and PostgreSQL converts the default as part of the type change without your `USING` expression. There is no automatic conversion from `integer` to `boolean`, so the statement fails. Run `ALTER COLUMN ... DROP DEFAULT` first, change the type, then `SET DEFAULT true`.

### Will my application break after converting the column to boolean?

Any query that sends the flag as a number will. After the change, `WHERE is_active = 1` fails with `operator does not exist: boolean = integer`, and an insert of the integer `1` fails with `column is_active is of type boolean but expression is of type integer`. A quoted `'1'` still works, and whether a bound parameter fails depends on whether your driver sends it as a typed integer, so test it. Update the application in the same release, or move it to a new boolean column before the old one is dropped.