I am trying to create a trigger that uses ltree column.



SELECT * FROM pg_catalog.pg_extension 

gives the following output:

extname     |extowner|extnamespace|extrelocatable|extversion|extconfig|extcondition|
plpgsql     |      10|          11|false         |1.0       |NULL     |NULL        |
ltree       |      10|      361964|true          |1.1       |NULL     |NULL        |
postgres_fdw|      10|      361970|true          |1.0       |NULL     |NULL        |

I cannot drop the extension because I get an error that there are dependencies. I was also able to create a table that has a column with data type ltree without an error.

When trying to create a trigger, I get the following error:

SQL Error [42704]: ERROR: type "ltree" does not exist
  Position: 252

This is a snippet of the trigger and the problem seems to be happeing when I declare a variable of type ltree:

CREATE OR REPLACE FUNCTION asset.update_asset_path()


AS $$
        db_path_ltree ltree;
        single_asset INT;
        s3path TEXT = '';
        ltree_array TEXT[];
        v_asset_name TEXT;
        i_last_element INT;

What is confusing, is this is only happening in one environment for dev. Every other postgres server does not have this problem and is using the same code.

Any ideas why this is happening?

  • I dont know why but it happened to me that create table myschema.a ( path LTREE ) threw same error as you, but using SEARCH_PATH and create table a ( path LTREE ) did not
    – Whimusical
    Mar 2, 2020 at 11:47

1 Answer 1


The extension ltree is installed in the schema with the object ID 361964. If you want to know the name of that schema, run

SELECT 361964::regnamespace;

It could be that that schema is not on the search_path when you execute the function. That would explain the error.

If you install an extension in a schema other than public, it is usually best to reference its objects with schema qualification, for example

    db_path_ltree extschema.ltree;
  • Thanks for clearing that up. We use flyway to clean and migrate databases, which automatically created extension in flyway schema. Whereas we typically create extensions in public
    – kravb
    Dec 5, 2019 at 17:33
  • 1
    There is the SCHEMA clause in CREATE EXTENSION that gives you control. Dec 6, 2019 at 6:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.