Questions tagged [postgresql-extensions]

Additional modules supplied by the Postgres project but not installed by default.

0
votes
1answer
25 views

pg_stat_statements empty with administrator user and unusable for other users

I have an issue with pg_stat_statements, the extension is installed and enabled on the database, was working for a long time, however it now seems that it has crashed. portaldb=> SELECT * FROM ...
0
votes
0answers
82 views

How to install PLV8 in PostgreSQL 11 in Ubuntu 16?

I am trying to install PLV8 extension (JavaScript Procedural Language for PostgreSQL) in my PostgreSQL 11 Ubuntu Server 16 and having a hard time. The PostgreSQL 11 was installed using PostgreSQL's ...
17
votes
1answer
18k views

How to install the additional module pg_trgm

I just want to know how to install the module pg_tgrm as used in the trigram indexing scheme that allows you to do un-anchored search patterns on an index. WHERE foo LIKE '%bar%';
0
votes
1answer
370 views

`make install` on a module, I get `pgxs.mk:114: recipe for target 'install' failed`

I'm trying to install a PostgreSQL module (pg-bignum). When I try make install, I get the following at the bottom of the log /usr/bin/install: will not overwrite just-created '/usr/share/postgresql/9....
3
votes
2answers
2k views

Installing Temporal Tables extension (contrib extension) on windows

