44
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
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 ...
33
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
28
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
62.5k12 gold badges118 silver badges157 bronze badges
27
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 ...
26
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
130k17 gold badges306 silver badges451 bronze badges
26
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
130k17 gold badges306 silver badges451 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
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 ...
25
SELECT (ctid::text::point)[0]::bigint AS page_number FROM t;
Your fiddle with my solution.
@bma already hinted something similar in a comment. Here is a ...
Rationale for the type
ctid is of type tid (tuple identifier), called ItemPointer in the C code. Per documentation:
This is the data type of the system column ctid. A tuple ID is a
pair (block ...
answered May 27 '14 at 19:19
Erwin Brandstetter
130k17 gold badges306 silver badges451 bronze badges
24
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
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\&...
22
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
130k17 gold badges306 silver badges451 bronze badges
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 ...
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 ...
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
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 ...
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
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 ...
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 ...
16
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, ...
15
Internally, there are two separate forms of IN, as well as for 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 equivalent and both ...
14
Turns out this wasn't as complicated as I thought! (With just a little knowledge of pg_catalog...)
Part 1: Query whether a materialized view exists:
SELECT count(*) > 0
FROM pg_catalog.pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'm'
AND n.nspname = 'some_schema'
AND c.relname = 'some_mat_view';
Nice and easy.
Part 2: ...
14
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;
13
I have created a small test for checking this:
CREATE TABLE jsonb_test (id serial, data jsonb);
Then inserted some generated data:
INSERT INTO jsonb_test (data)
SELECT row_to_json(x.*)::jsonb
FROM (SELECT i AS bla,
ARRAY[i, i+1, i+2] AS foo,
ARRAY[('v' || i::text, i), ('v' || (i * 10)::text, i)] AS bar
FROM ...
13
You could use LATERAL, like in this query:
SELECT DISTINCT
x.n
FROM
atable
CROSS JOIN LATERAL (
VALUES (a), (b), (c), (d)
) AS x (n)
;
The LATERAL keyword allows the right side of the join to reference objects from the left side. In this case, the right side is a VALUES constructor that builds a single-column subset out of the column values you ...
Only top voted, non community-wiki answers of a minimum length are eligible
