Search code examples
databasepostgresqlsqliteblobclob

Comparing CLOB, BLOB values in Oracle, Postgres and SQLite

This involves comparing tables in two different DB schema. The requirement is to traverse a known set of tables and ensure that the table data in both the schema are identical. At the moment we are doing a similar operation on Oracle with a query like the following:

For each table;

SELECT COUNT(*) FROM (SELECT * FROM SCHEMA1.MY_TABLE MINUS (SELECT * FROM SCHEMA2.MY_TABLE));

But above query has a limitation in oracle when it comes to large objects:

ERROR at line 1: ORA-00932: inconsistent datatypes: expected - got BLOB

Apparently, the limitation exists for all set operations in Oracle when it comes to large objects as detailed here. It could be overcome by using DB specific functions like dbms_lob.compare.

As I have limited exposure to Postgres and SQLite I would like to know;

  • Are there similar limitations in using set operators like union, minus, or intersect in Postgres and SQLite when it comes to LOB values?
  • If there are limitations, are there any DB specific functions which should be used for LOB comparison?
Solution

  • In PostgreSQL/SQLite, the text/TEXT and bytea/BLOB data types behave just like smaller values and can be compared normally.