Names, Quoting, and Case Sensitivity

Oracle stores unquoted identifiers in uppercase. PostgreSQL stores unquoted identifiers in lowercase. This single behavioral difference — a default case folding that runs in opposite directions — is the source of a category of migration failures that appear only after the schema has been converted, the data has been loaded, and the application has been pointed at the new database.

The failures do not look like case failures. They look like "table not found," "column does not exist," or query results that return zero rows when the data is demonstrably present. The root cause is that the application was written for Oracle's case-folding rules and the migrated schema was created under PostgreSQL's case-folding rules, and the two are not the same.

This chapter covers what Oracle and PostgreSQL actually do with names, why migrated schemas tend to produce quoted identifiers that become a long-term maintenance problem, and how a product-grade migration system handles name normalization from the start.

5.1 Oracle Identifier Semantics

In Oracle, an unquoted identifier is stored and matched in uppercase. When a developer writes:

CREATE TABLE account (
  id        NUMBER,
  user_name VARCHAR2(100)
);

Oracle stores the table name as ACCOUNT and the column names as ID and USER_NAME. Queries against this table work regardless of how the developer writes the name:

SELECT user_name FROM account WHERE id = 1;
SELECT USER_NAME FROM ACCOUNT WHERE ID = 1;
SELECT User_Name FROM Account WHERE Id = 1;

All three are identical to Oracle. The underlying stored name is always uppercase.

Quoted identifiers in Oracle preserve case and become case-sensitive:

CREATE TABLE "Account" (
  "Id"        NUMBER,
  "user_name" VARCHAR2(100)
);

Now "Account" and ACCOUNT are different tables. Queries must use the exact quoted form to reach the quoted-identifier table. Unquoted references like FROM Account or FROM account resolve to ACCOUNT, not to "Account".

In practice, very few Oracle schemas use quoted identifiers intentionally. The overwhelming majority of Oracle production schemas use unquoted identifiers, which Oracle silently uppercases. This means the schema appears uppercase in the data dictionary regardless of how the DDL was written:

-- Oracle data dictionary
SELECT table_name FROM user_tables;
-- Returns: ACCOUNT, ORDER_HEADER, ORDER_LINE, CUSTOMER, ...

5.2 PostgreSQL Identifier Semantics

PostgreSQL folds unquoted identifiers to lowercase. The same CREATE TABLE statement:

CREATE TABLE account (
  id        INTEGER,
  user_name TEXT
);

Stores the table name as account (lowercase) and column names as id, user_name. Queries work in any case:

SELECT user_name FROM account WHERE id = 1;
SELECT USER_NAME FROM ACCOUNT WHERE ID = 1;

Both resolve to account.user_name. So far this seems equivalent to Oracle — just the opposite direction.

The problem emerges when you combine a PostgreSQL schema with an Oracle application. An Oracle application typically generates or contains SQL that references Oracle's stored uppercase names, either explicitly or implicitly. When that application targets PostgreSQL, the uppercase references no longer match the lowercase-stored names — and PostgreSQL, unlike Oracle, is case-sensitive once identifiers are quoted.

The Quoted Identifier Escape Hatch

A naive migration tool that wants to preserve Oracle's uppercase convention will create the PostgreSQL schema using quoted identifiers:

CREATE TABLE "ACCOUNT" (
  "ID"        INTEGER,
  "USER_NAME" TEXT
);

This works, in the sense that queries using the quoted form succeed:

SELECT "USER_NAME" FROM "ACCOUNT" WHERE "ID" = 1;

But it fails in several ways that accumulate over time:

1. Every query must quote every identifier. Unquoted FROM account no longer resolves to "ACCOUNT". The application must use FROM "ACCOUNT" everywhere, which means every existing ORM mapping, every dynamically constructed query, every stored procedure must be rewritten to include quotes.

2. psql and tooling become hostile. Most PostgreSQL tooling, including \d, \dt, tab completion in psql, and most schema migration frameworks, assume lowercase unquoted names. Uppercase quoted names work but require constant quoting that breaks assumptions throughout the toolchain.

3. New PostgreSQL code written by engineers who joined after the migration will use lowercase conventions and will be incompatible with the quoted-uppercase legacy schema.

The quoted-uppercase approach is not a migration solution. It is a debt instrument that defers the case problem while making it compound.

5.3 The Correct Approach: Lowercase Normalization at Migration Time

The correct approach is to normalize all identifiers to lowercase at migration time and simultaneously update the application to use lowercase names. This is not optional. It is the only approach that produces a PostgreSQL schema that can be maintained as a PostgreSQL schema.

Identifier normalization has three components:

DDL Normalization

The schema DDL is created using lowercase, unquoted identifiers:

-- Correct PostgreSQL DDL after normalization
CREATE TABLE account (
  id        INTEGER NOT NULL,
  user_name TEXT,
  created_at TIMESTAMP WITH TIME ZONE
);

