ERROR: recursive query "t" column 3 has type character varying(255) in non-recursive term but type character varying overall

Question

I have this table.

CREATE TABLE directory (
  id           INT NOT NULL,
  parent_id    INT,
  label        character varying(255),
  
  CONSTRAINT pk_directory PRIMARY KEY (id),
  CONSTRAINT fk_directory FOREIGN KEY (parent_id) REFERENCES t_directory (id)
);

I'm trying to build a hierarchy so I have done this in jOOQ.

CommonTableExpression<?> cte = name("t").fields(
  "id",
  "name",
  "path"
).as(
  select(
    DIRECTORY.ID,
    DIRECTORY.LABEL,
    DIRECTORY.LABEL)
  .from(DIRECTORY)
  .where(DIRECTORY.PARENT_ID.isNull())
  .unionAll(
  select(
    DIRECTORY.ID,
    DIRECTORY.LABEL,
    field(name("t", "path"), VARCHAR)
      .concat("\\")
      .concat(DIRECTORY.LABEL))
  .from(table(name("t")))
  .join(DIRECTORY)
  .on(field(name("t", "id"), INTEGER)
    .eq(DIRECTORY.PARENT_ID)))
);

System.out.println(
    create().withRecursive(cte)
            .selectFrom(cte)
            .fetch()
);

But I get this error:

ERROR: recursive query "t" column 3 has type character varying(255) in non-recursive term but type character varying overall

If I change the cast from VARCHAR to TEXT it works but PostgresDataType.TEXT is deprecated and shouldn't be used.

Anyone knows the solution?

Thanks

score 1 · Answer 1 · edited Jul 09 '21 at 03:41

You get same error if you convert it to sql and run in postgresql. What you need is cast:

      WITH RECURSIVE t(id, name, path) as (
      
      select
      ID,
      LABEL,
      LABEL
     from DIRECTORY 
      where PARENT_ID is null
    union all 
      select d.ID,
      d.LABEL,
      cast(t.path || '\\' || d.label as varchar(255)) 
  -- ^^^^^^^ you need jOOQ equivalent of cast
             from t
                join directory d on (t.id = d.parent_id)

  ) select * from t;

score 0 · Answer 2 · answered Jun 28 '21 at 07:14

This seems to be an issue caused by a direct application of the WITH RECURSIVE example from the jOOQ manual. An example improvement is on the way.

In PostgreSQL, you'll have to cast both subqueries of the recursive UNION ALL query like this:

DIRECTORY.LABEL.cast(VARCHAR(255))
// ...
field(name("t", "path"), VARCHAR)
      .concat("\\")
      .concat(DIRECTORY.LABEL).cast(VARCHAR(255))

Where the above uses the usual static imports:

import static org.jooq.impl.DSL.*;
import static org.jooq.impl.SQLDataType.*;

Alternatively, you could use TEXT as a column type in your table declaration:

CREATE TABLE directory (
  id           INT NOT NULL,
  parent_id    INT,
  label        TEXT, -- Use TEXT instead
  
  CONSTRAINT pk_directory PRIMARY KEY (id),
  CONSTRAINT fk_directory FOREIGN KEY (parent_id) REFERENCES directory (id)
);

Apparently, PostgreSQL doesn't require casts in that case.

If I change the cast from VARCHAR to TEXT it works but PostgresDataType.TEXT is deprecated and shouldn't be used.

The standard SQL type that corresponds to TEXT in jOOQ is SQLDataType.CLOB.

ERROR: recursive query "t" column 3 has type character varying(255) in non-recursive term but type character varying overall

2 Answers2