31

pg_trgm is an extension, so: CREATE EXTENSION pg_trgm; If you get the following error ERROR: could not open extension control file ".../extension/pg_trgm.control": No such file or directory" then you need to install the module for your operating system Ubuntu/Debian: sudo apt install postgresql-contrib Redhat/Centos sudo dnf install postgresql10-...


16

Don't install extensions to pg_catalog (unless that's their default: very few extensions are designed that way), because you don't mess with system catalog, ever. @Chris demonstrates one reason why. There are others. However, the "public" schema is in no way special. It's just the default schema that's pre-installed in standard distributions so we can get ...


11

From the docs on Extensions, superuser (boolean) If this parameter is true (which is the default), only superusers can create the extension or update it to a new version. If it is set to false, just the privileges required to execute the commands in the installation or update script are required. The value isn't set in pgcrypto.control, so it's ...


8

This is a frequently asked question. Users and SQL calls them "extensions". You add them with CREATE EXTENSION. They get packaged by distros like Ubuntu as postgresql-contrib or the like. The docs calls them "Additional Supplied Modules", but "modules" isn't really used anywhere else. I'm not sure you should ever call anything a module, but it seems to be ...


8

Is it generally true that any PL can be both set up to be trusted and untrusted? No. Any PL can be marked as trusted or untrusted, but if you park a PL that doesn't support running trusted as trusted, it'll either notice that and ERROR or it'll silently run without sandboxing as if it were untrusted. Don't modify catalogs directly. If not, what are the ...


8

You appear to be attempting to load uuid-ossp extension by loading the .sql file directly. You should not be doing that on any modern PostgreSQL, and it won't work on RDS. Use CREATE EXTENSION "uuid-ossp"; instead.


8

In PostgreSQL 9.0 and later, PL/pgSQL is pre-installed by default. Version 9.0 also introduced CREATE OR REPLACE LANGUAGE: CREATE OR REPLACE LANGUAGE will either create a new language, or replace an existing definition. If the language already exists, its parameters are updated according to the values specified or taken from pg_pltemplate ... To ...


7

You have to install the additional module fuzzystrmatch. In PostgreSQL 9.1 or later, simply run CREATE EXTENSION once per database: CREATE EXTENSION fuzzystrmatch; In PostgreSQL 8.4 on Debian Squeeze, you would run as OS postgres matching the DB superuser postgres (so with peer authentication without PW) the SQL script provided by the package postgresql-...


7

I see two possibilities: 1. Install the extension into a template database (just a regular database) and use it as template for CREATE DATABASE: CREATE DATABASE new_db TEMPLATE my_template_db; That copies all installed extensions into the new database. Your user obviously can do that. Also, if she created the database she should have the necessary ...


7

Are they the same? No. The Postgres documetation for uuid-ossp suggests using gen_random_uuid() If you only need randomly-generated (version 4) UUIDs, The uuid-ossp extension also provides other types of UUID (such as mac-addresses based) The difference? I looked at the source and discovered that uuid_generate_v4() uses arc4random to determine the ...


6

Installing extensions into pg_catalog are, as far as I'm aware, not advised. You should use the default public schema, which is also in the search_path by default. Why? As an example, I will work with the pageinspect extension which I've already created within the public schema. All functions are, by default, accessible to all schemas in the database if ...


5

pgcrypto is a regular Postgres extension. You have to install it once per database (not per DB cluster like you may have been assuming) to use it. Installation is permanent, surviving DB restarts. If you want every new database in the same DB cluster to have it pre-installed, you can install it to the default template database template1 by connecting to it ...


5

The length aside, a btree index would not help that query. You could create a hash index but that would also only help if the query wants an exact match for the whole column. not for a substring pattern. To do what you want first, add the pg_trgm extension: CREATE EXTENSION pg_trgm; Then create a trigram index: CREATE INDEX trgm_idx ON sometable USING ...


5

This is not 100% proven, but the following pass of the documentation suggests you just cannot have your control files in other locations: extension_name The name of the extension to be installed. PostgreSQL will create the extension using details from the file SHAREDIR/extension/extension_name.control. I can imagine one can work it around on systems ...


4

The package to install is postgresql-plpython3. By way of dependencies, this will install postgresql-plpython3-9.1, which provides these files: $ dpkg -L postgresql-plpython3-9.1 /. /usr /usr/share /usr/share/doc /usr/share/doc/postgresql-plpython3-9.1 /usr/share/doc/postgresql-plpython3-9.1/copyright /usr/share/postgresql /usr/share/postgresql/9.1 /usr/...


