> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Differences from PostgreSQL

> Compatibility matrix of Firebolt SQL against PostgreSQL, covering data types, query syntax, functions, statements, and the system catalog, with the differences that change query results listed first.

Firebolt SQL is modeled after PostgreSQL (see [SQL reference](/reference-sql)). This page lists only where the two differ; anything not mentioned works as in PostgreSQL. The comparison is against PostgreSQL 18 with default settings: `DateStyle = 'ISO, MDY'` and a database created with the `en_US.UTF-8` locale.

Tables on this page use these statuses:

| Status | Meaning |
| :- | :- |
| **Different behavior** | Runs without an error but returns a different result for common input. |
| Different return type | Works, but returns a different data type. |
| With limitations | Some forms raise an error or behave differently; the notes say which. |
| Extended | Also accepts input that PostgreSQL rejects. |
| Supported | Works as in PostgreSQL, with a minor difference noted. |
| Not supported | Raises an error. |

## Key differences

These differences change query results, mostly without raising an error. Check them first when you migrate a workload.

<AccordionGroup>
  <Accordion title="Decimal literals are DOUBLE PRECISION, not NUMERIC">
    ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
    SELECT 0.1 + 0.2 = 0.3;  -- PostgreSQL: true   Firebolt: false
    SELECT round(2.5);       -- PostgreSQL: 3      Firebolt: 2
    SELECT 2.5::INT;         -- PostgreSQL: 3      Firebolt: 2
    ```

    Write exact values as `NUMERIC '1.5'` or `'1.5'::NUMERIC(10, 2)`.
  </Accordion>

  <Accordion title="NUMERIC defaults to NUMERIC(38, 9), and NUMERIC(p) has scale min(p, 9)">
    ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
    SELECT 1.5::NUMERIC(12);  -- PostgreSQL: 2     Firebolt: 1.500000000
    SELECT 123::NUMERIC(5);   -- PostgreSQL: 123   Firebolt: error
    ```

    Always specify precision and scale, for example `NUMERIC(12, 0)`.
  </Accordion>

  <Accordion title="NUMERIC multiplication, division, and avg keep the input scale">
    ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
    SELECT 1.25::NUMERIC(10, 2) * 1.25::NUMERIC(10, 2);
    -- PostgreSQL: 1.5625   Firebolt: 1.56
    SELECT 2::NUMERIC(10, 2) / 3;
    -- PostgreSQL: 0.66666666666666666667   Firebolt: 0.67
    ```

    Cast the operands to a larger scale first.
  </Accordion>

  <Accordion title="avg of integers returns DOUBLE PRECISION, and extract returns INTEGER">
    ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
    SELECT extract(year FROM DATE '2024-05-01') / 10;
    -- PostgreSQL: 202.4   Firebolt: 202
    ```

    `avg` over `BIGINT` values above 2^53 loses precision. Cast to `NUMERIC` where the exact result matters.
  </Accordion>

  <Accordion title="Hexadecimal, octal, and binary literals are read as 0 with a column alias">
    ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
    SELECT 0x1F;  -- PostgreSQL: 31   Firebolt: 0, in a column named x1f
    ```

    Use decimal literals.
  </Accordion>

  <Accordion title="Text sorts by byte value, as under the PostgreSQL C collation">
    ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
    SELECT 'a' < 'B';  -- PostgreSQL: true   Firebolt: false
    SELECT x FROM (VALUES ('b'), ('B'), ('a'), ('A')) t(x) ORDER BY x;
    -- PostgreSQL: a A b B   Firebolt: A B a b
    ```

    `COLLATE` is not supported. The same order affects `min`, `max`, `greatest`, and `least`.
  </Accordion>

  <Accordion title="VARCHAR(n) and CHAR(n) ignore the length and don't pad">
    ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
    SELECT 'abcdef'::VARCHAR(3);              -- PostgreSQL: abc    Firebolt: abcdef
    SELECT 'ab'::CHAR(5) = 'ab   '::CHAR(5);  -- PostgreSQL: true   Firebolt: false
    ```

    Truncate with `substr` and trim trailing blanks with `rtrim`.
  </Accordion>

  <Accordion title="DATE and TIMESTAMP cover a narrower range">
    `DATE` spans `0001-01-01` to `9999-12-30`, and `TIMESTAMP` and `TIMESTAMPTZ` end at `9999-12-30 22:00:00.999999`. PostgreSQL accepts years from 4713 BC to 294276 AD for timestamps and to 5874897 AD for dates. Values outside the Firebolt range raise an error, including the common end-of-time value:

    ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
    SELECT '9999-12-31'::DATE;  -- PostgreSQL: 9999-12-31   Firebolt: error
    ```
  </Accordion>

  <Accordion title="Arrays are nested, not multidimensional">
    ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
    SELECT (ARRAY[[1, 2], [3, 4]])[2];         -- PostgreSQL: NULL     Firebolt: {3,4}
    SELECT count(*) FROM unnest(ARRAY[[1, 2], [3, 4]]);
    -- PostgreSQL: 4   Firebolt: 2
    ```

    Inner arrays can have different lengths. Index with `a[i][j]` to reach an element.
  </Accordion>

  <Accordion title="JSON and JSONB are one type, normalized on input">
    ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
    SELECT '{"b":1,"a":2}'::JSON;  -- PostgreSQL: {"b":1,"a":2}   Firebolt: {"a":2,"b":1}
    ```

    To keep the original text, store the document as `TEXT` and apply JSON functions to it.
  </Accordion>

  <Accordion title="UNIQUE and PRIMARY KEY are not enforced, but the optimizer trusts them">
    ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
    CREATE TABLE t (u INT UNIQUE);
    INSERT INTO t VALUES (1), (1);              -- PostgreSQL: error   Firebolt: 2 rows stored
    SELECT count(*) FROM (SELECT DISTINCT u FROM t) s;  -- Firebolt: 1
    SELECT count(DISTINCT u) FROM t;                    -- Firebolt: 2
    ```

    Declare a constraint only when your loads guarantee it.
  </Accordion>

  <Accordion title="TEMPORARY tables are ordinary tables">
    PostgreSQL keeps a temporary table private to the session and drops it when the session ends. Firebolt creates an ordinary table that every session sees and that is never dropped automatically. Use a regular table with a unique name and drop it explicitly.
  </Accordion>

  <Accordion title="Views bind when queried, and schema changes don't check them">
    A `SELECT *` view returns columns added to the table after the view was created. `ALTER TABLE ... DROP COLUMN` succeeds when a view uses the column, and the view then fails when queried; PostgreSQL refuses the drop. List view columns explicitly.
  </Accordion>

  <Accordion title="Only snapshot isolation, with conflicts detected at COMMIT">
    PostgreSQL defaults to `READ COMMITTED`: a second writer waits on the row lock and then succeeds. Firebolt has no locks; the first transaction to commit wins, and a later conflicting transaction fails at `COMMIT`. Retry the transaction on a conflict.
  </Accordion>
</AccordionGroup>

## Data types

These types work as in PostgreSQL: `integer`, `bigint`, `real`, `double precision`, `bytea`, and `boolean`. `real` and `double precision` print `inf`, `-inf`, and `nan` instead of `Infinity`, `-Infinity`, and `NaN`, and switch to exponent notation at 1e21 and 1e-6 rather than at PostgreSQL's thresholds. See [Data types](/reference-sql/data-types) for the Firebolt types.

| PostgreSQL type | Status | Notes |
| :- | :- | :- |
| `numeric`, `decimal` | **Different behavior** | `NUMERIC` is `NUMERIC(38, 9)` and `NUMERIC(p)` has scale `min(p, 9)`. Precision is at most 38. No `NaN`, `Infinity`, or negative scale. Operations on two values with different precision and scale raise an error. See [NUMERIC](/reference-sql/data-types/numeric). |
| `varchar(n)` | **Different behavior** | Maps to `TEXT`; the length is ignored. Write `varchar`, not `character varying`. |
| `char(n)` | **Different behavior** | Maps to `TEXT`; no blank padding, and trailing blanks count in comparisons. `character(n)` and `bpchar` are not accepted. |
| `text` | With limitations | Byte-order comparison; no `COLLATE`. |
| `date` | With limitations | `0001-01-01` to `9999-12-30`. No `infinity`, `epoch`, or `today`. `NN/NN/YYYY` is read day first: `'01/02/2024'::DATE` is February 1. See [DATE](/reference-sql/data-types/date). |
| `timestamp`, `timestamptz` | With limitations | Microsecond precision only. Ends at `9999-12-30 22:00:00.999999`. A time of day must include seconds. `NN/NN/YYYY` is read day first. Time zone abbreviations such as `PST` are rejected; use IANA names or offsets. See [TIMESTAMPTZ](/reference-sql/data-types/timestamptz). |
| `interval` | With limitations | Expressions only, not a column type. ISO 8601 input (`P1D`) and fractional seconds in `hh:mm:ss` input are rejected. |
| `json`, `jsonb` | Supported | One type, normalized on input; `pg_typeof` reports `json`. See [JSON](/reference-sql/data-types/json). |
| Arrays | Supported | Nested `ARRAY(T)`: `INT[]` and `INT[][]` are different types, and `'{{1,2},{3,4}}'::INT[]` is rejected. |
| `oid`, `regclass` | With limitations | Casts only. Identifiers are 64-bit, and `regclass` displays as a number. |

Not available: `smallint` (use `INTEGER`), `serial` and identity columns, `money`, `"char"`, `name`, `time`, `timetz`, `uuid` (use `TEXT` with `gen_random_uuid_text()`), composite and row types (use [STRUCT](/reference-sql/data-types/struct)), `enum`, domains, range types, network types, geometric types (use [GEOGRAPHY](/reference-sql/data-types/geography)), `tsvector`, `tsquery`, `bit`, `xml`, `pg_lsn`, and the other `reg*` types.

### Literals

| Literal | Status | Notes |
| :- | :- | :- |
| `1.5`, `1e3` | **Different behavior** | Typed `DOUBLE PRECISION`; PostgreSQL uses `NUMERIC`. |
| `0x1F`, `0o17`, `0b101` | Not supported | Parsed as `0` with a column alias, without an error. |
| `'a' 'b'` | Extended | Always concatenated; PostgreSQL requires a newline between them. |
| Integers | With limitations | More than 38 digits raise an error. |

Not available: tagged dollar quotes (`$tag$...$tag$`), Unicode escape strings (`U&'...'`), bit strings (`B'101'`, `X'1F'`), and array literals with explicit bounds (`'[0:2]={1,2,3}'`).

## Query syntax

Core `SELECT` features work as in PostgreSQL, including joins, `LATERAL`, `GROUPING SETS`, subqueries, CTEs, and the default `NULL` ordering. Two precedence rules differ without an error; the remaining gaps raise errors.

<AccordionGroup>
  <Accordion title="Set operations and joins">
    | Feature | Status | Notes |
    | :- | :- | :- |
    | `INTERSECT` mixed with `UNION` or `EXCEPT` | **Different behavior** | Evaluated left to right: `SELECT 1 UNION SELECT 2 INTERSECT SELECT 2` returns `2`, while PostgreSQL evaluates `INTERSECT` first and returns `1, 2`. Use parentheses. |
    | Comma joins mixed with `JOIN` | **Different behavior** | `FROM a, b RIGHT JOIN c ON ...` is read as `(a, b) RIGHT JOIN c`; PostgreSQL reads `a, (b RIGHT JOIN c)`. Use `CROSS JOIN` and parentheses. |
    | `FULL JOIN` on a non-equality condition | Extended | |

    Not available: `NATURAL LEFT`, `NATURAL RIGHT`, and `NATURAL FULL` joins (write them with `USING`), and `USING (...) AS alias`.
  </Accordion>

  <Accordion title="SELECT, grouping, and limits">
    | Feature | Status | Notes |
    | :- | :- | :- |
    | `ORDER BY` on text | With limitations | Byte order; no `COLLATE` or `ORDER BY ... USING`. |
    | Output aliases in `WHERE` and `HAVING` | Extended | An input column with the same name takes precedence, so PostgreSQL queries keep their meaning. |
    | `db.schema.table` | Extended | See [Cross-database queries](/reference-sql/commands/queries/cross-database-queries). |
    | `GROUP BY ALL`, `QUALIFY` | Extended | Firebolt clauses. |

    Not available:

    * `DISTINCT ON`; use `QUALIFY row_number() OVER (PARTITION BY ... ORDER BY ...) = 1`.
    * `GROUP BY DISTINCT`.
    * `LIMIT ALL`, `OFFSET n ROWS` without `FETCH`, `FETCH FIRST ROW ONLY` without a count, and `WITH TIES`; use `OFFSET n` and `FETCH FIRST 1 ROWS ONLY`.
    * Bare `VALUES ... ORDER BY` and `TABLE t`; use `SELECT * FROM ...`.
    * `TABLESAMPLE`, `FOR UPDATE`, `FOR SHARE`, `ONLY`, and system columns such as `ctid`.
  </Accordion>

  <Accordion title="Subqueries, CTEs, and window frames">
    | Feature | Status | Notes |
    | :- | :- | :- |
    | `RANGE` frame offsets | With limitations | Integer offsets over an `INTEGER`, `BIGINT`, `REAL`, `DOUBLE PRECISION`, or `DATE` sort key only. |

    Not available:

    * `WITH RECURSIVE`, `SEARCH`, `CYCLE`, and data-modifying statements in `WITH`.
    * Row comparisons such as `(a, b) < (c, d)`, `ROW(...)`, `SOME`, and `ARRAY(subquery)`; `(a, b) IN (SELECT ...)` works.
    * `GROUPS` frames, `EXCLUDE`, and refining a named window, as in `OVER (w ORDER BY ...)`.
  </Accordion>

  <Accordion title="Identifiers and utility statements">
    | Feature | Status | Notes |
    | :- | :- | :- |
    | Identifiers | Supported | Names longer than 63 bytes are not truncated. |
    | Keywords as identifiers | With limitations | More words are reserved, for example `time`, `first`, `replace`, and `interval` as column names. Quote them. See [Reserved words](/reference-sql/lexical-structure/reserved-words). |
    | `EXPLAIN` | With limitations | Options must be parenthesized, as in `EXPLAIN (ANALYZE)`. No `VERBOSE`, `COSTS`, or `BUFFERS`. See [EXPLAIN](/reference-sql/commands/queries/explain). |

    Not available: `PREPARE` and `EXECUTE` (drivers pass parameters), and bare `user`, `current_role`, `current_schema`, `current_time`, `localtime`, and `system_user` (use `current_user` and `current_schema()`).
  </Accordion>
</AccordionGroup>

## Functions and operators

Functions not mentioned here work as in PostgreSQL. Functions that exist only in Firebolt are documented in [SQL functions](/reference-sql/functions-reference).

<AccordionGroup>
  <Accordion title="Casts and conditional expressions">
    | Function | Status | Notes |
    | :- | :- | :- |
    | `COALESCE`, `CASE` | Extended | A string literal that can't be cast to the result type fails only if evaluated: `coalesce(1, 'a')` returns `1`. |

    Firebolt adds `TRY_CAST`, which returns `NULL` when a cast fails. Not available: `to_number`, `to_char` with a numeric or interval argument, `IS UNKNOWN`, `BETWEEN SYMMETRIC`, `num_nulls`, and `num_nonnulls`.
  </Accordion>

  <Accordion title="String and pattern matching">
    Text is compared and case-mapped as under the PostgreSQL `C` collation: by byte value, with case changes for ASCII letters only.

    | Function | Status | Notes |
    | :- | :- | :- |
    | `substring(s FROM pattern)` | **Different behavior** | Reads the pattern as a start position: `substring('a2c' FROM '2')` returns `2c`, not `2`. Use [REGEXP\_EXTRACT](/reference-sql/functions-reference/string/regexp-extract). |
    | `lower`, `upper` | With limitations | ASCII letters only: `upper('é')` returns `é`. |
    | `ILIKE`, `~*` | With limitations | ASCII letters only: `'ÉA' ILIKE 'éa'` is `false`. |
    | `~`, `!~`, `regexp_like` | With limitations | [RE2 syntax](https://github.com/google/re2/wiki/Syntax): no backreferences, lookaround, `\m`, `\M`, `\y`, or `(?x)`. |
    | `regexp_replace` | With limitations | Flags `g` and `i` only; no start or occurrence arguments. |
    | `string_to_array` | With limitations | Two arguments only. |
    | `concat` | Supported | Booleans become `'true'` and `'false'`, not `'t'` and `'f'`. |

    Not available: `concat_ws`, `initcap`, `chr`, `translate`, `overlay`, `quote_literal`, `quote_nullable`, `bit_length`, `normalize`, `string_to_table`, `LIKE ... ESCAPE`, `SIMILAR TO`, `LIKE ANY (array)`, and `regexp_match`, `regexp_matches`, `regexp_substr`, `regexp_count`, and the `regexp_split_to_*` functions (use [REGEXP\_EXTRACT\_ALL](/reference-sql/functions-reference/string/regexp-extract-all)).
  </Accordion>

  <Accordion title="Math">
    | Function | Status | Notes |
    | :- | :- | :- |
    | `abs`, `sign`, `ceil`, `floor`, `round`, `trunc` | **Different behavior** | Keep the `NUMERIC` input scale: `ceil(2.5::NUMERIC(10, 2))` is `3.00`, not `3`. |
    | `+`, `-`, `*`, `/` | Supported | `NUMERIC` results keep the input scale. |
    | `%`, `mod` | With limitations | No `DOUBLE PRECISION`; two `NUMERIC` arguments need the same precision and scale. |
    | `^`, `power`, `sqrt`, `exp`, `ln`, `log` | With limitations | No `NUMERIC` arguments; cast to `DOUBLE PRECISION`. |

    Not available: the operators `~` (bitwise NOT), `@`, `|/`, and `||/` (use `abs`, `sqrt`, `cbrt`), `div`, `gcd`, `lcm`, `factorial`, `width_bucket`, `scale`, `min_scale`, `trim_scale`, `setseed`, `random(min, max)`, and the degree-based trigonometric functions such as `sind`.
  </Accordion>

  <Accordion title="Date and time">
    | Function | Status | Notes |
    | :- | :- | :- |
    | `extract` | Different return type | `INTEGER` for whole-number fields; `NUMERIC(38, 9)` for `epoch`, `second`, `milliseconds`, and `microseconds`. No `julian`. |
    | `date_trunc` | Different return type | A `DATE` argument returns a `DATE`, not `timestamptz`. |
    | `to_char`, `to_date`, `to_timestamp` | With limitations | `to_char` lacks `FX`, `J`, `FF1` to `FF6`, and `TM`. Parsing also lacks `FM`, `BC`, `WW`, `DDD`, `IYYY`, and `SSSS`. |

    Not available: `date_part` (use `extract`), `age`, `date_bin`, `make_date`, `make_timestamp`, `isfinite`, `OVERLAPS`, `current_time`, `localtime`, `clock_timestamp`, `statement_timestamp`, `transaction_timestamp`, and `timeofday`.
  </Accordion>

  <Accordion title="Aggregate and window functions">
    | Function | Status | Notes |
    | :- | :- | :- |
    | `avg` | Different return type | `DOUBLE PRECISION` for integer input; input scale for `NUMERIC` input. |
    | `string_agg`, `array_agg` | With limitations | No `ORDER BY` inside the call; no `DISTINCT` in `string_agg`. Sort with `array_sort(array_agg(x))`. |
    | `stddev`, `variance` and variants | With limitations | `REAL` and `DOUBLE PRECISION` input only. |

    Not available: `last_value`, `every` (use `bool_and`), `mode()`, `percentile_disc`, `regr_*`, hypothetical-set aggregates, `json_agg`, `jsonb_agg`, and `count(DISTINCT (a, b))`.
  </Accordion>

  <Accordion title="Array">
    | Function | Status | Notes |
    | :- | :- | :- |
    | `a[i]` | With limitations | An index of 0 or less raises an error instead of returning `NULL`. |
    | `array_upper` | With limitations | Dimension 1 only; `0` for an empty array instead of `NULL`. |
    | `array_position` | With limitations | No start argument. |
    | `unnest(a, b, ...)` | With limitations | In `FROM` only, and the arrays must have equal lengths. |
    | `array_length` | Not supported | Firebolt's `array_length(a)` has no dimension argument and returns `0` for an empty array. |

    Not available: `@>`, `<@`, `&&`, and `array || element` (use [ARRAY\_CONTAINS](/reference-sql/functions-reference/array/array-contains), [ARRAYS\_OVERLAP](/reference-sql/functions-reference/array/arrays-overlap), and [ARRAY\_CONCAT](/reference-sql/functions-reference/array/array-concat)), slices, `array_append`, `array_prepend`, `array_remove`, `array_replace`, `array_ndims`, `array_dims`, `array_lower`, `cardinality`, and `WITH ORDINALITY`.
  </Accordion>

  <Accordion title="JSON">
    Access fields with the [subscript and dot operators](/reference-sql/functions-reference/json/json-operators), for example `(doc)['a']` or `(doc).a`.

    | Operator | Status | Notes |
    | :- | :- | :- |
    | `->>` | With limitations | `TEXT` input only; on a `JSON` value it raises an error. |

    Not available: `->`, `#>`, `#>>` (use the subscript operator or [JSON\_VALUE](/reference-sql/functions-reference/json/json-value)), `@>`, `?`, `=` on `JSON`, `jsonb_array_length`, `jsonb_each`, `json_strip_nulls`, `jsonb_pretty`, `json_build_object`, `json_build_array`, `json_extract_path`, `json_array_elements`, `json_object_keys`, `jsonb_path_query`, and `row_to_json`.
  </Accordion>

  <Accordion title="System information and set-returning functions">
    | Function | Status | Notes |
    | :- | :- | :- |
    | `pg_typeof` | With limitations | Returns Firebolt type names as `TEXT`, such as `numeric(10, 2)` and `array(integer)`. |
    | `format_type` | With limitations | Internal names such as `int4`, without type modifiers. |
    | `current_setting` | With limitations | Firebolt settings only; no `missing_ok` argument. |
    | `current_schemas` | Supported | `current_schemas(true)` omits `pg_catalog`. |
    | `version` | Supported | Returns the Firebolt version string. |

    Not available: `generate_series` over timestamps or `NUMERIC`, `generate_subscripts`, `sha224`, `sha256`, `sha384`, `sha512`, `crc32`, `set_config`, `pg_backend_pid`, `txid_current`, `pg_sleep`, `inet_client_addr`, `obj_description`, `to_regclass`, `pg_get_viewdef`, `pg_get_constraintdef`, and `pg_table_size`.
  </Accordion>
