SQL features

SQL features The dialects expose a large amount of engine-specific SQL that the model layer does not surface — generated columns, exclusion constraints, index hints, sampling, cursors, table storage settings. This page is the index to it. Every snippet below is the helper's real output, produced by calling it. Most of these are emitted for you when you declare the equivalent option on a model or index; you reach for the builder directly when writing a migration or generating DDL yourself. Note: support varies by engine, and most of these builders do not check. Passing 'INITIALLYDEFERRED' where the type says 'INITIALLY DEFERRED' emits the underscore into your SQL rather than raising, so the value only fails when the database sees it. Take the accepted values below literally. Columns Generated columns A column computed from others, maintained by the database. stored: false gives a virtual column, recomputed on read. MySQL supports both; PostgreSQL supports stored only. Identity columns The standard-SQL replacement for SERIAL / AUTOINCREMENT. type is 'ALWAYS' or 'BY DEFAULT'. ALWAYS refuses an explicit insert into the column, which is usually what you want — it stops an application from writing an id that the sequence will later reissue. Collation Collation decides sort order and comparison. A column compared against another of a different collation is an error on some engines and a silent full scan on others. Constraints Exclusion constraints PostgreSQL's generalisation of UNIQUE: no two rows may satisfy the given operator pairwise. The canonical use is preventing overlapping reservations, which a unique constraint cannot express. Deferrable constraints Accepted: 'NOT DEFERRABLE', 'INITIALLY IMMEDIATE', 'INITIALLY DEFERRED'. A deferred constraint is checked at commit rather than per statement, which is what lets you insert rows with circular references inside one transaction. Indexes Index hints useIndex suggests, forceIndex insists, ignoreIndex excludes. Reach for these last: a hint that helps today becomes wrong when the data distribution changes, and unlike a missing index it will not show up as a slow query — it shows up as a query that stopped improving. Spatial indexes Clustered indexes type is 'clustered' or 'nonclustered', lower case. A clustered index determines physical row order, so a table has at most one. Statistics targets Raise the target on a column whose distribution is skewed enough that the planner keeps choosing badly — a status column where 99% of rows share one value is the usual case. Queries DISTINCT ON PostgreSQL only. Returns the first row per group, decided by ORDER BY — the concise way to fetch "the latest row per user" without a window function. TABLESAMPLE BERNOULLI scans and keeps each row with the given probability; SYSTEM picks whole pages, which is far faster and far less random. For a rough aggregate over a huge table, SYSTEM is usually the right trade. Cursors holdable is 'HOLD' or 'WITHOUT HOLD' — a held cursor survives the transaction that declared it. For iterating a large result in application code, prefer streams, which manage the cursor for you. JSON access There are matching generateJSONSetSQL and generateJSONRemoveSQL builders. For querying rather than manipulating, the JSON operators are the better route. Tables Inheritance PostgreSQL table inheritance. Note that constraints and indexes are not inherited, and a query against the parent scans the children unless you write ONLY — declarative partitioning is usually the better tool. Tablespaces Compression 'pglz' or 'lz4'. Applies to oversized values moved out of line, not to the row itself. Replica identity Decides what logical replication writes for an update or delete. FULL logs every old column, which makes downstream consumers able to match rows on a table without a primary key — at a real write cost. Sessions There is a matching generateIdleInTransactionTimeoutSQL. Both are session settings, so on a pooled connection they apply to whichever connection ran them — see SQL constants. Where the rest lives Looking for | Page | --- | --- | Views, materialized views, triggers, procedures, sequences, row-level security, partitions | Schema objects | Check constraints, foreign keys, unique and partial indexes | Indexes & constraints | fn(), col(), literal(), window functions, aggregates | SQL functions | Session settings, isolation levels, pragmas | SQL constants | Raw statements and bound parameters | Raw queries | DDL from code, outside a migration | Query interface | Related reading Schema objects — views, triggers, sequences and the rest Indexes & constraints Query interface