52 votes

Database "frozen" on ALTER TABLE

The command you wish to run does take an ACCESS EXCLUSIVE lock on the table, preventing all other access to that table. But the duration of this lock should be just a few milliseconds, as adding a ...
user avatar
  • 31.6k
38 votes

Query the definition of a materialized view in Postgres

Looks like 9.3 and up you can do: select * from pg_matviews; select * from pg_matviews where matviewname = 'view_name'; More info found here: https://stackoverflow.com/questions/29297296/postgres-...
user avatar
  • 481
35 votes
Accepted

Trigram search gets much slower as search string gets longer

In PostgreSQL 9.6 there will be a new version of pg_trgm, 1.2, which will be much better about this. With a little effort, you can also get this new version to work under PostgreSQL 9.4 (you have to ...
user avatar
  • 31.6k
35 votes
Accepted

How do I split a long PL/pgSQL line of code over multiple lines?

String constants can be split over multiple lines as documented in the manual INSERT INTO insert_log (log_time, description) VALUES ( now() , 'A description. Made up of 3 semi long sentences. ...
user avatar
31 votes
Accepted

PostgreSQL JSON query array against multiple values

There is a way: combine the containment operator @> with the ANY construct: SELECT d FROM grp WHERE d->'customers' @> ANY (ARRAY ['[{"id":"1"}]', '[{"id":&...
user avatar
31 votes
Accepted

Convert right side of join of many to many into array

To aggregate most rows While querying all or most items, it is typically substantially faster to aggregate rows from the "many"-table first and join later: SELECT id, i.title AS item_title, ...
user avatar
30 votes
Accepted

Refresh materalized view incrementally in PostgreSQL

You can always implement your own table serving as "materialized view". That's how we did it before MATERIALIZED VIEW was implemented in Postgres 9.3. You can create a plain VIEW: CREATE VIEW ...
user avatar
30 votes
Accepted

Uniqueness constraint with date range

Yes, you can use an EXCLUDE constraint, which is a generalization of UNIQUE constraints: ALTER TABLE prices ADD CONSTRAINT unique_price_per_product_quantity_daterange EXCLUDE USING gist ( ...
user avatar
29 votes
Accepted

PostgreSQL joining using JSONB

This would be more efficient: With jsonb and jsonb_array_elements_text() in pg 9.4+ EXPLAIN SELECT p.id AS p_id, p.data , c.id AS c_id, c.data FROM test p LEFT JOIN LATERAL ...
user avatar
28 votes
Accepted

Does Postgres preserve insertion order of records?

The answer for this simple case is: "Yes". Rows are inserted in the provided order in the VALUES expression. And if your id column is a serial type, values from the underlying sequence will ...
user avatar
28 votes

How to get particular object from jsonb array in PostgreSQL?

It would be much more efficient to store your values in a normalized schema. That said, you can also make it work with your current setup. Assumptions Assuming this table definition: CREATE TABLE tbl (...
user avatar
27 votes
Accepted

How do I decompose ctid into page and row numbers?

SELECT (ctid::text::point)[0]::bigint AS block_number FROM t; db<>fiddle here @bma suggested something similar in his comment. Here is a ... Rationale for the type ctid is of type tid (tuple ...
user avatar
26 votes
Accepted

How to upgrade PostgreSQL from version 8.4 to 9.4?

This is how I solved my problem. Upgrade Postgresql 8.4 to 9.4 in Centos 1. Yum Install PG9.4 2. wget http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-redhat94-9.4-1.noarch.rpm 3. yum ...
user avatar
26 votes
Accepted

SELECT DISTINCT on multiple columns

Update: Tested all 5 queries in SQLfiddle with 100K rows (and 2 separate cases, one with few (25) distinct values and another with lots (around 25K values). A very simple query would be to use UNION ...
user avatar
24 votes

postgres how to drop all the table in a schema through command

You want to use the CASCADE option of DROP SCHEMA. From the documentation: CASCADE - Automatically drop objects (tables, functions, etc.) that are contained in the schema, and in turn all ...
user avatar
  • 30.8k
23 votes
Accepted

"Recheck Cond:" line in query plans with a bitmap index scan

As @Chris commented correctly on the referenced question: a little investigation seems to indicate that the recheck condition is always printed in the EXPLAIN, but is actually only performed when ...
user avatar
22 votes
Accepted

Get 10th and 90th percentile by customer

It seems you are after the percentile_disc() ordered-set aggregate function. The documentation says the following about it: percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression) discrete ...
user avatar
  • 28.8k
22 votes
Accepted

Same function in SELECT and WHERE clause

Let's create a function that has a side effect so that we can see how many times it is executed: CREATE OR REPLACE FUNCTION test.this_here(val integer) RETURNS numeric LANGUAGE plpgsql AS $...
user avatar
  • 28.8k
21 votes
Accepted

Postgres query to return JSON object keys as array

Sure, with json_object_keys(). This returns a set - unlike the JavaScript function Object.keys(obj) you are referring to, which returns an array. Feed the set to an ARRAY constructor to transform it: ...
user avatar
20 votes

Refresh materalized view incrementally in PostgreSQL

Concurrent Update (Postgres 9.4) While not an incremental update as you asked for, Postgres 9.4 does provide a new concurrent update feature. To quote the doc… Prior to PostgreSQL 9.4, refreshing ...
user avatar
20 votes
Accepted

PostgreSQL force upper case for all data

Forcing correct values is one thing. A simple CHECK constraint per column can do the job reliably: CREATE TABLE foo foo_id serial PRIMARY KEY , text_column text CHECK (upper(text_column) = ...
user avatar
20 votes
Accepted

Amazon RDS Postgresql adding new extensions

You can't add your own extensions to RDS, at least not ones that require superuser rights (like anything with C code). This is one of the downsides you accept in exchange for convenient management. ...
user avatar
  • 51.8k
20 votes
Accepted

Index not used with = ANY() but used with IN

Internally, there are two separate forms of IN, and also two separate forms of the ANY construct. One of each, taking a set, is equivalent to the other and expr IN (<set>) also leads to the same ...
user avatar
19 votes
Accepted

Duplicate row with Primary Key in PostgreSQL

Simple with hstore If you have the additional module hstore installed (instructions in link below), there is a surprisingly simple way to replace the value(s) of individual field(s) without knowing ...
user avatar
19 votes
Accepted

Do indexes get transferred with pg_restore

Do indexes get transferred with pg_restore? I see other questions that have been answered in the negative (i.e. indexes do not get transferred over with the standard pg_restore) That seems to be ...
user avatar
19 votes

Convert right side of join of many to many into array

You need to add the group by clause and use array_agg. SELECT i.id, i.title, array_agg(i.title) FROM items i INNER JOIN items_tags it ON it.item_id = i.id INNER JOIN tags t ON t.id = it.tag_id GROUP ...
user avatar
  • 55.7k
18 votes
Accepted

psql: FATAL: Peer authentication failed for user

In a fresh install from a few days ago, the second line of my pg_hba.conf is local all all peer I believe this is the one that makes your connection attempt fail. The ...
user avatar
  • 28.8k
17 votes

Is it recommended to install extensions into pg_catalog schema?

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. ...
user avatar
17 votes
Accepted

How to examine PostgreSQL server's SSL certificate?

It looks like OpenSSL's s_client tool added Postgres support using the -starttls in 1.1.1, so you can now use the full power of OpenSSL's command line tools without additional helper scripts: openssl ...
user avatar
16 votes
Accepted

Calculate rolling sum over 7 consecutive day period on PostgreSQL

By far the cleanest solution is to use window function sum with rows between: with days as ( SELECT date_trunc('day', d)::date as day FROM generate_series(CURRENT_DATE-31, ...
user avatar
  • 570

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