PostgreSQL Compatibility

SereneDB implements the SQL dialect of PostgreSQL. Not every PostgreSQL statement parses, and not all underlying functionality is implemented yet. Among the statements that fail to parse are SAVEPOINT, DECLARE ... CURSOR and FETCH, SELECT ... FOR UPDATE, FETCH FIRST, 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 yet.

This page gives a non-exhaustive overview of currently supported core SQL functionality, followed by the behavioral differences where SereneDB intentionally diverges from PostgreSQL. SereneDB strives for full PostgreSQL compatibility, and most features not currently supported will be added over time.

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	No	See issue

Table Modification (ALTER TABLE)

Feature	Support State	Details
ADD COLUMN	Yes
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

Privileges

Feature	Support State	Details
CREATE ROLE	Yes	See Database Roles
OWNER TO	Yes	Tables, views, sequences, schemas, types, jobs; not databases/functions yet
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	Partial	Accepted and stored, not yet applied to new objects
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
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

Type	Support State	Details
array	Yes
bigint	Yes
bigserial	Yes
bit [ (n) ]	Yes
bit varying [ (n) ]	Yes
boolean	Yes
box	No
bytea	Yes
character [ (n) ]	Yes	CHAR(n) not supported; use TEXT
character varying [ (n) ]	Yes	VARCHAR works, VARCHAR(n) does not
cidr	No
circle	No
date	Yes
double precision	Yes
inet	No
integer	Yes
interval [ fields ] [ (p) ]	Yes
json	Yes
jsonb	No
line	No
lseg	No
macaddr	No
macaddr8	No
money	No
numeric [ (p, s) ]	Yes
path	No
pg_lsn	No
pg_snapshot	No
point	No
polygon	No
real	Yes
smallint	Yes
smallserial	Yes
serial	Yes
text	Yes
time [ (p) ] [ without time zone ]	Partial
time [ (p) ] with time zone	Yes
timestamp [ (p) ] [ without time zone ]	Yes
timestamp [ (p) ] with time zone	Yes
tsquery	Yes	SereneDB TSQUERY (inverted index), not PG tsvector/tsquery
tsvector	No
txid_snapshot	No
uuid	Yes
xml	No

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
%	Yes
^	Yes
	/	Yes
		/
@	Yes
&	Yes
		Yes
#	No
~	Yes
<<	Yes
>>	Yes
abs	Yes
cbrt	Yes
ceil	Yes
degrees	Yes
div	Yes
erf	Yes
erfc	Yes
exp	Yes
factorial	Yes
floor	Yes
gcd	Yes
lcm	Yes
ln	Yes
log	Yes
log10	Yes
min_scale	No
mod	Yes
pi	Yes
power	Yes
radians	Yes
round	Yes
scale	No
sign	Yes
sqrt	Yes
trim_scale	No
trunc	Yes
width_bucket	Yes
random	Yes
random_normal	No
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	No
bit_length	Yes
char_length	Yes
lower	Yes
lpad	Yes
ltrim	Yes
normalize	Yes
octet_length	Yes
overlay	Yes
position	Yes
rpad	Yes
rtrim	Yes
substring	Yes	Currently not supporting regular expression arguments
trim	Yes
upper	Yes
^@	Yes
ascii	Yes
chr	Yes
concat	Yes
concat_ws	Yes
format	Yes
initcap	Yes
left	Yes
length	Yes
md5	Yes
parse_ident	No
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	No
strpos	Yes
substr	Yes
to_ascii	No
to_hex	Yes
translate	Yes
unistr	No

Bytea

Feature	Support State	Details
||	Yes
bit_length	Yes
btrim	No
ltrim	Yes
octet_length	Yes
overlay	Yes
position	Yes
rtrim	Yes
substring	Yes
trim	Yes
bit_count	No
get_bit	Yes
get_byte	Yes
length	Yes
md5	Yes
set_bit	Yes
set_byte	Yes
sha224	No
sha384	No
sha512	No
substr	Yes
convert	No
convert_from	Yes
convert_to	Yes
encode	Yes
decode	Yes

Bit

Feature	Support State	Details
||	Yes
&	Yes
|	Yes
#	No
~	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	Currently not supporting replacing N’th match
regexp_split_to_array	Yes
regexp_split_to_table	Yes
regexp_substr	Yes

Data Type Formatting

Feature	Support State	Details
to_char	No
to_date	No
to_number	No
to_timestamp	Partial	Epoch (numeric) form only; text+format parsing unsupported

Date/Time

Feature	Support State	Details
+	Yes
-	Yes
*	Yes
/	Yes
age	Yes
clock_timestamp	Yes
current_date	Yes
current_time	Yes
current_timestamp	Yes
date_add	Yes	possible with +
date_bin	Yes
date_part	Yes
date_subtract	No	possible with -
date_trunc	Yes	Without timezone
extract	Yes
isfinite	Yes
justify_days	No
justify_hours	No
justify_interval	No
localtime	Yes
localtimestamp	Yes
make_date	Yes
make_interval	No
make_time	Yes
make_timestamp	Yes
make_timestamptz	Yes	With time zone name
now	Yes
statement_timestamp	No
timeofday	Yes
transaction_timestamp	Yes
to_timestamp	Partial	Epoch (numeric) form only; text+format parsing unsupported
OVERLAPS	No
EXTRACT	Yes

