Installing the Postgres LTREE Extension
Installing the Postgres LTREE Extension
Learn how to get the Postgres LTREE extension installed and enabled so that you can access special SQL operators and functions designed to support tree operations.
Join the DZone community and get the full member experience.Join For Free
Built by the engineers behind Netezza and the technology behind Amazon Redshift, AnzoGraph™ is a native, Massively Parallel Processing (MPP) distributed Graph OLAP (GOLAP) database that executes queries more than 100x faster than other vendors.
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.
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
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
Using LTREE on a Shared Postgres Server
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
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
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
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
/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
Now that you have LTREE installed and enabled, you can read my next post to learn how to use it.
Published at DZone with permission of Pat Shaughnessy , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.