21

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-...


15

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 ...


8

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

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

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 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.


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 ...


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

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 ...


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 ...


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 ...


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

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 ...


4

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 ...


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

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

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

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 *; ...


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

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....


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

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

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 ...


2

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 ...


1

GIN indexes inherently do not support indexable order by. It either needs to find all results which match the ilike using the gin index, and then sort them. Or it needs to walk through the sortable index in sort order, until it accumulates the LIMIT worth of things which match the ilike. If you would like to force it to switch to the second mode to see ...


1

intarray is an extension, you need to add it first to use its push element onto array (add it to end of array) opperator.. Before you add the extension, you get this. SELECT ARRAY[1]::smallint[] + 1; ERROR: operator does not exist: smallint[] + integer LINE 1: SELECT ARRAY[1]::smallint[] + 1; ^ HINT: No operator matches ...


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