Enum

Feature	Support State	Details
enum_first	Yes
enum_last	Yes
enum_range	Yes

Geometric

PostgreSQL's geometric types (point, line, lseg, box, path, polygon, circle) and their operators/functions are not supported. SereneDB instead provides spatial data through a built-in GEOMETRY type and ST_* functions (ST_Point, ST_Intersects, ST_Contains, ST_Distance, …). See Spatial Functions and the GEOMETRY type.

Feature	Support State	Details
+	No
-	No
*	No
/	No
@-@	No
@@	No
#	No
##	No
<->	No
@>	No
<@	No
&&	No
<<	No
>>	No
&<	No
&>	No
<<| No	No
|>>	No
&<| No	No
	&>	No
<^	No
>^	No
?#	No
?-	No
?		No
?-	No	No
?
~=	No
area	No
center	No
diagonal	No
diameter	No
height	No
isclosed	No
isopen	No
length	No
npoints	No
pclose	No
popen	No
radius	No
slope	No
width	No
box	No
bounding_box	No
circle	No
line	No
lseg	No
path	No
point	No
polygon	No

Network

Feature	Support State	Details
<<	No
<<=	No
>>	No
>>=	No
&&	No
~	No
&	No
|	No
+	No
-	No
abbrev	No
broadcast	No
family	No
host	No
hostmask	No
inet_merge	No
inet_same_family	No
masklen	No
netmask	No
network	No
set_masklen	No
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	No
xmlcomment	No
xmlconcat	No
xmlelement	No
xmlforest	No
xmlpi	No
xmlroot	No
xmlagg	No
IS DOCUMENT	No
IS NOT DOCUMENT	No
XMLEXISTS	No
xml_is_well_formed	No
xpath	No
xpath_exists	No
XMLTABLE	No

JSON

The PostgreSQL-named JSON functions below are largely unsupported. SereneDB instead 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.

Binary JSON (jsonb) is not supported, so every jsonb_* function and the jsonb-only operators are unavailable.

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	No	Use to_json
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	Requires even arg count; SereneDB json_object(key, value)
jsonb_object	No
IS JSON	No	Use json_valid()
json_array_elements	No	Use json_each()
jsonb_array_elements	No
json_array_elements_text	No	Use json_extract_string()
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	No	Use json_each()
jsonb_each_text	No
json_object_keys	No	Use json_keys()
jsonb_object_keys	No
json_populate_record	No	Use json_transform()
jsonb_populate_record	No
json_populate_recordset	No	Use json_transform()
jsonb_populate_recordset	No
json_to_record	No	Use json_transform()
jsonb_to_record	No
json_to_recordset	No	Use json_transform()
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	Single-arg form needs a cast; multi-arg works
json_extract_path_text	Yes	Single-arg form needs a cast; multi-arg works
json_path	No	No jsonpath type

Sequence Manipulation

Feature	Support State	Details
nextval	Yes
setval	Yes
currval	Yes
lastval	No

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	No
array_lower	Yes
array_ndims	Yes
array_position	Yes
array_positions	Yes
array_prepend	Yes
array_remove	Yes
array_replace	Yes
array_sample	No
array_shuffle	No
array_to_string	Yes
array_upper	Yes
array_cardinality	Yes	via cardinality()
trim_array	Yes
unnest	Yes	No multi-array expansion

Range

Feature	Support State	Details
@>	No
<@	No
&&	No
<<	No
>>	No
&<	No
&>	No
-| No	No
+	No
*	No
-	No
lower	No
upper	No
isempty	No
lower_inc	No
upper_inc	No
lower_inf	No
upper_inf	No
range_merge	No
multirange	No
unnest	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	No
json(b)_objectagg	No
json(b)_object_agg	No
json_arrayagg	No
max	Yes	Aggregate functions
min	Yes	Aggregate functions
range(_intersect)_agg	No
string_agg	Yes
sum	Yes	Aggregate functions
xmlagg	No

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
percentile_disc	Yes

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_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_partkeydef	Yes	Always NULL: 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

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

SereneDB and PostgreSQL handle floating-point arithmetic differently for division by zero. SereneDB conforms to the IEEE Standard for Floating-Point Arithmetic (IEEE 754) for both division by zero and operations involving infinity values. PostgreSQL returns an error for division by zero but aligns with IEEE 754 for handling infinity values. In SereneDB:

SQL

SELECT 1.0 / 0.0 AS x;
SELECT 0.0 / 0.0 AS x;
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;

 x
----------
 Infinity

 x
-----
 NaN

 x
-----------
 -Infinity

 x
-----
 NaN

 x
---
 0

 x
-----
 NaN

 x
----------
 Infinity

On an attached PostgreSQL, the same division by zero raises an error instead (operations involving infinity follow IEEE 754 on both engines):

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

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

to_date Function

SereneDB does not support the to_date PostgreSQL date formatting function. Instead, please use the strptime function:

SQL

SELECT to_date('2024-01-01', 'YYYY-MM-DD');

db error: ERROR: Scalar Function with name to_date does not exist!
Did you mean "to_days"?

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.