4

Found the error in the Release Notes for 9.1.2 Make contrib/citext's upgrade script fix collations of citext columns and indexes (Tom Lane) Existing citext columns and indexes aren't correctly marked as being of a collatable data type during pg_upgrade from a pre-9.1 server. That leads to operations on them failing with errors such as "could ...


4

They do different things, earth_distance Returns the great circle distance between two points on the surface of the Earth. <@> Gives the distance in statute miles between two points on the Earth's surface. So this is the difference between the average of the two (assuming neither is correct) is given as, SELECT ((d2-d1) / ((d2+d1)/2)) * 100 AS ...


3

It seems as if || is doing a lot more for multidimensional arrays. For instance with || catching the following case SELECT ARRAY[ARRAY[1]] || 5; ERROR: argument must be empty or one-dimensional array Whereas, with + the array is flattened.. SELECT ARRAY[ARRAY[1]] + 5; ?column? ---------- {1,5} (1 row) In addition it seems intarray does not handle ...


3

Use the concatenation operator myarray || 9 Details in the manual: https://www.postgresql.org/docs/current/9.1/functions-array.html


3

For those who are interested in an "official source", there is the PL Matrix in PostgreSQL wiki, which contains a "(un)trusted" column telling whether a language is trusted or not.


3

1) Log into postgres psql -U <DB_USERNAME> 2) After you are connected, switch to the DB you want to install the extension for: \c <DB_NAME> 3) Then install the extension as answered previously: CREATE EXTENSION pg_trgm; Installing the extension initially gave me issues because I was not doing step 2. I thought the installation was a global ...


3

On PostgreSQL 9.1, it is possible to get to this state without directly manipulating the catalogs. I started from having plpgsql as an extension, and did the following: # DROP EXTENSION plpgsql CASCADE; -- do this only if you are prepared to lose your plpgsql functions, too # \dx List of installed extensions Name ...


3

The solution I found involes mimicing the utf8 module's AUTOLOAD function in the plperl.on_init setting: plperl.on_init = 'use utf8; use re; package utf8; require "utf8_heavy.pl";' This successfully loads and initializes the utf8_heavy.pl file before dropping into Postgres's safe mode for running perl scripts.


3

You seem to have two versions (9.6 and 9.3) installed (because the psql version is 9.6). Because the postgresql-contrib package automatically defaults to the currently supported PostgreSQL database contrib package (as noted if you do apt-cache show postgresql-contrib), you'll have to install the contrib package for 9.3: apt-get install postgresql-contrib-9....


3

The problem is that the needed contrib files are not installed. This was actually the case for several contrib modules. I found help with the solution to this here: https://wiki-bsse.ethz.ch/display/ITDOC/Get+PostgreSQL+contrib+modules+on+Mac+OS+X $ su # port build postgresql92 # cd `port work postgresql92`/postgresql-<version>/contrib # for d in *; ...


3

pgxn will install using the PostgreSQL associated with whatever pg_config it finds first in $PATH. If you set your PATH (either permanently or temporarily) to list first the directory containing that pg_config then that should fix the problem sudo su PATH=/some/version/postgres/bin:$PATH pgxn install pg_repack


2

Since you already store the values (rmin, rmax, gmin, gmax, bmin, bmax) for the image column, a btree index on those covers equality checks just fine: CREATE INDEX foo1 ON icons (rmin, rmax, gmin, gmax, bmin, bmax); This query will use the index: SELECT COUNT(*) FROM avatars a JOIN icons i WHERE a.id = 123 AND (a.rmin, a.rmax, a.gmin, a.gmax, a....


2

Short answer You are actually using (at least) two databases. You are installing CITEXT in the wrong one. Longer answer In the first database (whose name is not shown), you install CITEXT. This database is probably the postgres database, which is created by default when you install PostgreSQL. Then you use the \c command and you switch to another ...


2

The core performance problem is the selectivity estimation for the predicate path ilike '%cat%', which is off by a factor of 130: (cost=0.00..809.15 rows=1753 width=0) (actual time=2108.286..2108 .286 rows=223590 loops=1) Probably leading to a sub-optimal query plan. In this particular case, Postgres identifies a ...


2

It doesn't work like that, from the a mailing list thread in 2017-2018 We searching for continuous extent of second string trigrams (in original orders) which has best similarity with first string trigrams. Possible solution could be forcing this extent boundaries to be at word boundaries. However, it would become less convenient to search for part ...


Only top voted, non community-wiki answers of a minimum length are eligible