Product note: Pulsaride Transform applies lowercase normalization during schema extraction. The StepConfig for DDL generation includes a namingPolicy: lowercase setting that folds all Oracle uppercase names before writing the target DDL. This is the default and should not be overridden to preserve-oracle-case without understanding the consequences described above.

Application Query Normalization

Application SQL that references Oracle names must be updated to reference lowercase PostgreSQL names. This is the step that migration tooling cannot do automatically — it requires understanding the application's SQL surface.

Common Oracle patterns that require normalization:

// Oracle JDBC — works because Oracle is case-insensitive
String sql = "SELECT USER_NAME, CREATED_DATE FROM ACCOUNT WHERE ACCOUNT_ID = ?";

// Must become (PostgreSQL case-insensitive for unquoted lowercase)
String sql = "SELECT user_name, created_date FROM account WHERE account_id = ?";

// Or, if the application uses uppercase constants, must quote
// (the quoted-identifier escape hatch — not recommended)
String sql = "SELECT \"USER_NAME\", \"CREATED_DATE\" FROM \"ACCOUNT\" WHERE \"ACCOUNT_ID\" = ?";

ORM Mapping Normalization

Hibernate and JPA mappings that were generated from Oracle's uppercase schema need updating:

// Oracle-era Hibernate mapping
@Table(name = "ACCOUNT")
@Column(name = "USER_NAME")

// PostgreSQL-correct mapping (no quoting needed if lowercase)
@Table(name = "account")
@Column(name = "user_name")

If the ORM uses @Column(name = "USER_NAME") without explicit quoting configuration, Hibernate will send the name to PostgreSQL unquoted, PostgreSQL will fold it to lowercase, and it will match user_name. This works. But it creates a schema where the Java code says uppercase and the database stores lowercase, which confuses future engineers. Explicit lowercase naming is cleaner.

5.4 Reserved Words and Collision Risk

Some Oracle object names that are legal Oracle identifiers become PostgreSQL reserved words after lowercasing.

Oracle allows identifiers like:

CREATE TABLE "ORDER" (...);       -- Oracle: fine as unquoted ORDER
CREATE TABLE order_header (...);  -- Oracle: unquoted ORDER_HEADER, fine

ORDER is a reserved word in PostgreSQL. A table named order (lowercase, unquoted) will fail:

-- PostgreSQL: reserved word collision
CREATE TABLE order (id INTEGER);
-- ERROR: syntax error at or near "order"

The options are:

1. Rename the table during migration (order → sales_order, payment_order, or the business-appropriate name)
2. Use a quoted identifier "order" and accept the quoting burden
3. Use an alternative name that avoids the collision

Product note: Pulsaride Transform includes a reserved-word scanner that runs during assessment. Before DDL generation, it compares all normalized (lowercase) Oracle object names against PostgreSQL's reserved word list and emits a RESERVED_WORD_COLLISION finding for each conflict. The finding includes the object type, the original Oracle name, and a suggested rename. Reserved word collisions must be resolved before DDL generation proceeds — the product does not auto-rename because the correct name is a business decision.

Common Collision Candidates

These Oracle table and column names commonly collide with PostgreSQL reserved or restricted words:

Oracle name	PostgreSQL status	Recommended action
ORDER	Reserved	Rename to domain-specific name
USER	Reserved	Rename (app_user, account_user)
COMMENT	Reserved	Rename (note, remarks)
CONSTRAINT	Reserved	Rename
TIMESTAMP	Type name	Rename column
VALUE	Reserved in some contexts	Rename
ROLE	Mostly safe but watch context	Audit queries

5.5 Schema Names and Search Path

Oracle uses schemas as namespaces that correspond to database users. A table owned by user MYAPP is referenced as MYAPP.ACCOUNT from other users. PostgreSQL also supports schema namespaces but the default behavior differs.

In PostgreSQL, the search_path determines which schemas are searched when an unqualified name is used. The default search_path is "$user", public. If a user named myapp connects, PostgreSQL first searches a schema named myapp, then the public schema.

Oracle applications that use unqualified names (just ACCOUNT, not MYAPP.ACCOUNT) work because all objects are in the connecting user's schema. In PostgreSQL, this maps to the public schema (or a dedicated application schema) and works as long as the search_path is set correctly.

Oracle applications that use schema-qualified names (MYAPP.ACCOUNT) require one of:

1. Create a PostgreSQL schema named myapp and place objects there, updating the application to use the lowercase qualified name myapp.account
2. Create synonym-like search_path aliases
3. Create PostgreSQL views in public that proxy to the actual schema

Product note: Pulsaride Transform maps Oracle schema owners to PostgreSQL schemas during assessment. The schemaMapping configuration section controls how Oracle user names are mapped to PostgreSQL schema names:

schemaMapping:
  MYAPP: myapp          # Oracle MYAPP → PostgreSQL myapp
  REFERENCE: ref        # Oracle REFERENCE → PostgreSQL ref
  defaultSchema: public # unqualified names land in public

