A cast to five bytes and a two-character word

CAST('中文' AS VARCHAR2(5)) asks for five bytes of a value that takes six. Each of the two characters is three bytes long in a UTF-8 database, so the fifth byte falls inside the second character. Oracle, PostgreSQL and Paritas each answer differently, and until this month Paritas had the worst answer of the three.

ExpressionOracle Database 21cPostgreSQL 18.6, nearest formParitas
CAST('中文' AS VARCHAR2(5))中, 3 bytesvarchar(5): 中文, 6 bytes中, 3 bytes
CAST('中文' AS VARCHAR2(2))NULLvarchar(2): 中文, 6 bytesNULL
CAST('中文' AS CHAR(5))中, 3 byteschar(5): 中文 and three blanks, 9 bytes中 and two blanks, 5 bytes

PostgreSQL has no VARCHAR2 and no lengths in bytes. varchar(5 byte) is a syntax error, and varchar(n) counts characters, so a column converted from VARCHAR2(5) to varchar(5) can hold up to 20 bytes of UTF-8 where Oracle's held five.

What our build did

Until 6 November a cast in Paritas cut at the fifth byte wherever that fell, so the value kept the first character and two bytes of the second. The cast itself raised nothing. An INSERT stored the broken text, LENGTH refused to read it, pg_dump wrote it out and the restore lost the row.

Oracle stops before the character the size would cut, and gives NULL when no whole character fits. Since 6 November 2026 both VARCHAR2 and CHAR in Paritas do that. In UTF-8 the byte at the cut says where to stop, so a long value costs no walk from its beginning.

The idea for the VARCHAR2 half came from a fix in the open-source base Paritas is built on. That version answers an empty string where Oracle answers NULL, and it leaves CHAR as it was.

How it was measured

The Oracle column is Oracle Database 21c. The regression test ora_byte_cast holds 294 of its answers: seven strings of one- to four-byte characters, cast to VARCHAR2 and CHAR at each size, by bytes, by the default and by characters, read back as LENGTHB, LENGTH and the value. Paritas runs the same 294 statements. On 6 November 2026, 274 of them matched.

The PostgreSQL column is a stock PostgreSQL 18.6 with a UTF-8 database, run on 10 November 2026.

What is still different

All 20 cells that differ are CHAR casts that stopped short, and they differ in LENGTHB and LENGTH alone. Paritas pads such a value to its size, so CAST('中文' AS CHAR(5)) is the first character and two blanks. Oracle pads only when the value is stored in a CHAR of that size or more, and leaves it short everywhere else. A stopped value and a padded one are therefore a single value to DISTINCT in Paritas.

EUC_JP and LATIN1 follow the same rule in the tests, but we have no Oracle instance in those character sets, so nothing checks them against Oracle's own answers.

Paritas is an Oracle-compatible database built on PostgreSQL, from Tech Style. It starts from an open-source base on PostgreSQL 18.6. We are building tools around it for migration, development and connecting applications. pgrecon, our free assessment tool, is out today, and the development tools and drivers are not available yet. Paritas is still in development and we are aiming to release it next quarter. More at tech-style.co/product-paritas.

Comments

Post a Comment

Popular posts from this blog

Strapi Remote Backup Pro, backing up a Strapi CMS without installing anything in it

Image
Every Strapi backup tool we looked at is a plugin. Installing one means adding a dependency to a live CMS, redeploying it, and trusting code that runs inside the production process. If you are an agency backing up a client's site, you may not have deploy access at all. Strapi Remote Backup Pro works from outside. You point it at the CMS URL and sign in as an admin, and it talks to the same admin API the Strapi admin panel uses. Nothing is installed, so nothing has to be redeployed or removed afterwards, and it cannot destabilise a CMS it is not running inside. It is in development, and this post says how far it has got. What works today The engine and the command line, published on npm as 0.1.0. The commands are login , backup , restore , inspect and verify . npx @tech-style/strapi-remote-backup-pro backup --url https://cms.example.com --email admin@example.com It needs Node.js 22.19 or later. If you would rather not deal with Node, a bootstrap installer stages a pinned, che...
Read more

pgrecon, an Oracle to PostgreSQL assessment that never connects to your database

Image
Most Oracle to PostgreSQL projects are priced before anyone has read the code. Someone counts tables and guesses at the PL/SQL, and the expensive surprises turn up late: package-level state, autonomous transactions, LONG columns, interval partitioning, database links. pgrecon is our free, open-source tool for finding those things first. It is on GitHub under Apache 2.0 and on PyPI as pgrecon . It needs Python 3.11 or later. It never connects to your database That matters, because it decides whether a bank or a hospital will let you run the tool at all. pgrecon writes a plain SQL*Plus script, SELECT only, against the dictionary views. The DBA reads it, runs it with a read-only account and hands back a folder of files. Only files cross the boundary. pgrecon script --source-version 19 sqlplus readonly_user@service @pgrecon_extract.sql SCHEMA_NAME pgrecon load dump_dir --db inventory.db pgrecon report --db inventory.db The extracting account needs SELECT_CATALOG_ROLE or the equivalen...
Read more

JSON Compress, lossless JSON compression whose output is still JSON

Image
Send a thousand records over an API and you send a thousand copies of every key name. A status with four possible values is spelled out a thousand times, and so is a currency that is always "GBP". Minifying takes out the whitespace, which is the small part of the problem. The repetition stays. JSON Compress is our open-source library for the repetition. It writes each key name once, turns arrays of like-shaped objects into columns and rows, and stores repeated values once. On record-shaped data the result is typically 45 to 75% smaller than minified JSON, and it comes back byte for byte. The output is still JSON, so it goes wherever the original went: an HTTP body, a WebSocket frame, a Postgres jsonb column, a queue message. What it does to a document There are four transformations, and each can be turned off: A key catalogue. Every distinct key name is written once and replaced by a short token, with the busiest key getting the shortest one. Columnar tables. An array...
Read more