0

Suppose I register a custom collation my_collation which, for example, orders digit strings as integers. Then I create and populate a table foo with a primary key on the column bar using this collation:

CREATE TABLE foo(
    bar TEXT PRIMARY KEY COLLATE my_collation,
    quux INT
);
INSERT INTO foo VALUES ('1000', 1000), ('1', 1), ('123', 123), ('12', 12);

When I subsequently open this database in an environment that does not have this custom collation registered (e.g. sqlite3 CLI shell) I'm unable to query this table:

sqlite> SELECT * FROM foo;
Parse error: no query solution

Obviously I don't expect to be able to INSERT/DELETE, UPDATE the bar column or even to SELECT with constraints on bar, e.g.

SELECT * FROM foo
WHERE bar = '12';

since all these require comparing bar values via the unavailable collation. But I don't understand why I can't just SELECT the entire table.

Why can't I, and how do I get the data out?

yuri kilochek
  • 12,709
  • 2
  • 32
  • 59
  • 2
    Maybe this will help: https://stackoverflow.com/questions/47095400/exporting-a-sqlite3-table-from-a-db-with-error-no-such-collation-sequence-iun – forpas Feb 18 '23 at 16:03
  • If you registered the custom collation with a loadable extension, simply load the extension in the CLI shell. – Mark Benningfield Feb 18 '23 at 16:15

0 Answers0