Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [postgresql-extensions]

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

1
vote
1answer
123 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. ...
0
votes
1answer
146 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
130 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
732 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
53 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 ...
0
votes
1answer
75 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
358 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
2k 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
99 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.
1
vote
1answer
570 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 ...
4
votes
2answers
137 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 ...
1
vote
1answer
94 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 ...
2
votes
1answer
967 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: ...
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....
1
vote
1answer
5k 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
213 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=...
0
votes
1answer
349 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....
1
vote
2answers
253 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. ...
0
votes
1answer
178 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
votes
1answer
72 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 ...
5
votes
1answer
802 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
3answers
390 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
7k 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),...
3
votes
1answer
404 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 ...
3
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 ...
4
votes
1answer
1k 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
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?
0
votes
1answer
242 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 ...
17
votes
1answer
16k 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
89 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 ...
5
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
1answer
1k 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
172 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? ...
4
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 ...
5
votes
1answer
13k 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
495 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 ...
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 ...
2
votes
2answers
77 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 ...
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
0answers
164 views

Require Language In PostgreSQL Extension Control File

I'm developing a custom extension that uses plpythonu in several functions. I'd like to be able to require that plpythonu be installed in order to create my extension, but I don't see anything in the ...
9
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?
6
votes
1answer
3k views

PostgreSQL 9.3 Amazon RDS gives error while using UUID-OSSP extension for generating uuid

I have created DB instance on RDS and used below function. It gave error while with the same PostgreSQL version on a local development machine it works fine. create or replace function uuid() returns ...
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
2answers
659 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, ...
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 ...
3
votes
1answer
3k views

PostgreSQL CREATE EXTENSION file not found error on Mac OS

On PostgreSQL 9.2.3 when trying this: CREATE EXTENSION btree_gist; I get this error: ERROR: could not open extension control file "/opt/local/share/postgresql92/extension/btree_gist.control": No ...
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 ...
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 ...
3
votes
1answer
2k views

PostgreSQL 8.4 no levenshtein fuzzymatching?

I'm using debian squeeze package, it says function does not exist?