</AccordionGroup>

## Database objects

Firebolt supports tables, views, schemas, databases, roles, and users. It adds objects with no PostgreSQL counterpart: engines, locations, external tables, and aggregating indexes.

| Object | Status | Notes |
| :- | :- | :- |
| Tables | With limitations | `ALTER TABLE` supports `ADD COLUMN`, `DROP COLUMN`, `RENAME COLUMN`, `RENAME TO`, and `OWNER TO`, one action per statement. Details below. |
| Temporary tables | **Different behavior** | Ordinary tables, visible to every session and never dropped automatically. No `ON COMMIT`. |
| Views | With limitations | Bind to their tables when queried. `ALTER VIEW` supports `OWNER TO` only. Details below. |
| Indexes | With limitations | Firebolt index types only: `CREATE INDEX ... USING SKIP_INDEX`, `INVERTED_INDEX`, `FULL_TEXT`, or `HNSW`. See [CREATE INDEX](/reference-sql/commands/data-definition/create-index). |
| Schemas | With limitations | No `AUTHORIZATION` or `RENAME`; `ALTER SCHEMA` supports `OWNER TO` only. `public` cannot be dropped. |
| Databases | With limitations | No `ENCODING`, `TEMPLATE`, or locale options. |
| Functions | Not supported | No SQL or PL/pgSQL functions. Python functions are in private preview; see [CREATE FUNCTION](/reference-sql/commands/data-definition/create-function). |
| Comments | With limitations | `COMMENT ON` tables, columns, databases, locations, and engines; not views or schemas. Read them from the `description` column of `information_schema.tables` and `information_schema.columns`. |