I am trying to install this Temporal Tables extension into my localhost installation of PostgreSQL. (pg9.4, windows 7) I have figured out I needed to put the files into the 9.4/share/contrib folder (...
1
vote
1answer
110 views

Installing Postgresql parray_gin extension on mac, include not found

I am trying to install parray_gin in PostgreSQL 10 on macOS, but I can't get it to build. I'm running pgxn install parray_gin. This first gave me a missing stdio.h error, so I installed the xcode ...
5
votes
1answer
14k views

How do I install/enable the uuid-ossp extension on Postgres 9.3?

I’m using Postgres 9.3 on Ubuntu Linux 14.04. How do I install/enable the uuid-ossp extension? I first verified the contrib package was installed … root@prodbox:/home/rails/myproject# apt-get ...
1
vote
1answer
179 views

Postgres: ERROR: operator does not exist: point <@> point

Using PostgreSQL 11 on Windows. Both cube and earthdistance installed and verified by pg_available_extensions. Restarted PostgreSQL. [42883] ERROR: operator does not exist: point <@> point. ...
10
votes
2answers
7k views

Is it recommended to install extensions into pg_catalog schema?

Since objects in pg_catalog schema are implicitly in the search_path (docs), would it be recommended to install extensions in that schema?
0
votes
1answer
193 views

Postgresql install extension

I installed Posgresql version 10 (stable) through the source package (compiling manually) in the directory opt/pgv10/ .. on my OS LInux Debian Version 8. I would like to use the HIPOPG extension 1, 2 ...
1
vote
1answer
612 views

Use PGXN to install pg_repack on postgres 9.6 on top of Ubuntu 14.04

So I have Ubuntu 14.04 with postgres 9.6 and I wanted to install pg_repack so I did sudo apt-get install pgxnclient` sudo pgxn install pg_repack but I got an error (sorry I forgot the error) and I ...
0
votes
1answer
189 views

Installing extension as non superuser on Amazon AWS RDS

I would like to allow non super users to install a specific postgres extension. Accordingly to the documentation at https://www.postgresql.org/docs/current/extend-extensions.html that is possible if I ...
0
votes
1answer
176 views

PGAUDIT on RHEL 7.4

I have download PostGreSQL 9.6 and pgaudit 9.6 from my RHEL repository. I have installed the rpm. How to I verify it is installed correctly? I have not been able to find good directions. It seems ...
-2
votes
2answers
947 views

How do I install a PostgreSQL extension in Windows? [closed]

In Linux (with Ubuntu or Debian) if I want to install gen_salt(), I first install the -contrib package. sudo apt install postgresql-contrib Then I install the extension in the database, psql -d ...
3
votes
2answers
58 views

PostgreSQL GiST compress skeleton when no compression required

I'm trying to implement my GiST index, where storage type is the same as column type (bytea). They are even going to have same length because these are bitarrays and unions are just disjunctions of ...
2
votes
2answers
80 views

PostgreSQL GiST for compressed entry type

I am using PostgreSQL 9.5 and I'm trying to understand how to implement a GiST index where I have a representation that is a lossily compressed version of the indexed type. For instance, say I have ...
1
vote
1answer
509 views

How to initialize a PostGIS database in PostgreSQL single-user mode?

I'm using PostgreSQL 9.2.15 with PostGIS 2.0.7 through the standard packages available on CentOS 7 with EPEL. I have a server script to automatically setup a PostGIS database, by first executing ...
0
votes
1answer
84 views

PostgreSQL extension installation, best practices

I have a SQL only postgres extension I want to make public and share on GitHub, but I want to make sure the installation process is clear to users. I've just been copying them to the correct ...
4
votes
1answer
446 views

Custom location for postgis extension .control file [closed]

I am trying to install the postgis extension on my database, which resides on a web hosting service. The problem is that the control files seem to have to be found in /package/host/localhost/postgresq-...
5
votes
1answer
3k views

PostgreSQL two different ways to generate a UUID: gen_random_uuid vs uuid_generate_v4?

What is the difference between the following two functions gen_random_uuid() provided by pgcrypto extension uuid_generate_v4() provided by uuid-ossp extension Are they both the same behind the ...
-1
votes
1answer
118 views

Indexing the condition lower(f) LIKE '%foo%'

How do I index the following statement with an un-anchored search pattern SELECT somefield FROM sometable WHERE lower(somefield2) like '%foo%'; Some rows have more than 2k bytes.
5
votes
1answer
910 views

How exactly does trigram word-similarity work?

The docs on the word_similarity function say: Returns a number that indicates how similar the first string to the most similar word of the second string. The function searches in the second string ...
0
votes
1answer
3k views

Where can I find a list of the available extensions for PostgreSQL?

Just wondering if there is an official list for extensions that are available for PostgreSQL?
4
votes
2answers
148 views

PostgreSQL Citus Extension Temp Table

Is it possible with citus extension in PostgreSQL to create temp table that is copied to each worker node (like reference table)? When I run SQL like this: DROP TABLE IF EXISTS mypoint; CREATE TEMP ...
0
votes
1answer
4k views

Do I need to execute “create extension pgcrypto” everytime?

I have used crypt() in one of my functions to hash user password. But what I observed the next day I started my pc that the same thing failed as cryptographic functions became unavailable upon restart....
2
votes
1answer
1k views

Installing PostGIS in a different schema causes topology extension to not find geometry type

I have a sql dump of the schema of an existing database that is working correctly. When I try to load this into a new, empty database psql new_db < dump.sql, I get the following error: ERROR: ...
3
votes
1answer
2k views

Couldn't open extension control file plpython3u.control : No such file or directory

I want to have python3 in my postgresql database for writing stored procedures. Being in the psql client, when I enter the command create extension plpython3u I get the error: couldn't open ...
4
votes
2answers
1k views

list of PostgreSQL trusted languages?

I am trying to find a list of PostgreSQL trusted procedural languages (PL) as I often need to use a PL in a non-superuser environment. But I couldn't find a definitive answer. For example, the ...
3
votes
2answers
673 views

How can I load 'utf8' into plperl by default?

I am attempting to solve a problem explained in another question here. On Debian 7.4 with Postgres 9.1.12, I am able to use the Perl qr operator just fine. On Solaris 5.11 with Postgres 9.2.4, ...
5
votes
1answer
4k views

PostgreSQL 8.4: How to tell if a procedural language is installed or not?

I have a setup program that requires plpgsql to install stored procedures in a PostgreSQL 8.4 database. I need to make sure the language is installed or the app will fail. I don't want to drop the ...
1
vote
1answer
6k views

How to install pgcrypto extension in postgres 10 on CentOS 7

I'm in the process of creating a postgres database for production in CentOS 7. So I already installed (yum install postgresql10-server postgresql10 after adding the repos of course) and configured ...
0
votes
1answer
216 views

When running `make installcheck` I get can not connect to database

On Ubuntu when I run make installcheck I'm getting /usr/lib/postgresql/9.5/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/usr/lib/postgresql/9.5/bin' --inputdir=...
1
vote
2answers
276 views

Hash function and custom C type for PostgreSQL

I am creating a custom type for PostgreSQL. The type is as follow: typedef struct { unsigned long prefix; unsigned long long id; } CustomType; I have build all the in and out functions. ...
3
votes
1answer
47 views

Is it possible to build PostGIS extensions without building the entire project?

PostGIS has an extension called postgis_tiger_geocoder, I have a few questions about it (all pretty tied together), Is it possible to build that without building the rest of PostGIS? If so, can I use ...
-1
votes
1answer
75 views

Postgresql extension pageinspect - mean bt_metap

I am using Postgresql extension pageinspect. Could someone tell me the meaning of these columns: magic, version, root, level, fastroot, fastlevel of the bt_metap function. This information is not ...
0
votes
3answers
402 views

Postgressql text search: pg_trgm index with order by going very slowly

I have a really slow query(~30 seconds) on a large text search DB that I could really use some help with. I'm searching for ordered text, in a long file path string. So the path could be: folder1/cat ...
10
votes
1answer
8k views

Postgresql enabling extensions without super-user

I have a PostgreSQL 9.5 server on which I have scripts that create roles and databases for users automatically. Within these databases it would be helpful to enable specific extensions (e.g. pgcrypto),...
0
votes
1answer
95 views

What is the easiest way to install the latest postgis_tiger_geocoder?

Ubuntu distributes PostGIS with a core extension called PostGIS TIGER Geocoder, packaged as postgis_tiger_geocoder. Currently, as of PostGIS 2.2.2, it pulls data from TIGER2015. I would like to ...
2
votes
1answer
424 views

Why is intarray's push `+` so much faster than array-to-element concatenation `||`?

When it comes to using arrays of ints, I always default to using intarray. But, now I'm wondering why is the default concatenation so much slower. This answer probably requires some understanding of ...
2
votes
3answers
1k views

Adding value to int array

I looked at the postgres website and to add a integer to a intarray it says int[] + int. I have a int array in a table and I eventually want to insert the array with another value added to it into ...
3
votes
1answer
2k views

Extension created for public schema not available within database

So, I create the citext extension for schema public and I can see it's there, but then it's not available within the database in that schema. The database is in public as well, because it's created ...
2
votes
1answer
2k views

How do I resolve Postgresql error, “could not determine which collation to use for string comparison”?

When ever I run this SQL script I get: ERROR: could not determine which collation to use for string comparison This is what is on line 34: CREATE TEMPORARY TABLE weight_options AS SELECT ...
0
votes
1answer
251 views

postgres9.5: can't create extension on standby [closed]

I can't create extension on postgresql9.5 server (hot standby). During to attempting I received: db=# create extension pgrowlocks; ERROR: cannot execute CREATE EXTENSION in a read-only transaction ...
3
votes
1answer
2k views

Why earthdistance <@> operator returns different value than earth_distance function?

Seeing a different value with <@> and earth_distance with points as ( select '(1,1)'::point as p1 ,'(2,2)'::point as p2 ) select ((p1 <@> p2) * 1609.34::double precision) as ...
1
vote
1answer
176 views

PostGIS error building with `make` in ./extensions: make[1]: *** No rule to make target

Whenever I run make in extensions, I'm getting error. make for DIR in postgis postgis_tiger_geocoder postgis_topology address_standardizer; do \ echo "---- Making all in ${DIR}"; \ make -C "...
1
vote
1answer
38 views

Distributing and managing Postgres SCHEMAs with an EXTENSION?

Postgres permits distribution and management with CREATE EXTENSION. Is it more proper to write internal EXTENSIONS to manage third-party provided data, or to use scripts that DROP and CREATE SCHEMA? ...
3
votes
2answers
2k views

Why does 'plpgsql' not appear in the pg_extension table?

I have two Postgres 9.1 installations. One works properly, the other does not. On the working installation: dc=# select * from pg_extension; extname | extowner | extnamespace | extrelocatable | ...
3
votes
1answer
1k views

Connect as postgres user on AWS RDS to install extension to pg_catalog

I need to connect to our AWS RDS PostgreSQL 9.5 instance so I can add the citext extension to pg_catalog schema so it is available to all schemas (PG prepends pg_catalog to the beginning of the ...
3
votes
1answer
2k views

PostgreSQL 8.4 no levenshtein fuzzymatching?

I'm using debian squeeze package, it says function does not exist?
3
votes
1answer
2k views

Trouble with installing additional modules on Postgresql 9.1 on ALT-Linux

I'm trying to create extension under postgresql 9.1 (Linux): CREATE EXTENSION pg_trgm; but got error: ERROR: could not open extension control file "/usr/share/pgsql/extension/pg_trgm.control": No ...