SQL WITHOUT

What is the SQL WITHOUT keyword?

WITHOUT removes or disables a default feature, most notably rowid storage in SQLite (WITHOUT ROWID) and time-zone storage in PostgreSQL (WITHOUT TIME ZONE).

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL WITHOUT: SQLite (WITHOUT ROWID), PostgreSQL (WITHOUT TIME ZONE, WITHOUT OIDS, WITHOUT DATA), SQL Server (ignored), MySQL (ignored), Oracle (ignored)

SQL WITHOUT Full Explanation

WITHOUT is not an ANSI-standard keyword but several SQL dialects reserve it for feature switches. The two most common forms are:1. SQLite – WITHOUT ROWID: Creates a table that does not include the implicit 64-bit rowid column. The primary key is stored in the b-tree root, reducing size and improving some lookups. Only tables that define a PRIMARY KEY can use it.2. PostgreSQL – WITHOUT TIME ZONE: Declares a TIMESTAMP or TIME that stores no time-zone information. Values are kept exactly as written without conversion.Other, less frequent uses include WITHOUT OIDS (deprecated PostgreSQL) and materialized views created WITHOUT DATA. Because semantics differ by engine, always consult the dialect documentation.Caveats:- NOT portable – every engine interprets WITHOUT differently or ignores it.- In SQLite, dropping a WITHOUT ROWID table and recreating it with ROWID requires full data reload.- SQLite still needs a PRIMARY KEY that is UNIQUE and NOT NULL to use WITHOUT ROWID.- In PostgreSQL, arithmetic between WITH TIME ZONE and WITHOUT TIME ZONE values can yield surprising results if the session time zone is changed.

SQL WITHOUT Syntax

-- SQLite
a) CREATE TABLE table_name (
       column_defs,
       PRIMARY KEY(col)
   ) WITHOUT ROWID;

-- PostgreSQL
b) data_type WITHOUT TIME ZONE
c) CREATE MATERIALIZED VIEW view_name AS query WITHOUT DATA;

SQL WITHOUT Parameters

Example Queries Using SQL WITHOUT

-- 1. SQLite: Smaller lookup table
CREATE TABLE country_code (
    iso TEXT PRIMARY KEY,
    name TEXT NOT NULL
) WITHOUT ROWID;

-- 2. PostgreSQL: Store timestamp literals exactly as sent
CREATE TABLE events (
    event_id  BIGSERIAL PRIMARY KEY,
    happened_at TIMESTAMP WITHOUT TIME ZONE
);

-- 3. PostgreSQL: Create an empty materialized view definition
CREATE MATERIALIZED VIEW active_users AS
SELECT id, last_login
FROM users
WHERE last_login >= current_date - INTERVAL '7 days'
WITHOUT DATA;

Expected Output Using SQL WITHOUT

SQLite creates table country_code without the hidden rowid column.
PostgreSQL creates table events; happened_at will not be converted to or from any zone.
PostgreSQL registers the materialized view but leaves it empty until REFRESH MATERIALIZED VIEW is run.

Use Cases with SQL WITHOUT

Performance-critical lookup tables in SQLite that benefit from smaller on-disk size.
Strict auditing of literal timestamps in PostgreSQL where time-zone shifts must be avoided.
Defining materialized views first and populating them later to avoid lengthy initial refreshes.

Common Mistakes with SQL WITHOUT

Assuming WITHOUT is interchangeable across databases.
Forgetting to declare a PRIMARY KEY in SQLite WHEN using WITHOUT ROWID.
Expecting PostgreSQL to auto-convert TIMESTAMP WITHOUT TIME ZONE values when the session zone changes.
Using WITHOUT ROWID on virtual or temporary tables in SQLite, which is not allowed.

First Introduced In

SQLite 3.8.2, PostgreSQL 7.3

Frequently Asked Questions

What is the difference between TIMESTAMP WITH TIME ZONE and WITHOUT TIME ZONE?

WITH TIME ZONE stores the moment in absolute UTC and converts for display, while WITHOUT TIME ZONE stores the literal text unchanged. Use WITH when you track absolute points in time; use WITHOUT for local or display-only timestamps.

Does WITHOUT ROWID improve performance in all cases?

Mostly on tables where the primary key is frequently used for lookups and inserts are modest. For write-heavy tables with many secondary indexes, the benefit may be marginal compared to classic rowid tables.

Can I alter a table to add or remove WITHOUT ROWID?

SQLite does not support ALTER TABLE for this switch. You must create a new table with the desired setting, copy data over, and rename.

Is WITHOUT a reserved keyword in every SQL engine?

No. Some engines treat it as fully reserved, others allow it as an identifier, and many ignore it unless paired with a specific phrase like WITHOUT ROWID.