Not available: materialized views (use [aggregating indexes](/reference-sql/commands/data-definition/create-aggregating-index)), B-tree, hash, GIN, GiST, and BRIN indexes, sequences and identity columns (generate keys while loading or with `row_number()`), procedures, `DO`, `CALL`, triggers, rules, types, domains, enums, extensions, row-level security policies, foreign tables (use [external tables](/reference-sql/commands/data-definition/create-external-table) or `read_parquet` and `read_csv`), `CREATE STATISTICS` (use `ALTER TABLE ... ADD STATISTICS`), tablespaces, collations, publications, and subscriptions.

### Tables

| Clause | Status | Notes |
| :- | :- | :- |
| `UNIQUE` | With limitations | Not enforced, but the optimizer assumes it holds. |
| `PRIMARY KEY` | With limitations | Only `PRIMARY KEY (columns) NOT ENFORCED` at table level, on `NOT NULL` columns, and not enforced. Unrelated to the Firebolt primary index. |
| `FOREIGN KEY` | With limitations | `NOT ENFORCED` only. |
| `DEFAULT` | With limitations | Literals, constant numeric and date arithmetic, `CURRENT_DATE`, `CURRENT_TIMESTAMP`, `LOCALTIMESTAMP`, and `NOW()`. |
| `PARTITION BY` | With limitations | Firebolt syntax: `PARTITION BY` a list of columns or `EXTRACT`, `DATE_TRUNC`, `TO_YYYYMMDD`, or `TO_YYYYMM` calls. No `RANGE`, `LIST`, `HASH`, or `PARTITION OF`. |
| `DROP COLUMN` used by a view | Extended | Succeeds; the view then fails when queried. |

