34
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 ...
29
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 ...
25
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
24
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 - ...
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 ...
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
This would be more efficient:
With json and json_array_elements() in pg 9.3
SELECT p.id AS p_id, p.data AS p_data
, c.id AS c_id, c.data AS c_data
FROM test p
LEFT JOIN LATERAL json_array_elements(p.data->'children') pc(child) ON TRUE
LEFT JOIN test c ON c.id = pc.child::text::int;
Use the -> operator instead of ->> in the reference to ...
21
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 ...
20
You can always implement your own table serving as "materialized view". That's what you had to do before MATERIALIZED VIEW was implemented in Postgres 9.3 either way.
For instance, 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 as a whole once or ...
answered Dec 22 '14 at 18:05
Erwin Brandstetter
102k10 gold badges214 silver badges339 bronze badges
19
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
45.8k9 gold badges90 silver badges122 bronze badges
18
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
102k10 gold badges214 silver badges339 bronze badges
16
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 ...
15
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
102k10 gold badges214 silver badges339 bronze badges
15
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 ...
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 ...
15
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
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 ...
15
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 ...
12
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 ...
12
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 ...
12
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: ...
12
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 ...
12
After many attempts to fix this problem, this command finally worked for me:
export LC_CTYPE=en_US.UTF-8 export LC_ALL=en_US.UTF-8
12
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 ...
12
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\"}]"}'::jsonb[]);
It's essential to cast the array to jsonb[] explicitly. And note that ...
11
Aaron,
In my recent work, I've been looking into some similar questions with PostgreSQL. PostgreSQL is almost always pretty good at generating the right query plan, but it isn't always perfect.
Some simple suggestions would be to make sure to run an ANALYZE on your progresses table to make sure that you have updated statistics, but this isn't guaranteed ...
11
@BrianEfting was correct, you can specify the appropriate cipher suites to only allow TLSv1.2 which should fit your PCI-DSS 3.1 specification needs.
Using a cipher list like this in the ssl_ciphers option in your postgresql.conf:
ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:DHE-RSA-...
11
Note: This answer addresses a couple of basic problems, but it's not the final solution. The question was still inconsistent after several requests for clarification, so I stopped processing.
General difficulty
The Problem is: predicates on some columns, ORDER BY on a different column.
In your fast query, without ORDER BY, the first (arbitrary) 10 rows ...
answered Jan 19 '16 at 16:49
Erwin Brandstetter
102k10 gold badges214 silver badges339 bronze badges
11
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 ...
answered Jan 25 '16 at 15:02
Erwin Brandstetter
102k10 gold badges214 silver badges339 bronze badges
Only top voted, non community-wiki answers of a minimum length are eligible
