50
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 column like the one you want to add does not require the table to be re-written, it just requires metadata to be updated.
Where the problem can come in, and I ...
35
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-see-query-used-to-create-materialized-view
35
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 apply the patch, and compile the extension module yourself and install it).
What the oldest version does is search for each trigram in the query and take the ...
30
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
( product_id WITH =,
quantity WITH =,
daterange(start_date, end_date, '[]') WITH && -- this is the crucial
);
The constraint can be ...
30
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. '
'That I want to split, in the code, not in the log table, '
'over 3 lines for readability.'
);
answered Jul 14 '16 at 10:38
a_horse_with_no_name
65.9k12 gold badges128 silver badges166 bronze badges
28
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, t.tag_array
FROM items i
JOIN ( -- or LEFT JOIN ?
SELECT it.item_id AS id, array_agg(t.title) AS tag_array
FROM items_tags it
JOIN tags ...
answered May 17 '17 at 22:54
Erwin Brandstetter
139k17 gold badges328 silver badges474 bronze badges
27
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 be fetched in that order.
But this is an implementation detail and there are no guarantees. In particular, the order is not necessarily maintained in more complex queries ...
answered Mar 20 '15 at 22:47
Erwin Brandstetter
139k17 gold badges328 silver badges474 bronze badges
27
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 graph_avg_view AS
SELECT xaxis, AVG(value) AS avg_val
FROM graph
GROUP BY xaxis;
And materialize the result once or whenever you need to start over:
CREATE ...
answered Dec 22 '14 at 18:05
Erwin Brandstetter
139k17 gold badges328 silver badges474 bronze badges
27
There is a way: combine the containment operator @> with the ANY construct:
SELECT d
FROM grp
WHERE d->'customers' @> ANY (ARRAY ['[{"id":"1"}]', '[{"id":"5"}]']::jsonb[]);
Or:
...
WHERE d->'customers' @> ANY ('{"[{\"id\": \"1\"}]","[{\"id\": \"5\&...
26
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 jsonb_array_elements_text(p.data->'children') pc(child) ON TRUE
LEFT JOIN test c ON c.id = pc.child::int;
db<>fiddle here
About jsonb_array_elements_text():
How to turn ...
26
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 identifier), called ItemPointer in the C-language source code. The manual:
This is the data type of the system column ctid. A tuple ID is a
pair (block number, ...
answered May 27 '14 at 19:19
Erwin Brandstetter
139k17 gold badges328 silver badges474 bronze badges
26
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 install pgdg-redhat94-9.4-1.noarch.rpm
4. yum install postgresql94-server
5. service postgresql-9.4 initdb
6. chkconfig postgresql-9.4 on
Backup Data
7. su - ...
26
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 (tbl_id int, usr jsonb);
"user" is a reserved word and would require double quoting to be used as column name. Don't do that. I use usr instead.
Query
The ...
25
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 DISTINCT. I think it would be most efficient if there is a separate index on each of the four columns It would be efficient with a separate index on each of the ...
22
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 percentile: returns the first input value whose position in the ordering equals or exceeds the specified fraction
The syntax is a bit strange for an aggregate, ...
22
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 $function$
BEGIN
RAISE WARNING 'I am called with %', val;
RETURN sqrt(val);
END;
$function$;
And then call this like you do:
SELECT this_here(i) FROM ...
21
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 objects that depend on those
objects
BE CAREFUL - emphasis above mine.
Obviously you'll need to recreate the schema afterwards.
To just drop all tables in the ...
20
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
work_mem is small enough that the bitmap becomes lossy. Thoughts?
http://www.postgresql.org/message-id/464F3C5D.2000700@enterprisedb.com
While this is all true ...
19
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 a materialized view meant locking the entire table, and therefore preventing anything querying it, and if a refresh took a long time to acquire the exclusive ...
19
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 a misunderstanding. The index itself (containing all the data) is not in the dump. Just the commands to recreate it. So, indexes get "transferred", but really, ...
17
Forcing correct values is one thing. Simple CHECK constraints per column can do the job reliably:
CREATE TABLE foo
foo_id serial PRIMARY KEY
, text_column text CHECK (upper(text_column) = text_column)
, ...
);
Auto-correcting all input is another thing, and not as simple. But it can be done with a generic trigger function that looks up column names ...
17
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 ...
17
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.
If the extension only includes simple plpgsql and sql functions you can add the functions manually. That is not possible with anything requiring superuser ...
17
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:
SELECT id, ARRAY(SELECT json_object_keys(obj)) AS keys
FROM tbl_items;
Or use jsonb_object_keys() for jsonb.
This returns an array of keys per row (not for ...
answered Jan 25 '16 at 15:02
Erwin Brandstetter
139k17 gold badges328 silver badges474 bronze badges
17
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 order of rules matter here: the first one that matches the access method, username, database name and source IP range will be considered. If it fails, then there ...
17
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 BY i.id, i.title,
16
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 anything about other columns:
Basic example: duplicate the row with id = 2 but replace 2 with 3:
INSERT INTO people
SELECT (p #= hstore('id', '3')).* FROM ...
15
Currently there are a few available extensions in Amazon RDS -- detailed in the "Database Engine Features" chapter.
You can issue the following command to get the available extensions supported:
SHOW rds.extensions;
15
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 query plan as expr = ANY(<set>) that can use a plain index. Details:
IN vs ANY operator in PostgreSQL
Consequently, the following two queries are ...
15
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, CURRENT_DATE-1, '1 day'::interval) d ),
counts as (
select
days.day,
sum((random()*5)::integer) num
FROM days
-- left ...
Only top voted, non community-wiki answers of a minimum length are eligible