Not available: `CHECK`, `CONSTRAINT name`, `EXCLUDE`, `UNLOGGED`, `INHERITS`, storage parameters, `LIKE` and `WITH NO DATA` (use `CREATE TABLE new AS SELECT * FROM old LIMIT 0`, or [CREATE TABLE CLONE](/reference-sql/commands/data-definition/create-table-clone) to copy the data too), `ALTER COLUMN ... TYPE`, `SET` and `DROP DEFAULT`, `SET` and `DROP NOT NULL`, `ADD CONSTRAINT`, and `SET SCHEMA`.

### Views

| Statement | Status | Notes |
| :- | :- | :- |
| `CREATE VIEW ... AS SELECT *` | **Different behavior** | Columns added to a table later appear in the view. |
| `CREATE OR REPLACE VIEW` | Extended | The column list can change freely. |

Not available: `WITH CHECK OPTION`, `TEMP` and `RECURSIVE` views, `ALTER VIEW ... RENAME TO`, and dropping several views or tables in one statement.

## Data manipulation

`INSERT`, `UPDATE`, `DELETE`, `MERGE`, and `TRUNCATE` work as in PostgreSQL, apart from the following.

| Statement | Status | Notes |
| :- | :- | :- |
| `ON CONFLICT` | With limitations | Single-row `VALUES` only; no unique constraint required; no `WHERE` or `ON CONSTRAINT`. Use `MERGE` for multi-row upserts. |
| `UPDATE ... FROM` | With limitations | Several source rows matching one target row raise an error; PostgreSQL applies one of them. |
| `MERGE ... DELETE` | Extended | Several matching source rows are allowed. |
| `GENERATED` columns | With limitations | The `DEFAULT` keyword is rejected for them; omit the column. |
| `TRUNCATE` | With limitations | One table; no `RESTART IDENTITY` or `CASCADE`. |

