Known differences between EDB Postgres Advanced Server and PostgreSQL v18

EDB Postgres Advanced Server (EPAS) is built on PostgreSQL and adds Oracle-compatibility features, additional built-in packages, and enterprise tooling. Most of these additions are a superset of PostgreSQL behavior and don't affect existing applications. However, a small number of differences can change how an application behaves when it's moved between PostgreSQL and EDB Postgres Advanced Server.

This topic lists the known differences that can affect application compatibility. It doesn't cover the full set of EDB Postgres Advanced Server enhancements — see Working with Oracle data and Enhanced compatibility features for the broader feature set.

Summary of differences

Additional reserved and restricted keywords

EDB Postgres Advanced Server's parser recognizes a number of keywords that core PostgreSQL doesn't, mainly to support Oracle-compatible syntax (for example, hierarchical queries, packages, and Oracle-style storage clauses). Most of these are unreserved and only affect identifiers in edge cases, but a smaller set is fully reserved or partially reserved.

For example, this statement works in PostgreSQL but fails in EDB Postgres Advanced Server, because FINAL is an added keyword:

SELECT 1 final;

Add AS, or quote the identifier, to work around it:

SELECT 1 AS final;
SELECT 1 "final";

You can query the live set of keywords and their category on any given server with:

SELECT * FROM pg_get_keywords() WHERE catcode <> 'U';

See EDB Postgres Advanced Server keywords for how to interpret the category codes, and Identifiers and key words for the general rules PostgreSQL and EDB Postgres Advanced Server both follow.

Back to summary table

Hash character in identifiers and operators

PostgreSQL treats # as a non-word character, so it's parsed as part of an operator. EDB Postgres Advanced Server treats # as a word character, so it can be parsed as part of an identifier instead. This mainly affects column names or operators that start or end with #.

For example, given:

CREATE TABLE t (a int, b int);
SELECT a#>b FROM t;

PostgreSQL parses a#>b as the operator #> applied to columns a and b. EDB Postgres Advanced Server parses it as the column a# followed by the operator >b, which fails unless a column literally named a# exists. Add whitespace around #-based operators to avoid the ambiguity.

Back to summary table

Double asterisk operator precedence

PostgreSQL has no ** operator. EDB Postgres Advanced Server adds ** for exponentiation and gives it the same precedence as ^. If an application relies on ** being parsed as two separate * (multiplication) tokens, as it would in PostgreSQL, the result can differ in EDB Postgres Advanced Server.

Back to summary table

SQL%FOUND, SQL%NOTFOUND, and SQL%ROWCOUNT are restricted

These tokens are meaningful only inside procedural code written in EDB-SPL, where their meaning matches Oracle's. They can't be used in ordinary SQL statements, even if whitespace is added before or after the % symbol.

Back to summary table

RETURN is accepted alongside RETURNS in CREATE FUNCTION

For Oracle compatibility, EDB Postgres Advanced Server's parser accepts RETURN as well as the standard RETURNS clause in CREATE FUNCTION:

-- Standard PostgreSQL syntax
CREATE FUNCTION add(a integer, b integer) RETURNS integer LANGUAGE SQL
RETURN a + b;

-- Oracle-compatible syntax also accepted by EDB Postgres Advanced Server
CREATE FUNCTION add(a integer, b integer) RETURN integer LANGUAGE SQL
RETURN a + b;

Because the parser can't always tell whether RETURN introduces a return type or a returned value, some statements that work in PostgreSQL fail in EDB Postgres Advanced Server:

-- Fails in EDB Postgres Advanced Server: RETURN is read as a return type
CREATE FUNCTION increment(a inout integer) RETURN a + 1;

The workaround is to specify the return type explicitly:

CREATE FUNCTION increment(a inout integer) RETURNS integer LANGUAGE SQL
RETURN a + 1;

Back to summary table

Query result ordering without ORDER BY can differ

Neither PostgreSQL nor EDB Postgres Advanced Server guarantees row order for a query without an ORDER BY clause, and this isn't guaranteed to be stable even between two PostgreSQL instances. EDB Postgres Advanced Server's additional query planner options can make it more likely that the row order differs from PostgreSQL's for the same query. Applications that depend on result order should always specify ORDER BY.

Back to summary table

Oracle-compatibility behavior depends on configuration

Several of the differences in this table — particularly Oracle-style data type conversions, date formatting, and NULL-string concatenation behavior — are tied to how the cluster is configured, not fixed behavior:

• The initdb --redwood-like / --no-redwood-compat initialization options control whether Oracle-compatible objects and behavior are included at all.
• Configuration parameters such as edb_redwood_date, edb_redwood_greatest_least, edb_redwood_strings, and db_dialect provide finer-grained control once the cluster is initialized.

See Choosing the configuration mode and Configuration parameters compatible with Oracle databases for details.

Back to summary table

Default network port is 5444, not 5432

EDB Postgres Advanced Server extends the PostgreSQL wire protocol to support some of its additional features, so it defaults to port 5444 instead of PostgreSQL's 5432. Tools, connection strings, and third-party poolers or connectors that assume the standard protocol or the 5432 default might not work unmodified against EDB Postgres Advanced Server.

For conformity across a mixed deployment, EDB Postgres Advanced Server can be set to listen on 5432 with the port directive in postgresql.conf, and client tools can be pointed at either port with PGPORT or a connection string.

Back to summary table

Default OS user, home, and data directory differ

To keep the two products separate on the same host, EDB Postgres Advanced Server packages use their own OS user and default paths instead of reusing PostgreSQL's:

Scripts, automation, or documentation that hardcode the PostgreSQL user or paths need to be updated for EDB Postgres Advanced Server.

Back to summary table

Default service name and socket directory differ

EDB Postgres Advanced Server installs its own systemd service, separate from PostgreSQL's, and defaults to a different Unix socket directory:

For conformity across a mixed deployment, tools can be configured to look for the socket in a specific location by setting host= in the connection string (or PGHOST) to the socket directory path, for example psql -h /tmp/.s.PGSQL.5432.

Back to summary table