The full-load and CDC steps use this mapping when writing to the target. Application teams must update qualified references to match the PostgreSQL schema names.

5.6 Identifier Length Limits

Oracle allows identifiers up to 128 bytes (Oracle 12.2+) or 30 bytes (earlier versions). PostgreSQL allows up to 63 bytes. Identifiers that are legal in Oracle 12.2+ and exceed 63 bytes will be truncated by PostgreSQL in ways that may create collisions.

-- 72-character Oracle column name (legal in 12.2+)
ALTER TABLE order_header ADD customer_reference_external_system_id_from_erp VARCHAR2(200);

-- PostgreSQL truncates to 63 characters:
-- customer_reference_external_system_id_from_er
-- (PostgreSQL emits a NOTICE about truncation but does not error)

If another column is named customer_reference_external_system_id_from_er_v2, truncation creates a collision that PostgreSQL treats as a duplicate column name error.

Product note: The assessment scanner checks all identifier lengths against a 63-byte threshold and emits IDENTIFIER_TOO_LONG findings. Like reserved word collisions, these require a rename decision before DDL generation. The product does not auto-truncate because truncation-by-algorithm can produce meaningless or colliding names.

5.7 Case Sensitivity in Application Queries: A Diagnostic Approach

After migration, case-related failures typically appear as one of three symptoms:

Symptom 1: "relation does not exist"

ERROR: relation "ACCOUNT" does not exist
LINE 1: SELECT * FROM "ACCOUNT"

The application is sending a quoted uppercase name that does not match the lowercase PostgreSQL table. The fix is to remove the quoting from the application or rename the table (not recommended — rename the reference).

Symptom 2: "column does not exist"

ERROR: column "USER_NAME" does not exist

Same cause at the column level. A quoted uppercase column reference that does not match the lowercase PostgreSQL column.

Symptom 3: Zero rows returned, no error

This is the most dangerous symptom. It occurs when the application is using a case-insensitive comparison that worked in Oracle but returns nothing in PostgreSQL because of case mismatch in stored string values (not identifiers). For example, if Oracle application code stored values in uppercase ('ACTIVE', 'PENDING') and the PostgreSQL migration preserved those values, but new PostgreSQL application code stores them in lowercase ('active', 'pending'), equality comparisons will silently return nothing.

This is a data-value case problem, not an identifier case problem. It requires a data normalization pre-step, not a schema rename.

5.8 Example: The ORDER_HEADER Table and Case-Sensitive ORM

A financial application owned by Oracle schema FINAPP has a table ORDER_HEADER with columns ORDER_ID, ORDER_DATE, STATUS_CODE, and CUSTOMER_REFERENCE.

The Hibernate mapping was generated from Oracle's JDBC metadata, which returns uppercase names:

@Entity
@Table(name = "ORDER_HEADER", schema = "FINAPP")
public class OrderHeader {
    @Id
    @Column(name = "ORDER_ID")
    private Long orderId;

    @Column(name = "ORDER_DATE")
    private LocalDate orderDate;

    @Column(name = "STATUS_CODE")
    private String statusCode;

    @Column(name = "CUSTOMER_REFERENCE")
    private String customerReference;
}

After migration to PostgreSQL with lowercase normalization:

• Table: finapp.order_header
• Columns: order_id, order_date, status_code, customer_reference

The Hibernate mapping needs updating:

@Entity
@Table(name = "order_header", schema = "finapp")
public class OrderHeader {
    @Id
    @Column(name = "order_id")
    private Long orderId;

    @Column(name = "order_date")
    private LocalDate orderDate;

    @Column(name = "status_code")
    private String statusCode;

    @Column(name = "customer_reference")
    private String customerReference;
}

With Hibernate's default PhysicalNamingStrategyStandardImpl, unquoted names in @Column and @Table are sent to PostgreSQL unquoted. PostgreSQL folds them to lowercase and matches. So even if the developer writes @Column(name = "ORDER_DATE"), the query sent to PostgreSQL will be order_date (unquoted by Hibernate) and will match the lowercase column. This is the "accidentally works" zone that conceals the underlying mismatch.

The issue surfaces when Hibernate's quoting is enabled (hibernate.globally_quoted_identifiers=true), which causes Hibernate to send "ORDER_DATE" quoted — and PostgreSQL then looks for an exact-match quoted identifier ORDER_DATE which does not exist.

Product note: Pulsaride Transform generates an application migration report alongside the schema DDL. The report lists every Oracle object referenced in the source schema (by cross-referencing DBA_DEPENDENCIES and DBA_PROCEDURES) and maps Oracle names to their normalized PostgreSQL equivalents. This report is the input to the application team's name-update task. The schema migration is blocked until application teams sign off on the mapping — the product enforces this through a namingPolicy.confirmed: true gate in the pipeline configuration.