Not available: `RETURNING`, `UPDATE ... SET (a, b) = (...)`, `MERGE ... INSERT DEFAULT VALUES`, `WITH` in front of a data-modifying statement (write `INSERT INTO t WITH x AS (...) SELECT ...`, or use a subquery), and `COPY ... FROM STDIN` or `TO STDOUT` (Firebolt [COPY FROM](/reference-sql/commands/data-management/copy-from) and [COPY TO](/reference-sql/commands/data-management/copy-to) use object storage).

## Access control

Users and roles are separate objects: privileges are granted to roles, and roles to users. Each `GRANT` names one privilege, one object, and one grantee. See [Role-based access control](/security/rbac).

| Statement | Status | Notes |
| :- | :- | :- |
| `GRANT ... ON TABLE t` | With limitations | The `TABLE` keyword is required. Views use `ON VIEW v`. |
| `GRANT ALL ON TABLE t` | Supported | Grants `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, and the Firebolt privileges `MODIFY` and `VACUUM`. |
| `GRANT role TO user` | With limitations | Written `GRANT ROLE r TO USER u`; role hierarchies use `GRANT ROLE r1 TO ROLE r2`. |
| `CREATE ROLE`, `CREATE USER` | With limitations | No attributes such as `LOGIN` or `SUPERUSER`. Privileges can't be granted to a user directly. |
| `ALTER DEFAULT PRIVILEGES` | With limitations | Schemas only (`ON SCHEMAS`). See [ALTER DEFAULT PRIVILEGES](/reference-sql/commands/access-control/alter-default-privileges). |
| `ON ALL TABLES IN SCHEMA` | Not supported | Use `GRANT SELECT ANY ON SCHEMA s`. |

Not available: several privileges or grantees in one statement, `WITH GRANT OPTION`, `REVOKE ... CASCADE`, `GRANT CONNECT` or `TEMPORARY ON DATABASE` (use `USAGE` and `CREATE ON DATABASE`), `REFERENCES` and `TRIGGER` privileges, `ALTER ROLE ... RENAME` (`ALTER USER ... RENAME` works), `ALTER USER ... WITH PASSWORD`, `SET ROLE`, `REASSIGN OWNED`, `DROP OWNED`, and row-level security.

## Transactions and sessions

| Statement | Status | Notes |
| :- | :- | :- |
| Isolation | With limitations | `REPEATABLE READ` (snapshot isolation) only. See [Explicit transactions](/reference-sql/explicit-transactions). |
| Concurrent writes | Supported | No locks; a conflicting transaction fails at `COMMIT`. |
| `COMMIT` outside a transaction | Not supported | Raises an error; PostgreSQL warns. |
| `SET` | With limitations | Firebolt settings only; write `SET timezone = '...'`, not `SET TIME ZONE`. See [System settings](/reference-sql/system-settings). |
| `SHOW` | With limitations | `SHOW ALL` and `SHOW TIMEZONE` only; use `current_setting()` for others. |
| `VACUUM t` | With limitations | A table name is required; no `FULL` or `ANALYZE`. See [VACUUM](/reference-sql/commands/data-management/vacuum). |

Not available: `READ COMMITTED`, `SERIALIZABLE`, `READ ONLY`, `SET TRANSACTION`, `SAVEPOINT`, `PREPARE TRANSACTION`, `SELECT ... FOR UPDATE`, `LOCK TABLE`, `SET search_path` (qualify names with the schema), `RESET`, `SET LOCAL`, `ANALYZE`, `REINDEX`, `CLUSTER`, `CHECKPOINT`, `DISCARD`, `LISTEN`, and `NOTIFY`.

## System catalog

`information_schema` and the common `pg_catalog` relations are available. Firebolt's own catalog views are documented under [Information schema](/reference-sql/information-schema).

| Relation | Status | Notes |
| :- | :- | :- |
| `information_schema.columns` | Supported | `data_type` holds upper-case Firebolt names such as `INTEGER` and `ARRAY(INTEGER)`, so `data_type = 'integer'` matches nothing. `VARCHAR(n)` is reported as `TEXT`. |
| `table_constraints`, `key_column_usage`, `pg_constraint`, `pg_description`, `pg_depend` | With limitations | Present but always empty. |
| `pg_class`, `pg_attribute`, `pg_type`, and related relations | Supported | Object identifiers are 64-bit. |
| `col_description` | With limitations | Always `NULL`. |

Not available: `referential_constraints`, `check_constraints`, `sequences`, `triggers`, `pg_proc`, `pg_roles`, `pg_auth_members`, `pg_index`, `pg_indexes`, `pg_stat_activity`, `pg_locks`, `pg_sequences`, and `pg_extension`.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.