Common data types

Common data types

Different type names can share the same storage structure and processing logic. PolarDB groups these under a Display type — the canonical name used internally. Understanding this grouping helps you recognize which Oracle type names map to which PostgreSQL-native types.

Oracle-to-PolarDB type mapping

Oracle type names that appear in the Type name column above are accepted directly. The following DDL example shows how common Oracle column types map when used in PolarDB.

-- Oracle DDL (source)
CREATE TABLE sales (
  id            NUMBER(10),
  amount        BINARY_DOUBLE,
  payload       BLOB,
  description   CLOB,
  created_at    DATE,
  label         NVARCHAR2(100),
  raw_data      RAW(200),
  doc           XMLTYPE
);

-- Equivalent PolarDB DDL
-- Oracle aliases resolve to the display types shown in the table above
CREATE TABLE sales (
  id            numeric(10),             -- NUMBER    -> numeric
  amount        double precision,        -- BINARY_DOUBLE -> double precision
  payload       bytea,                   -- BLOB      -> bytea
  description   text,                    -- CLOB      -> text
  created_at    date,                    -- DATE      -> date (includes time component)
  label         character varying(100),  -- NVARCHAR2 -> character varying
  raw_data      bytea,                   -- RAW       -> bytea
  doc           xmltype                  -- XMLTYPE   -> xmltype (Oracle-compatible)
);

Selection guide

json vs jsonb

Use jsonb for most workloads. It stores data in an optimized binary format, supports indexing, and delivers far better query performance than json. The json type stores data as plain text, which allows for fast insertions, making it suitable for audit logging or cases where exact text fidelity matters.

Numeric precision

When using numeric or decimal, always specify precision and scale to control storage size and accuracy. Example: numeric(10, 2).

String types

character(n) (and its alias char(n)) pads values with spaces to length n, which can cause unexpected comparison behavior. Prefer varchar or text unless you have a specific fixed-length requirement.

Integer types

Choose the smallest type that fits your data range:

• smallint — −32,768 to 32,767; use for compact lookup tables or flags

• integer — −2,147,483,648 to 2,147,483,647; the default choice for most integer columns

• bigint — up to ~9.2 × 10¹⁸; use for counters, user IDs, or any column that may exceed two billion rows

For auto-incrementing primary keys, use serial (integer-sized) or bigserial (bigint-sized).

FAQ

What is the difference between varchar(n) and text, and which should I use?

There is no performance difference between varchar(n) and text in PolarDB for PostgreSQL (Compatible with Oracle). The only difference is that varchar(n) rejects values longer than n characters at insert time, while text has no length limit. If your schema does not need a hard length constraint, use text — it simplifies schema evolution and avoids silent truncation surprises.

How do I choose the right integer type?

Start with integer. It covers the vast majority of use cases (up to about ±2.1 billion). Switch to bigint when you expect values to exceed that range, such as global counters or row IDs in high-volume tables. Use smallint only when storage compactness is critical and the range −32,768 to 32,767 is sufficient.

For auto-incrementing IDs, use serial (integer range) or bigserial (bigint range).