Installing the Postgres LTREE Extension

hidden inside of your postgres server is code that provides special sql operators and functions designed to support tree operations. it’s called the ltree extension . i’m guessing this stands for left-tree . in my next post, i’ll write about some of these functions and operators: what they do and how to use them.

but first, where is the ltree extension? how can you install and start using it? read on to find out.

testing whether the ltree extension is installed

depending on where you downloaded postgres from and how you installed it, you may have already installed ltree with postgres. to find out, execute this sql statement:

=> create extension ltree;
create extension

if you see the create extension message like this, then you’re all set! ltree was already installed and you just enabled it. skip to my next post when it's published to find out what it can do and how to use it.

but if you see:

=> create extension ltree;
error:  extension "ltree" already exists

…then your postgres server already had ltree enabled.

fyi : the pg_available_extensions table will show you all the postgres extensions that are available and installed on your server:

select * from pg_available_extensions;

  name   | default_version | installed_version |                     comment
---------+-----------------+-------------------+-------------------------------------------------
 ltree   | 1.1             | 1.1               | data type for hierarchical tree-like structures
 plpgsql | 1.0             | 1.0               | pl/pgsql procedural language
(2 rows)

as you can see, ltree already appears on my server’s list. the value 1.1 for installed_version indicates that i’ve already enabled it, too. this would have been blank before running the create extension ltree command above.

i originally installed a local copy of postgres on my mac using homebrew, and i was happy to find that the homebrew postgres formula does include steps to build and install ltree, after building the rest of the postgres server. but i still needed to enable it using create extension .

using ltree on a shared postgres server

running the create extension ltree command may fail with this error message:

=> create extension ltree;
error:  permission denied to create extension "ltree"
hint:  must be superuser to create this extension.

enabling postgres extensions requires super-user access. if you’re using a shared postgres server and don’t have super-user access, you’ll need to find someone who does. or you may just need to login to postgres using the proper postgres user account.

how to install the ltree extension

running the create extension command may also fail with this error message:

=> create extension ltree;
error:  could not open extension control file "/usr/local/pgsql/share/extension/ltree.control": no such file or directory

this error means the ltree code isn’t even installed on your postgres server. if you’re running on linux and installed postgres using a package manager, you may have to install a second package called postgresql-contrib .

if you installed postgres from source yourself, then you will see this error message because the postgres makefile doesn’t compile and install ltree by default. don’t worry! it turns out the postgres source tree already contains the code for ltree and many other extensions in a subdirectory called contrib .

compile it as follows:

$ cd /path/to/postgres-9.6.5/contrib/ltree
$ make

gcc -wall -wmissing-prototypes -wpointer-arith -wdeclaration-after-statement -wendif-labels -wmissing-format-attribute -wformat-security -fno-strict-aliasing -fwrapv -wno-unused-command-line-argument -o2  -dlower_node -i. -i. -i../../src/include   -c -o ltree_io.o ltree_io.c

etc…

$ sudo make install

/bin/sh ../../config/install-sh -c -d '/usr/local/pgsql/lib'
/bin/sh ../../config/install-sh -c -d '/usr/local/pgsql/share/extension'
/bin/sh ../../config/install-sh -c -d '/usr/local/pgsql/share/extension'
/usr/bin/install -c -m 755  ltree.so '/usr/local/pgsql/lib/ltree.so'
/usr/bin/install -c -m 644 ./ltree.control '/usr/local/pgsql/share/extension/'
/usr/bin/install -c -m 644 ./ltree--1.1.sql ./ltree--1.0--1.1.sql ./ltree--unpackaged--1.0.sql  ‘/usr/local/pgsql/share/extension/'

you can see above that the install step copied the ltree.so library into my postgres server’s lib directory, /usr/local/pgsql/lib , and ran a couple other commands, as well. now i can run the create extension ltree command as shown above. i don’t even need to restart postgres; it will find and load ltree.so automatically.

now that you have ltree installed and enabled, you can read my next post to learn how to use it.