Questions tagged [postgresql-extensions]

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

Filter by
Sorted by
Tagged with
0
votes
0answers
28 views

Can't create a Postgres extension for pgaudit

I compiled pgaudit on Windows10 using Visual Studio. I added shared_preload_libraries = 'pgaudit' and restarted the server. When I try to add the extension, I get: ERROR: could not find function &...
0
votes
1answer
20 views

List PostgreSQL extensions per database

I have a Postgres 9.5 server with 30 databases. I would like to find out which database uses which extension(s) in one go, preferably in a query that is usable in pgAdmin. I know that SELECT * FROM ...
0
votes
2answers
57 views

Debug why Postgres wants to use an uninstalled extension for a function

I recently moved a Postgres function app_user_id from an old, custom extension called intpair into a new, custom extension called heap_exts. This worked well except for machines I restored from a ...
0
votes
1answer
279 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
1answer
2k 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 ...
1
vote
0answers
271 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
33 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
90 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
103 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
216 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
802 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
544 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) ...
2
votes
1answer
1k 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
28 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
153 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
1answer
118 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
2k 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
706 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
738 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 ...
2
votes
0answers
1k 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
2k 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
1k 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
613 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
5k 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
84 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
150 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-...
14
votes
1answer
10k 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
188 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
297 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
268 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 ...
3
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: ...
4
votes
1answer
12k 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....
3
votes
4answers
11k 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
328 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
616 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
730 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
405 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
268 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
3k 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
865 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 ...
15
votes
1answer
21k 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
608 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
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 ...
5
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
7k 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
394 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 ...
38
votes
3answers
44k 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
134 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 ...