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 ...
- 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-...
- 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 ...
- 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. ...
- 70.6k
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":&...
- 151k
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, ...
- 151k
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 ...
- 151k
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
( ...
- 92.7k
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 ...
- 151k
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 ...
- 151k
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 (...
- 151k
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 ...
- 151k
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 ...
- 549
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 ...
- 92.7k
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 ...
- 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
...
- 151k
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 ...
- 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 $...
- 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:
...
- 151k
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 ...
- 8,986
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) = ...
- 151k
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.
...
- 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 ...
- 151k
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 ...
- 151k
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 ...
- 151k
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 ...
- 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 ...
- 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. ...
- 151k
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 ...
- 296
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, ...
- 570
Only top scored, non community-wiki answers of a minimum length are eligible