Questions tagged [postgresql-extensions]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
31 views

Create GIN index in a schema with the pg_trgm extension

I'm migrating entities from the public schema into a new one, let's call it myschema. Originally, the pg_trgm extension was installed and the index was created in a schema migration with: CREATE ...
0
votes
0answers
36 views

Makefile:34: /usr/lib64/pgsql/pgxs/src/makefiles/pgxs.mk: No such file or directory

While installing pg_repack, I'm getting the error below: pgxn install pg_repack INFO: best version: pg_repack 1.4.5 INFO: saving /tmp/tmp1s5fkkn9/pg_repack-1.4.5.zip INFO: unpacking: /tmp/...
0
votes
1answer
262 views

Is it possible to add Postgres extensions to AWS Aurora?

I am trying to futureproof a Postgres database so it can be transferred to AWS Aurora. I am using the guide extension uuid-ossp. Is it possible to add PostGres extensions to AWS Aurora? In ...
0
votes
0answers
49 views

Can't enable wal2json in postgres 12 docker image

I'm trying to enable wal2json in an image extended from postgres:12 image. But i'm getting an error: postgres=# create extension wal2json; ERROR: could not open extension control file "/usr/share/...
2
votes
1answer
27 views

Are C++ vectors in a user-defined aggregate function a POD for Postgres?

The official postgres documentation (docs) states in section "37.10.11. Using C++ for Extensibility" that If calling backend functions from C++ code, be sure that the C++ call stack contains only ...
0
votes
1answer
46 views

Custom Aggregate Postgresql C extension with input as initial condition

Is there a way to write a custom aggregate Postgresql C extension function that takes an input from the user and sets it as the initial condition? If so, how? I’ve written several Postgresql ...
1
vote
1answer
34 views

How to package a PostgreSQL extension as a binary .deb file in Ubuntu?

What is the minimal-effort way to package a PostgreSQL extension into a .deb file for Ubuntu (as of 18.04)? I did some search but find many different suggestions (some outdated). Suppose I want to ...
0
votes
0answers
86 views

In Amazon RDS, how does one enable PLV8 extension?

I see it is available. Just can't find the right documentation to help me enable it. From the docs, I entered the following command and got: postgres=> select * from pg_available_extensions where ...
1
vote
2answers
274 views

Does PostgreSQL btree_gin extension use btree or gin data structure?

In order to define a GIN index on an array and scalar field (for example) you need to enable the btree_gin extension in PostgreSQL. Then you can define your indexes as a GIN index. However I don't ...
1
vote
1answer
128 views

How to select functions that belong in a given extension in PostgreSQL?

I am trying to compose a SELECT from pg_catalog that pulls function name, its schema name and the name of extension that created this function. To pull function list I use (a simplified version) ...
1
vote
1answer
364 views

LTREE extension does not exist after CREATE EXTENSION

I am trying to create a trigger that uses ltree column. CREATE EXTENSION IF NOT EXISTS ltree; Running SELECT * FROM pg_catalog.pg_extension gives the following output: extname |extowner|...
1
vote
1answer
18 views

Can I do a backup without extension functions?

I use the PostGIS extension on my database. The thing is when I do a backup and then a restore, all the PostGIS functions give a warning during the restore process, because the functions already exist,...
0
votes
0answers
109 views

Create extension for user

My DB has two users, the superuser and a (non-superuser) worker. I've installed the tsm_system_rows extension using CREATE EXTENSION tsm_system_rows; on the superuser, and I can use the new ...
0
votes
0answers
42 views

How can I only load one function from an extension?

In PostgreSQL, I am loading the uuid-ossp extension so I can use the function that creates random UUIDs in my queries: create extension if not exists "uuid-ossp"; However, as well as making ...
1
vote
1answer
1k views

Cannot run 'uuid-ossp' functions even when it's activated

I want to generate a v4 uuid in PostgreSQL using the extension uuid-ossp, so in psql I ran: CREATE EXTENSION "uuid-ossp"; Then I ran SELECT uuid_generate_v1(); or SELECT uuid_generate_v4(); but I ...
3
votes
1answer
313 views

Postgres wait_event_type Extension

I have a function that was written in Language SQL (not plpgsql) that is scheduled to be called nightly from pgAgent. It was running fine until last night. The function usually takes under 3 minutes ...
0
votes
1answer
424 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 ...
1
vote
0answers
835 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 ...
1
vote
1answer
1k 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. ...
1
vote
1answer
818 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
493 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
4k 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
78 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
136 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 ...
6
votes
1answer
1k 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-...
12
votes
1answer
7k 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
165 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.
2
votes
1answer
1k 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
242 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 ...
2
votes
1answer
217 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
2k 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
9k 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....
4
votes
3answers
9k 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
280 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
561 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
668 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
356 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
152 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 ...
6
votes
1answer
2k 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
689 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 ...
14
votes
1answer
17k 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),...
2
votes
1answer
557 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
2k 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
2answers
3k 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 ...
3
votes
1answer
6k 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
340 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 ...
27
votes
3answers
34k 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
122 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 ...
4
votes
2answers
2k 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
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 ...