CockroachDB supports the PostgreSQL wire protocol and the majority of its syntax. This means that your existing applications can often be migrated to CockroachDB without changing application code.
CockroachDB is compatible with PostgreSQL 9.5 and works with majority of PostgreSQL database tools such as Dbeaver, Intellij, pgdump and so on. Consult this link for a full list of supported third-party database tools. CockroachDB also works with most PostgreSQL drivers and ORMs.
However, CockroachDB does not support some of the PostgreSQL features or behaves differently from PostgreSQL because these features cannot be easily implemented in a distributed system. This page documents the known list of differences between PostgreSQL and CockroachDB for identical input. That is, a SQL statement of the type listed here will behave differently than in PostgreSQL. Porting an existing application to CockroachDB will require changing these expressions.
ENUM type.Unsupported Features
- Stored procedures and functions
- Triggers
- Events
- User-defined functions
- User-defined schemas
- FULLTEXT functions and indexes
- GEOSPATIAL functions and indexes
- Drop primary key
- XML Functions
- Savepoints
- Column-level privileges
- CREATE TEMPORARY TABLE syntax
- XA syntax
Features that differ from PostgreSQL
Note, some of these differences below only apply to rare inputs, and so no change will be needed, even if the listed feature is being used. In these cases, it is safe to ignore the porting instructions.
Overflow of float
In PostgreSQL, the float type returns an error when it overflows or an expression would return Infinity:
postgres=# select 1e300::float * 1e10::float;
ERROR: value out of range: overflow
postgres=# select pow(0::float, -1::float);
ERROR: zero raised to a negative power is undefined
In CockroachDB, these expressions instead return Infinity:
SELECT 1e300::float * 1e10::float;
+----------------------------+
| 1e300::FLOAT * 1e10::FLOAT |
+----------------------------+
| +Inf |
+----------------------------+
SELECT pow(0::float, -1::float);
+---------------------------+
| pow(0::FLOAT, - 1::FLOAT) |
+---------------------------+
| +Inf |
+---------------------------+
Precedence of unary ~
In PostgreSQL, the unary ~ (bitwise not) operator has a low precedence. For example, the following query is parsed as ~ (1 + 2) because ~ has a lower precedence than +:
SELECT ~1 + 2
In CockroachDB, unary ~ has the same (high) precedence as unary -, so the above expression will be parsed as (~1) + 2.
Porting instructions: Manually add parentheses around expressions that depend on the PostgreSQL behavior.
Precedence of bitwise operators
In PostgreSQL, the operators | (bitwise OR), # (bitwise XOR), and & (bitwise AND) all have the same precedence.
In CockroachDB, the precedence from highest to lowest is: &, #, |.
Porting instructions: Manually add parentheses around expressions that depend on the PostgreSQL behavior.
Integer division
In PostgreSQL, division of integers results in an integer. For example, the following query returns 1, since the 1 / 2 is truncated to 0:
SELECT 1 + 1 / 2
In CockroachDB, integer division results in a decimal. CockroachDB instead provides the // operator to perform floor division.
Porting instructions: Change / to // in integer division where the result must be an integer.
Shift argument modulo
In PostgreSQL, the shift operators (<<, >>) sometimes modulo their second argument to the bit size of the underlying type. For example, the following query results in a 1 because the int type is 32 bits, and 32 % 32 is 0, so this is the equivalent of 1 << 0:
SELECT 1::int << 32
In CockroachDB, no such modulo is performed.
Porting instructions: Manually add a modulo to the second argument. Also note that CockroachDB's INT type is always 64 bits. For example:
SELECT 1::int << (x % 64)
Locking and FOR UPDATE
New in v19.2: Because CockroachDB only supports SERIALIZABLE isolation, locking is not required to order concurrent transactions relative to each other. The FOR UPDATE locking clause is supported in selection queries for database migration compatibility only. As a no-op clause, FOR UPDATE does not provide stronger, PostgreSQL-compatible locking guarantees for uses unrelated to performance, such as applications that require locks to protect data from concurrent access altogether.
CockroachDB uses a lightweight latch to serialize access to common keys across concurrent transactions. As CockroachDB does not allow serializable anomalies, transactions may experience deadlocks or read/write contention. This is expected during concurrency on the same keys. These can be addressed with either automatic retries or client-side intervention techniques.
Schema namespaces
For compatibility with PostgreSQL, CockroachDB supports a three-level structure for names: databases, schemas, and objects.
However, in CockroachDB versions < v20.2, user-defined schemas are not supported, and the only schema available for stored objects is the preloaded public schema. As a result, CockroachDB effectively supports a two-level storage structure: databases and objects. To provide a multi-level structure for stored objects, we recommend using database namespaces in the same way as schema namespaces are used in PostgreSQL.
For more details, see Name Resolution.
SQL Compatibility
Click the following link to find a full list of CockroachDB supported SQL Features.
Was this helpful?
