PostgreSQL Compatibility
SereneDB implements the SQL dialect of PostgreSQL. Not every PostgreSQL statement parses, and not all underlying functionality is implemented. Among the statements that fail to parse are SAVEPOINT, DECLARE ... CURSOR and FETCH, SELECT ... FOR UPDATE, FETCH FIRST ... WITH TIES, identity columns, CREATE DOMAIN, unlogged tables, materialized views, CREATE EXTENSION, CREATE INDEX CONCURRENTLY and PL/pgSQL function bodies; SQL-language functions work. LISTEN, NOTIFY and UNLISTEN parse and answer that they are not supported.
This page gives a non-exhaustive overview of supported core SQL functionality, followed by the behavioral differences where SereneDB intentionally diverges from PostgreSQL.
For PostgreSQL-specific functionality such as system table support, see the System Table Compatibility page.
Data Definition
Table Creation & Deletion
| Feature | Support State | Details |
|---|---|---|
| CREATE TABLE | Yes | |
| DROP TABLE | Yes | |
| Default Values | Yes | |
| GENERATED | Yes | |
| Check Constraints | Yes | |
| Not-Null Constraints | Yes | |
| Unique Constraints | Yes | |
| Primary Keys | Yes | |
| Foreign Keys | Yes | Enforced; over-eager on some UPDATEs |
| Named Constraints | Yes | |
| Exclusion Constraints | No | See issue |
| System Columns | Partial | Every table has tableoid, ctid, xmin, xmax, cmin and cmax. ctid identifies a row and orders rows by their position, but its block and offset don't name storage pages, and the row a ctid names can move after VACUUM or a checkpoint. xmin, xmax, cmin and cmax are always 0. |
Table Modification (ALTER TABLE)
| Feature | Support State | Details |
|---|---|---|
| ADD COLUMN | Yes | A serial column can't declare PRIMARY KEY or UNIQUE on a table that already has rows |
| DROP COLUMN | Yes | |
| ADD CHECK | Yes | |
| ADD CONSTRAINT | Yes | CHECK, UNIQUE and PRIMARY KEY constraints; not FOREIGN KEY |
| ADD FOREIGN KEY | No | Fails with No support for adding FOREIGN_KEY constraints with ALTER TABLE |
| DROP CONSTRAINT | Yes | |
| ALTER COLUMN | Yes | TYPE, SET/DROP DEFAULT and SET NOT NULL |
| SET DEFAULT | Yes | |
| DROP DEFAULT | Yes | |
| COLUMN TYPE | Yes | Not on a column that has a CHECK constraint |
| RENAME COLUMN | Yes | |
| RENAME TO | Yes | |
| SET SCHEMA | Partial | Moves a table, a search table included, with its indexes and owned sequences, and with ALTER VIEW, ALTER SEQUENCE, ALTER TYPE, ALTER FUNCTION and ALTER TEXT SEARCH DICTIONARY a view, sequence, type, function or dictionary; not an entry that another object depends on |
Privileges
| Feature | Support State | Details |
|---|---|---|
| CREATE ROLE | Yes | See Database Roles |
| OWNER TO | Yes | Tables, views, sequences, schemas, types, jobs; not databases or functions |
| ALTER ROLE | Yes | Attributes, passwords, VALID UNTIL, RENAME TO |
| GRANT | Yes | Privileges and role membership, incl. column grants; see Privileges |
| REVOKE | Yes | Incl. GRANT OPTION FOR |
| SET ROLE | Yes | Enforced; membership required for non-superusers |
| INHERIT | Yes | INHERIT/NOINHERIT attributes enforced |
| ALTER DEFAULT PRIVILEGES | Yes | |
| Row Security Policies | No | See issue |
Indexes
| Feature | Support State | Details |
|---|---|---|
| CREATE INDEX | Yes | |
| GIN | No | |
| BRIN | No | |
| Multicolumn Indexes | Yes | |
| Ordered Indexes | Yes | |
| Unique Indexes | Yes | |
| Indexes on Expressions | Yes | |
| Partial Indexes | Partial | Inverted indexes honor WHERE. A plain index accepts it but filters only the rows present when it is built: rows written later are indexed whether or not they match, keyed on the value and on whether the predicate holds, so a partial UNIQUE index also rejects duplicates outside its predicate |
| INCLUDE | Partial | Inverted indexes store INCLUDE columns. A plain index accepts INCLUDE but keeps only its key columns, so a UNIQUE index is unique on the keys alone, as in PostgreSQL |
Misc
| Feature | Support State | Details |
|---|---|---|
| CREATE SCHEMA | Yes | |
| DROP SCHEMA | Yes | |
| search_path | Yes | |
| Table Inheritance | No | |
| Table Partitioning | No | |
| Foreign Data Wrappers | No | |
| Views | Yes | |
| Databases | Yes | |
| Scheduled Jobs | Yes | SereneDB extension, not PostgreSQL syntax: CREATE JOB |
| Functions & Procedures | Yes | |
| Custom Types | Yes | |
| Triggers | No | |
| Prepared Statements | Yes | |
| Advisory Locks | No |
Data Manipulation
| Feature | Support State | Details |
|---|---|---|
| INSERT | Yes | |
| UPDATE | Yes | |
| DELETE | Yes | |
| TRUNCATE | Yes | |
| RETURNING | Yes | |
| COPY FROM | Yes | |
| COPY TO | Yes | |
| ON CONFLICT | Partial |
Queries
| Feature | Support State | Details |
|---|---|---|
| Table & View References | Yes | |
| Inner Joins | Yes | |
| Outer Joins | Yes | |
| Semijoins | Yes | |
| Antijoins | Yes | |
| Table Functions | Yes | |
| Column Definition Lists | No | |
| Lateral Subqueries | Yes | |
| User-Specified Aliases | Yes | |
| GROUP BY | Yes | |
| HAVING | Yes | |
| GROUPING SETS | Yes | |
| CUBE | Yes | |
| ROLLUP | Yes | |
| WINDOW Functions | Yes | |
| WITH | Yes | |
| WITH RECURSIVE | Yes | |
| UNION | Yes | |
| UNION ALL | Yes | |
| INTERSECT | Yes | |
| INTERSECT ALL | Yes | |
| EXCEPT | Yes | |
| EXCEPT ALL | Yes | |
| ORDER BY | Yes | |
| LIMIT | Yes | |
| OFFSET | Yes | |
| Table Generating Function | Yes |
Data Types
Types
Data Type Compatibility lists every PostgreSQL type and how SereneDB supports it.
Operators & Functions
Logical
| Feature | Support State | Details |
|---|---|---|
| AND | Yes | |
| OR | Yes | |
| NOT | Yes |
Comparison
| Feature | Support State | Details |
|---|---|---|
| < | Yes | |
| > | Yes | |
| <= | Yes | |
| >= | Yes | |
| = | Yes | |
| <> | Yes | |
| != | Yes | |
| BETWEEN | Yes | |
| NOT BETWEEN | Yes | |
| IS DISTINCT | Yes | |
| IS NOT DISTINCT | Yes | |
| IS NULL | Yes | |
| IS NOT NULL | Yes | |
| IS TRUE | Yes | |
| IS NOT TRUE | Yes | |
| IS FALSE | Yes | |
| IS NOT FALSE | Yes | |
| IS UNKNOWN | Yes | |
| IS NOT UNKNOWN | Yes |
Mathematical
| Feature | Support State | Details |
|---|---|---|
| + | Yes | |
| - | Yes | |
| * | Yes | |
| / | Yes | On numeric, returns double precision |
| % | Yes | |
| ^ | Yes | On numeric, returns double precision |
| / | Yes | |
| / | ||
| @ | Yes | |
| & | Yes | |
| Yes | ||
| # | Yes | |
| ~ | Yes | |
| << | Yes | |
| >> | Yes | |
| abs | Yes | |
| cbrt | Yes | |
| ceil | Yes | |
| degrees | Yes | |
| div | Yes | |
| erf | Yes | |
| erfc | Yes | |
| exp | Yes | On numeric, returns double precision |
| factorial | Yes | |
| floor | Yes | |
| gcd | Yes | |
| lcm | Yes | |
| ln | Yes | On numeric, returns double precision |
| log | Yes | On numeric, returns double precision |
| log10 | Yes | On numeric, returns double precision |
| min_scale | Yes | |
| mod | Yes | |
| pi | Yes | |
| power | Yes | On numeric, returns double precision |
| radians | Yes | |
| round | Yes | |
| scale | Yes | The scale of the column type: numeric without a scale has ten fractional digits |
| sign | Yes | Returns smallint |
| sqrt | Yes | On numeric, returns double precision |
| trim_scale | Partial | Trims a constant; a column's value keeps the scale of its type |
| trunc | Yes | |
| width_bucket | Yes | |
| random | Yes | |
| random_normal | Yes | |
| setseed | Yes | |
| acos | Yes | |
| acosd | Yes | |
| asin | Yes | |
| asind | Yes | |
| atan | Yes | |
| atand | Yes | |
| atan2 | Yes | |
| atan2d | Yes | |
| cos | Yes | |
| cosd | Yes | |
| cot | Yes | |
| cotd | Yes | |
| sin | Yes | |
| sind | Yes | |
| tan | Yes | |
| tand | Yes | |
| sinh | Yes | |
| cosh | Yes | |
| tanh | Yes | |
| asinh | Yes | |
| acosh | Yes | |
| atanh | Yes |
Text
| Feature | Support State | Details |
|---|---|---|
| || | Yes | |
| btrim | Yes | Without characters to remove, also removes Unicode space separators such as the no-break space; PostgreSQL removes only spaces |
| bit_length | Yes | |
| char_length | Yes | |
| lower | Yes | |
| lpad | Yes | |
| ltrim | Yes | Same as btrim |
| normalize | Partial | Returns the text unchanged: no normalization form is applied |
| octet_length | Yes | |
| overlay | Yes | |
| position | Yes | |
| rpad | Yes | |
| rtrim | Yes | Same as btrim |
| substring | Yes | |
| trim | Yes | Same as btrim |
| upper | Yes | |
| ^@ | Yes | |
| ascii | Yes | |
| casefold | Yes | |
| chr | Yes | |
| concat | Yes | |
| concat_ws | Yes | |
| format | Yes | |
| initcap | Yes | |
| left | Yes | |
| length | Yes | |
| md5 | Yes | |
| parse_ident | Yes | |
| quote_ident | Yes | |
| quote_literal | Yes | |
| quote_nullable | Yes | |
| repeat | Yes | |
| replace | Yes | |
| reverse | Yes | |
| right | Yes | |
| split_part | Yes | |
| starts_with | Yes | |
| string_to_array | Yes | |
| string_to_table | Yes | |
| strpos | Yes | |
| substr | Yes | |
| to_ascii | No | |
| to_hex | Yes | |
| translate | Yes | |
| unistr | Yes |
Bytea
| Feature | Support State | Details |
|---|---|---|
| || | Yes | |
| bit_length | Yes | |
| btrim | Yes | |
| ltrim | Yes | |
| octet_length | Yes | |
| overlay | Yes | |
| position | Yes | |
| rtrim | Yes | |
| substring | Yes | |
| trim | Yes | |
| bit_count | No | |
| crc32 | Yes | |
| crc32c | Yes | |
| get_bit | Yes | |
| get_byte | Yes | |
| length | Yes | |
| md5 | Yes | |
| set_bit | Yes | |
| set_byte | Yes | |
| sha224 | Yes | |
| sha384 | Yes | |
| sha512 | Yes | |
| substr | Yes | |
| convert | No | |
| convert_from | Partial | Converts from UTF8, SQL_ASCII and LATIN1; any other valid encoding raises default conversion function ... does not exist |
| convert_to | Partial | Converts to UTF8, SQL_ASCII and LATIN1; any other valid encoding raises default conversion function ... does not exist |
| encode | Yes | |
| decode | Yes |
Bit
| Feature | Support State | Details |
|---|---|---|
| || | Yes | |
| & | Yes | |
| | | Yes | |
| # | Yes | |
| ~ | Yes | |
| << | Yes | |
| >> | Yes | |
| bit_count | Yes | |
| bit_length | Yes | |
| length | Yes | |
| octet_length | Yes | |
| overlay | No | |
| position | No | |
| substring | No | |
| get_bit | Yes | |
| set_bit | Yes |
Pattern Matching
| Feature | Support State | Details |
|---|---|---|
| LIKE | Yes | |
| SIMILAR TO | Yes | |
| regexp_count | Yes | |
| regexp_instr | Yes | |
| regexp_like | Yes | |
| regexp_match | Yes | |
| regexp_matches | Partial | |
| regexp_replace | Partial | The start and N arguments are not supported |
| regexp_split_to_array | Yes | |
| regexp_split_to_table | Yes | |
| regexp_substr | Yes |
Data Type Formatting
| Feature | Support State | Details |
|---|---|---|
| to_char | Yes | TZ prints the UTC offset for zones other than UTC and GMT |
| to_date | Yes | |
| to_number | Yes | The scale of the result is fixed per query |
| to_timestamp | Yes | TZ input recognizes UTC, GMT and numeric offsets |
Date/Time
| Feature | Support State | Details |
|---|---|---|
| + | Yes | |
| - | Yes | |
| * | Yes | |
| / | Yes | |
| age | Yes | |
| clock_timestamp | Yes | |
| pg_sleep_for | Yes | Also pg_sleep_until |
| current_date | Yes | |
| current_time | Yes | |
| current_timestamp | Yes | |
| date_add | Yes | possible with + |
| date_bin | Yes | |
| date_part | Yes | |
| date_subtract | Yes | |
| date_trunc | Yes | |
| extract | Yes | pg_catalog.extract(field, source) returns numeric. An infinite source gives NULL where PostgreSQL gives Infinity, julian of a timestamp has 20 fractional digits, and a field that is not a constant gives six fractional digits |
| isfinite | Yes | |
| justify_days | Yes | |
| justify_hours | Yes | |
| justify_interval | Yes | |
| localtime | Yes | |
| localtimestamp | Yes | |
| make_date | Yes | |
| make_interval | Yes | |
| make_time | Yes | |
| make_timestamp | Yes | |
| make_timestamptz | Yes | With time zone name |
| now | Yes | |
| statement_timestamp | Yes | |
| timeofday | Yes | |
| timestamp | Yes | Also timestamptz: the casts as functions, and timestamp(value, precision) rounds to the precision |
| transaction_timestamp | Yes | |
| to_timestamp | Yes | |
| OVERLAPS | Yes | |
| EXTRACT | Yes |
Enum
| Feature | Support State | Details |
|---|---|---|
| enum_first | Yes | |
| enum_last | Yes | |
| enum_range | Yes |
Geometric
An operator with a string literal on one side, as in point '(1,2)' <-> '(3,4)', is an error when the operator takes several types on that side: cast the literal, as in point '(1,2)' <-> point '(3,4)'. For spatial data, SereneDB also has a GEOMETRY type and ST_* functions: see Spatial Functions and the GEOMETRY type.
| Feature | Support State | Details |
|---|---|---|
| + | Yes | |
| - | Yes | |
| * | Yes | |
| / | Yes | |
| @-@ | Yes | |
| @@ | Yes | |
| # | Yes | |
| ## | Yes | |
| <-> | Yes | |
| @> | Yes | |
| <@ | Yes | |
| && | Yes | |
| << | Yes | |
| >> | Yes | |
| &< | Yes | |
| &> | Yes | |
| <<| | Yes | |
| |>> | Yes | |
| &<| | Yes | |
| |&> | Yes | |
| <^ | Yes | |
| >^ | Yes | |
| ?# | Yes | |
| ?- | Partial | Between two values only: a leading ? is a parameter, so ?- x is written line_horizontal(x) or lseg_horizontal(x) |
| ?| | Yes | A ? parameter followed by | reads as this operator, so a parameter's bitwise or is written $1 | x or (?) | x |
| ?-| | Yes | |
| ?|| | Yes | |
| ~= | Yes | |
| area | Yes | |
| center | Yes | |
| diagonal | Yes | |
| diameter | Yes | |
| height | Yes | |
| isclosed | Yes | |
| isopen | Yes | |
| length | Yes | |
| npoints | Yes | |
| pclose | Yes | |
| popen | Yes | |
| radius | Yes | |
| slope | Yes | |
| width | Yes | |
| box | Yes | |
| bound_box | Yes | |
| circle | Yes | |
| line | Yes | |
| lseg | Yes | |
| path | Yes | |
| point | Yes | |
| polygon | Yes |
Network
| Feature | Support State | Details |
|---|---|---|
| << | Yes | |
| <<= | Yes | |
| >> | Yes | |
| >>= | Yes | |
| && | Yes | |
| ~ | Yes | |
| & | Yes | |
| | | Yes | |
| + | Yes | |
| - | Yes | |
| abbrev | Yes | |
| broadcast | Yes | |
| family | Yes | |
| host | Yes | |
| hostmask | Yes | |
| inet_merge | Yes | |
| inet_same_family | Yes | |
| masklen | Yes | |
| netmask | Yes | |
| network | Yes | |
| set_masklen | Yes | |
| text | No |
Text Search
| Feature | Support State | Details |
|---|---|---|
| @@ | Yes | SereneDB full-text search (inverted index), not PG tsvector/tsquery |
| || | Yes | SereneDB full-text search (inverted index), not PG tsvector/tsquery |
| && | Yes | SereneDB full-text search (inverted index), not PG tsvector/tsquery |
| !! | Yes | SereneDB full-text search (inverted index), not PG tsvector/tsquery |
| <-> | No | |
| @> | No | |
| <@ | No | |
| array_to_tsvector | No | |
| get_current_ts_config | No | |
| length | No | |
| numnode | No | |
| plainto_tsquery | Yes | SereneDB full-text search (inverted index), not PG tsvector/tsquery |
| phraseto_tsquery | Yes | SereneDB full-text search (inverted index), not PG tsvector/tsquery |
| websearch_to_tsquery | Yes | SereneDB full-text search (inverted index), not PG tsvector/tsquery |
| querytree | No | |
| setweight | No | |
| strip | No | |
| to_tsquery | Yes | SereneDB full-text search (inverted index), not PG tsvector/tsquery |
| to_tsvector | No | |
| json(b)_to_tsvector | No | |
| ts_delete | No | |
| ts_filter | No | |
| ts_headline | No | |
| ts_rank | No | |
| ts_rank_cd | No | |
| ts_rewrite | No | |
| tsquery_phrase | Yes | SereneDB full-text search (inverted index), not PG tsvector/tsquery |
| tsvector_to_array | No | |
| unnest | No | |
| ts_debug | No | |
| ts_lexize | Yes | SereneDB extension: works with CREATE TEXT SEARCH DICTIONARY |
| ts_parse | No | |
| ts_token_type | No | |
| ts_stat | No |
UUID
| Feature | Support State | Details |
|---|---|---|
| get_random_uuid | Yes | |
| uuid_extract_timestamp | Yes | |
| uuid_extract_version | Yes |
XML
| Feature | Support State | Details |
|---|---|---|
| xmltext | Yes | |
| xmlcomment | Yes | |
| xmlconcat | Yes | |
| xmlelement | No | |
| xmlforest | No | |
| xmlpi | No | |
| xmlroot | No | |
| xmlagg | Yes | |
IS DOCUMENT | Yes | |
IS NOT DOCUMENT | Yes | |
| XMLEXISTS | No | |
| xml_is_well_formed | Yes | Also xml_is_well_formed_document and xml_is_well_formed_content |
| xpath | Yes | |
| xpath_exists | Yes | |
| XMLTABLE | No |
JSON
Most of the PostgreSQL json functions below are supported. SereneDB also provides JSON access through the JSON column type and SereneDB-style functions -- json_extract, json_extract_string, json_keys, json_type, json_valid and more (see JSON Functions). The PostgreSQL accessor operators ->, ->>, #> and #>> are supported.
The jsonb type is a json value that keeps its text as written (see jsonb); the jsonb_* functions and the jsonb-only operators are unavailable. A query parameter a client sends as jsonb is read as JSON.
| Feature | Support State | Details |
|---|---|---|
-> | Yes | |
->> | Yes | |
#> | Yes | |
#>> | Yes | |
@> | No | Use json_contains() |
<@ | No | Use json_contains() |
? | No | Use json_exists() |
?| | No | Use json_exists() |
?& | No | Use json_exists() |
|| | Yes | |
- | No | |
#- | No | |
@? | No | Use json_exists() |
@@ | No | No JSON-path predicate |
| to_json | Yes | |
| to_jsonb | Yes | Returns json, as to_json does |
| array_to_json | Yes | |
| json_array | Yes | |
| row_to_json | Yes | |
| json_build_array | Yes | Same as json_array() |
| jsonb_build_array | No | |
| json_build_object | Yes | Same as json_object(key, value) |
| jsonb_build_object | No | |
| json_object | Partial | Two text arguments are one key, value pair, as for json_build_object: the arrays of json_object('{a,b}', '{1,2}') need ::text[] |
| jsonb_object | No | |
IS JSON | No | Use json_valid() |
| json_array_elements | Yes | |
| jsonb_array_elements | No | |
| json_array_elements_text | Yes | |
| jsonb_array_elements_text | No | |
| json_array_length | Yes | |
| jsonb_array_length | No | Use json_array_length |
| json_each | Yes | |
| jsonb_each | No | |
| json_each_text | Yes | |
| jsonb_each_text | No | |
| json_object_keys | Yes | |
| jsonb_object_keys | No | |
| json_populate_record | Yes | |
| jsonb_populate_record | No | |
| json_populate_recordset | Yes | |
| jsonb_populate_recordset | No | |
| json_to_record | No | Use json_populate_record |
| jsonb_to_record | No | |
| json_to_recordset | No | Use json_populate_recordset |
| jsonb_to_recordset | No | |
| jsonb_set | No | |
| jsonb_set_lax | No | |
| jsonb_insert | No | |
| json_strip_nulls | Yes | |
| jsonb_strip_nulls | No | Use json_strip_nulls |
| json_extract_path | Yes | |
| json_extract_path_text | Yes | |
| json_path | No | No jsonpath type |
Sequence Manipulation
| Feature | Support State | Details |
|---|---|---|
| nextval | Yes | |
| setval | Yes | |
| currval | Yes | |
| lastval | Yes |
Conditional
| Feature | Support State | Details |
|---|---|---|
| CASE | Yes | |
| COALESCE | Yes | |
| NULLIF | Yes | |
| GREATEST | Yes | |
| LEAST | Yes |
Array
| Feature | Support State | Details |
|---|---|---|
| @> | Yes | |
| <@ | Yes | |
| && | Yes | |
| || | Yes | Not for multidimensional |
| array_append | Yes | |
| array_cat | Yes | |
| array_dims | Yes | |
| array_fill | Yes | Lower bounds must be 1; dimensions taken from a column fill one-dimensional arrays unless written as ARRAY[...]; up to 6 dimensions |
| array_lower | Yes | |
| array_ndims | Yes | |
| array_position | Yes | |
| array_positions | Yes | |
| array_prepend | Yes | |
| array_remove | Yes | |
| array_replace | Yes | |
| array_sample | Yes | |
| array_shuffle | Yes | |
| array_to_string | Yes | |
| array_upper | Yes | |
| array_cardinality | Yes | via cardinality() |
| trim_array | Yes | |
| unnest | Yes |
Range
| Feature | Support State | Details |
|---|---|---|
| @> | Yes | A string literal on the right is read as an element, not a range or multirange: write r @> '[2,3)'::int4range to test a range |
| <@ | Yes | A string literal on the left is read as an element, not a range or multirange: write '[2,3)'::int4range <@ r to test a range |
| && | Yes | |
| << | Yes | |
| >> | Yes | |
| &< | Yes | |
| &> | Yes | |
| -|- | Yes | |
| + | Yes | |
| * | Yes | |
| - | Yes | |
| lower | Yes | |
| upper | Yes | |
| isempty | Yes | |
| lower_inc | Yes | |
| upper_inc | Yes | |
| lower_inf | Yes | |
| upper_inf | Yes | |
| range_merge | Yes | |
| multirange | Yes | |
| unnest | Yes | |
| CREATE TYPE ... AS RANGE | No |
Aggregate Functions
Generic
| Feature | Support State | Details |
|---|---|---|
| any_value | Yes | Aggregate functions |
| array_agg | Yes | Aggregate functions |
| avg | Yes | Aggregate functions |
| bit_and | Yes | Aggregate functions |
| bit_or | Yes | Aggregate functions |
| bit_xor | Yes | Aggregate functions |
| bool_and | Yes | Aggregate functions |
| bool_or | Yes | Aggregate functions |
count(*) | Yes | Aggregate functions |
count("any") | Yes | Aggregate functions |
| json(b)_agg | Partial | json_agg and json_agg_strict, not jsonb_agg: JSON aggregate functions |
| json(b)_objectagg | No | |
| json(b)_object_agg | Partial | json_object_agg and its _strict, _unique and _unique_strict forms, not jsonb_object_agg: JSON aggregate functions |
| json_arrayagg | No | |
| max | Yes | Aggregate functions |
| min | Yes | Aggregate functions |
| range(_intersect)_agg | Yes | |
| string_agg | Yes | |
| sum | Yes | Aggregate functions |
| xmlagg | Yes |
Statistical
| Feature | Support State | Details |
|---|---|---|
| corr | Yes | |
| covar_pop | Yes | |
| covar_samp | Yes | |
| regr_avgx | Yes | |
| regr_avgy | Yes | |
| regr_count | Yes | |
| regr_intercept | Yes | |
| regr_r2 | Yes | |
| regr_slope | Yes | |
| regr_sxx | Yes | |
| regr_sxy | Yes | |
| regr_syy | Yes | |
| stddev | Yes | Aggregate functions |
| stddev_pop | Yes | Aggregate functions |
| stddev_samp | Yes | Aggregate functions |
| variance | Yes | Aggregate functions |
| var_pop | Yes | Aggregate functions |
| var_samp | Yes | Aggregate functions |
Ordered-Set
| Feature | Support State | Details |
|---|---|---|
| mode | Yes | |
| percentile_cont | Yes | The fraction must be a constant |
| percentile_disc | Yes | The fraction must be a constant |
Window
| Feature | Support State | Details |
|---|---|---|
| row_number | Yes | |
| rank | Yes | |
| dense_rank | Yes | |
| percent_rank | Yes | |
| cume_dist | Yes | |
| ntile | Yes | |
| lag | Yes | |
| lead | Yes | |
| first_value | Yes | |
| last_value | Yes | |
| nth_value | Yes |
Subquery
| Feature | Support State | Details |
|---|---|---|
| EXISTS | Yes | |
| IN | Yes | |
| NOT IN | Yes | |
| ANY/SOME | Yes | |
| ALL | Yes |
Array Comparison
| Feature | Support State | Details |
|---|---|---|
| EXISTS | Yes | |
| IN | Yes | |
| NOT IN | Yes | |
| ANY/SOME | Yes | |
| ALL | Yes |
Set Returning
| Feature | Support State | Details |
|---|---|---|
| generate_series | Yes | |
| generate_subscript | Yes |
System Information
| Feature | Support State | Details |
|---|---|---|
| pg_get_constraintdef | Yes | CHECK expressions are printed in SereneDB's form (for example != and no casts) |
| pg_get_serial_sequence | Yes | |
| pg_get_indexdef | Yes | The access method is secondary or inverted. An inverted index shows its column dictionaries and options, INCLUDE columns and settings; ordering (DESC, NULLS FIRST) is not stored, so it is not shown |
| pg_get_viewdef | Yes | The view's query in SereneDB's one-line form |
| pg_get_ruledef | Yes | The _RETURN rule of a view, around the same query text |
| pg_get_triggerdef | Yes | A trigger runs a statement, which takes the place of EXECUTE FUNCTION |
| pg_get_functiondef | Yes | The body is printed in SereneDB's form; a DuckDB macro is printed as CREATE MACRO |
| pg_get_function_arguments | Yes | |
| pg_get_function_identity_arguments | Yes | |
| pg_get_function_result | Yes | |
| pg_get_function_arg_default | Yes | |
| pg_get_function_sqlbody | Partial | Always NULL: the body of a SQL function is kept as source text, shown in prosrc and by pg_get_functiondef |
| pg_get_partkeydef | Yes | Always NULL, as is pg_get_partition_constraintdef: there are no partitioned tables |
| pg_get_statisticsobjdef | Yes | Always NULL, as are pg_get_statisticsobjdef_columns and pg_get_statisticsobjdef_expressions: there are no extended statistics |
| pg_get_serial_sequence | Yes | Finds the sequence named by the column's nextval(...) default |
| pg_get_replica_identity_index | Yes | The index of the table's primary key; NULL for a table without one |
| format_type | Yes | Prints type modifiers, for example numeric(10,2), character varying(50) and timestamp(3) without time zone |
| to_regclass | Yes | Also to_regtype, to_regtypemod, to_regproc, to_regprocedure, to_regnamespace, to_regrole, to_regcollation, to_regoper and to_regoperator; they return NULL where the cast would fail |
| pg_input_is_valid | Yes | Also pg_input_error_info. For bytea, cidr, pg_lsn and the reg* types it reports PostgreSQL's message, detail, hint and SQLSTATE; for other types the message of the failed cast, with SQLSTATE 22P02 |
| pg_get_userbyid | Yes | |
| getpgusername | Yes | |
| pg_basetype | Yes | |
| pg_collation_for | Yes | An untyped string literal is text, so it has the default collation |
| system_user | Yes | Also the SYSTEM_USER keyword. The authentication method and the role, for example scram-sha-256:alice; NULL for a connection a trust rule admits and for the HTTP API |
| pg_get_keywords | Yes | Lists the keywords of SereneDB's parser, among them pivot and qualify, which PostgreSQL does not reserve. A word the parser takes both as a column name and as a type or function name, such as struct, is listed once, as unreserved. barelabel and baredesc are NULL: the parser does not say which keywords can be a bare column label |
| pg_table_is_visible | Yes | Also pg_type_is_visible and pg_function_is_visible. Functions are compared by name, not by argument types |
| obj_description | Yes | Also col_description. Built-in types, functions and schemas have no description, so they return NULL |
| shobj_description | Partial | Always NULL: comments on databases and roles are not stored |
| pg_describe_object | Partial | Also pg_identify_object. Tables, views, indexes, sequences, composite types and their columns, functions, types, schemas, roles, constraints, column defaults, view rules, triggers, databases, collations, languages, access methods and tablespaces; other object classes return NULL |
| pg_get_acl | Partial | Tables, views, sequences and their columns, functions, types and schemas; other object classes return NULL |
| pg_relation_is_updatable | Yes | Also pg_column_is_updatable. Tables are updatable, views are not |
| pg_sequence_last_value | Yes | |
| pg_sequence_parameters | Yes | Also pg_get_sequence_data. data_type is always bigint |
| pg_options_to_table | Yes | |
| pg_encoding_to_char | Yes | Also pg_char_to_encoding |
| pg_tablespace_location | Yes | Always an empty string |
| pg_tablespace_size | Yes | pg_default holds every database and pg_global the catalog that all databases share |
| pg_tablespace_databases | Yes | pg_default lists every database |
| pg_size_bytes | Yes | |
| pg_column_size | Partial | Counts a value as it is held in memory: a value read from a table counts as a computed one, so a short string takes 4 bytes more than its length. A whole row, as in pg_column_size(t), counts as in PostgreSQL |
| pg_column_compression | Yes | Always NULL, as is pg_column_toast_chunk_id: values are not compressed or stored out of line one by one |
| pg_my_temp_schema | Partial | Always 0 |
| acldefault | Yes | Also aclexplode |
| makeaclitem | Yes | |
| has_table_privilege | Yes | Also has_column_privilege, has_any_column_privilege, has_sequence_privilege, has_schema_privilege, has_database_privilege, has_function_privilege, has_type_privilege and pg_has_role, with the role and the object given by name or by oid |
| has_server_privilege | Yes | Also has_foreign_data_wrapper_privilege (clickhouse_fdw, iceberg_fdw and postgres_fdw) and has_tablespace_privilege (pg_default and pg_global) |
| has_parameter_privilege | Yes | true for superusers and false for other roles: privileges on settings cannot be granted |
| has_largeobject_privilege | Yes | Always NULL: there are no large objects |
| pg_settings_get_flags | Partial | An empty array for every setting, NULL for an unknown name |
| unicode_version | Yes | Also icu_unicode_version and unicode_assigned; both versions are 17.0 |
| pg_current_logfile | Yes | Always NULL: there is no logging collector |
| pg_jit_available | Yes | Always false, as is pg_numa_available |
| pg_collation_actual_version | Yes | Also pg_database_collation_actual_version; always NULL: collations are not versioned |
| pg_safe_snapshot_blocking_pids | Yes | Also pg_blocking_pids; always an empty array |
| pg_available_wal_summaries | Yes | Returns no rows |
| pg_extension_update_paths | Yes | Fails for every extension name: no extension is available |
| pg_wal_lsn_diff | Yes | Also pg_lsn(numeric) |
| pg_restore_relation_stats | Partial | Also pg_restore_attribute_stats; accept the arguments pg_dump --statistics writes and return false: imported statistics are not kept |
| pg_clear_relation_stats | Yes | Also pg_clear_attribute_stats; there are no imported statistics to clear |
| pg_stat_get_* | Partial | pg_stat_get_live_tuples returns the table's row count and pg_stat_get_db_numbackends the database's sessions. pg_stat_get_db_xact_commit, pg_stat_get_db_xact_rollback, pg_stat_get_db_sessions and pg_stat_get_db_session_time count from server start. Other counters and durations, function statistics included, are 0, and timestamps NULL |
Behavioral Differences from PostgreSQL
Even where a feature is supported, SereneDB intentionally diverges from PostgreSQL in a few places. SereneDB's SQL dialect closely follows PostgreSQL conventions; the exceptions are listed below.
Floating-Point Arithmetic
A division by zero raises a Division by zero error with SQLSTATE 22012, as in PostgreSQL, and operations involving infinity values follow the IEEE Standard for Floating-Point Arithmetic (IEEE 754) on both engines. One case differs: 'NaN'::float8 / 0 raises the error too, where PostgreSQL returns NaN. SET ieee_floating_point_ops = true makes a division by zero return Infinity, NaN or -Infinity instead. In SereneDB:
SQL
SELECT 1.0 / 0.0 AS x;
SELECT 'Infinity'::FLOAT / 'Infinity'::FLOAT AS x;
SELECT 1.0 / 'Infinity'::FLOAT AS x;
SELECT 'Infinity'::FLOAT - 'Infinity'::FLOAT AS x;
SELECT 'Infinity'::FLOAT - 1.0 AS x;db error: ERROR: Division by zero in expression (1 // 0). Use TRY(...) to return NULL for this expression, or SET error_on_division_by_zero=false to return NULL for all divisions by zero. x ----- NaN x --- 0 x ----- NaN x ---------- Infinity
On an attached PostgreSQL:
SQL
-- PostgreSQL raises an error on division by zero (SereneDB returns Infinity per IEEE 754)SELECT * FROM postgres_query('pg', 'SELECT 1.0 / 0.0');db error: ERROR: Failed to prepare COPY "COPY (SELECT "?column?" FROM (SELECT 1.0 / 0.0) AS __unnamed_subquery ) TO STDOUT (FORMAT "binary");": ERROR: division by zero
Mathematical functions raise PostgreSQL's errors for an argument outside their domain, such as ln(0) or sqrt(-1). A result too large for double precision, such as exp(1000), is Infinity, where PostgreSQL reports an overflow. With ieee_floating_point_ops on, an argument outside the domain gives NaN or ±Infinity too:
SQL
SELECT ln(0) AS ln_zero;
SELECT sqrt(-1) AS sqrt_negative;
SELECT exp(1000) AS exp_overflow;db error: ERROR: cannot take logarithm of zero db error: ERROR: cannot take square root of a negative number exp_overflow -------------- Infinity
Integer Division Operator (//)
Integer division itself behaves exactly like PostgreSQL: when both operands are integers, / performs integer division, so both engines return 0 here:
SQL
SELECT 1 / 2 AS x;x --- 0
The divergence is that SereneDB additionally accepts the // operator as an explicit integer-division spelling, which PostgreSQL does not provide (on PostgreSQL // raises operator does not exist):
SQL
SELECT 1 // 2 AS x;x --- 0
This returns 0.
UNION of Boolean and Integer Values
The following query fails in PostgreSQL but successfully completes in SereneDB:
SQL
SELECT true AS xUNIONSELECT 2;x --- 1 2
On an attached PostgreSQL, the same query raises an error:
SQL
-- PostgreSQL rejects a UNION of boolean and integerSELECT * FROM postgres_query('pg', 'SELECT true UNION SELECT 1');db error: ERROR: Failed to prepare query "SELECT true UNION SELECT 1": ERROR: UNION types boolean and integer cannot be matched
LINE 1: SELECT true UNION SELECT 1
^SereneDB instead performs an enforced cast and completes the query, returning the rows shown above.
Implicit Casting on Equality Checks
SereneDB performs implicit casting on equality checks, e.g., converting strings to numeric and boolean values.
Therefore, there are several instances where PostgreSQL throws an error while SereneDB successfully computes the result. Each expression below is evaluated on both engines -- the PostgreSQL column runs it on an attached PostgreSQL via postgres_query, the SereneDB column evaluates it locally:
SQL
ATTACH 'host=localhost port=5432 dbname=mydb user=postgres' AS pg (TYPE postgres);
SELECT '''1.1'' = 1' AS "Expression", 'error' AS "PostgreSQL", COALESCE(TRY(('1.1' = 1)::text), 'error') AS "SereneDB"UNION ALL SELECT '''1.1'' = 1.1', (SELECT r FROM postgres_query('pg', 'SELECT (''1.1'' = 1.1)::text r')), COALESCE(TRY(('1.1' = 1.1)::text), 'error')UNION ALL SELECT '1 = 1.1', (SELECT r FROM postgres_query('pg', 'SELECT (1 = 1.1)::text r')), COALESCE(TRY((1 = 1.1)::text), 'error')UNION ALL SELECT 'true = ''true''',(SELECT r FROM postgres_query('pg', 'SELECT (true = ''true'')::text r')), COALESCE(TRY((true = 'true')::text),'error')UNION ALL SELECT 'true = 1', 'error', COALESCE(TRY((true = 1)::text), 'error')UNION ALL SELECT '''true'' = 1', 'error', COALESCE(TRY(('true' = 1)::text), 'error');Expression | PostgreSQL | SereneDB ---------------+------------+---------- '1.1' = 1 | error | true '1.1' = 1.1 | true | true 1 = 1.1 | false | false true = 'true' | true | true true = 1 | error | true 'true' = 1 | error | error
Case Sensitivity for Quoted Identifiers
PostgreSQL lowercases unquoted identifiers and keeps the case of quoted ones. A SereneDB server does the same, so the following command creates a table named mytable, and the query for "MyTaBLe" fails because quotes keep the case:
SQL
CREATE TABLE MyTaBLe (x INTEGER);
SELECT * FROM "MyTaBLe";db error: ERROR: Table with name MyTaBLe does not exist! Did you mean "mytable"?
The rule works both ways: a table created with a quoted name is not found under an unquoted one.
SQL
CREATE TABLE "PreservedCase" (x INTEGER);
SELECT * FROM PreservedCase;db error: ERROR: Table with name preservedcase does not exist! Did you mean "PreservedCase"?
Case insensitivity therefore only works if you never quote identifiers with different cases. Column names follow the same rule; aliases inside one query, such as the output columns of a subquery, match regardless of case.
serened shell keeps DuckDB's behavior instead: identifiers are case-insensitive everywhere, even when quoted, and keep the case they were created with. A server session gets the same with the preserve_identifier_case option, under which the scripts above complete successfully:
SQL
SET preserve_identifier_case = true;
CREATE TABLE MyOtheRTaBLe (x INTEGER);
SELECT * FROM "MyOtheRTaBLe";
CREATE TABLE "PreservedCase" (x INTEGER);
SELECT * FROM PreservedCase;
SELECT tablename FROM pg_tables;x x tablename --------------- MyOtheRTaBLe PreservedCase
A server runs with preserve_identifier_case = false, which stores unquoted names in lowercase:
SQL
SET preserve_identifier_case = false;
CREATE TABLE MyTaBLe (x INTEGER);
SELECT tbl FROM sdb_tables();tbl --------- mytable
Using Double Equality Sign for Comparison
SereneDB supports both = and == for equality comparison, while PostgreSQL only supports =.
SQL
SELECT 1 == 1 AS t;t --- t
SereneDB returns true, while on an attached PostgreSQL the same query errors (== is not a PostgreSQL operator):
SQL
-- PostgreSQL has no == operatorSELECT * FROM postgres_query('pg', 'SELECT 1 == 1');db error: ERROR: Failed to prepare query "SELECT 1 == 1": ERROR: operator does not exist: integer == integer
LINE 1: SELECT 1 == 1
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.Note that the use of == is not encouraged due to its limited portability.
Vacuuming Tables
In PostgreSQL, the VACUUM statement garbage collects tables and analyzes tables.
In SereneDB, the VACUUM statement is only used to rebuild statistics.
To reclaim space, use the CHECKPOINT statement or compact the database by creating a fresh copy with the COPY FROM DATABASE statement.
Strings
SereneDB escapes characters such as ' when it serializes strings inside nested data structures -- for example, casting a list that contains a quote to text:
SQL
SELECT (['a''b'])::VARCHAR AS escaped;escaped ----------- ['a\\'b']
PostgreSQL's array-to-text conversion leaves the quote unescaped instead:
SQL
-- PostgreSQL's array-to-text leaves the quote unescaped (compare with SereneDB's escaped list cast)SELECT r AS "PostgreSQL" FROM postgres_query('pg', 'SELECT (ARRAY[''a''''b''])::text r'); PostgreSQL
------------
{a'b}Functions
regexp_extract Function
Unlike PostgreSQL's regexp_substr function, SereneDB's regexp_extract returns empty strings instead of NULLs when there is no match:
SQL
SELECT regexp_extract('abc', 'x') AS result, regexp_extract('abc', 'x') IS NULL AS is_null;result | is_null ---------+--------- (empty) | f
date_part Function
Most parts extracted by the date_part function are returned as integers. Since there are no infinite integer values in SereneDB, NULLs are returned for infinite timestamps:
SQL
SELECT date_part('year', TIMESTAMP '2021-08-03 11:59:44.123456') AS finite, date_part('year', 'infinity'::TIMESTAMP) AS infinite;finite | infinite --------+---------- 2021 | NULL
Resolution of Type Names in the Schema
For CREATE TABLE statements, SereneDB attempts to resolve type names in the schema where a table is created. For example:
SQL
CREATE SCHEMA myschema;
CREATE TYPE myschema.mytype AS ENUM ('as', 'df');
CREATE TABLE myschema.mytable (v mytype);On an attached PostgreSQL, the same CREATE TABLE errors because PostgreSQL does not resolve the unqualified type name in the table's schema:
SQL
-- PostgreSQL does not resolve the unqualified type name (SereneDB resolves it in the table's schema)CALL postgres_execute('pg', 'CREATE TABLE mytable (v mytype)');db error: ERROR: Failed to execute query "(varies)CREATE TABLE mytable (v mytype)": ERROR: type "mytype" does not exist
LINE (varies): CREATE TABLE mytable (v mytype)
^SereneDB runs the statement and creates the table successfully, confirmed by the following query:
SQL
DESCRIBE myschema.mytable;column_name | column_type | null | key | default | extra -------------+-------------+------+------+---------+------- v | mytype | YES | NULL | NULL | NULL
Exploiting Functional Dependencies for GROUP BY
PostgreSQL can exploit functional dependencies, such as i -> j in the following query, and runs it. SereneDB instead raises the error shown below:
SQL
CREATE TABLE tbl (i INTEGER, j INTEGER, PRIMARY KEY (i));
SELECT jFROM tblGROUP BY i;db error: ERROR: column "j" must appear in the GROUP BY clause or must be part of an aggregate function.
Either add it to the GROUP BY list, or use ANY_VALUE("j") if the exact value of "j" is not important.To work around this, add the other attributes or use the GROUP BY ALL clause.