2

I'm currently transitioning a SQL backend from SQLite to CockroachDB, the latter being a semi-PGSQL-compatible database.

I have multiple tables which at their core are adjacency lists, i.e.:

CREATE TABLE IF NOT EXISTS foobar (
  id INTEGER NOT NULL PRIMARY KEY,
  name TEXT NOT NULL,
  ...
  parent_id INTEGER
);

With SQLite, I used this query to retrieve the elements in the full path of a given row:

WITH RECURSIVE path(level, name, parentid) AS (
    SELECT 0, name, parentid
    FROM {TableName}
    WHERE id = @id
    UNION ALL
    SELECT path.level + 1,
        {TableName}.name,
        {TableName}.parentid
    FROM {TableName}
    JOIN path 
        ON {TableName}.id = path.parentid
)
SELECT name
FROM path
ORDER BY level DESC;

However, CockroachDB doesn't support any kind of WITH statement.

So the question is: In this database, what is the most efficient way of porting the above statement.

The full path for each row (usually about 2 to 10 million per table) only needs to be calculated once, and the table itself is not expected to change often if ever. So I'm planning to save the results from this query permanently using CREATE TABLE AS.

I am using the CockroachDB 2.0beta.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.