36

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 ...


31

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


27

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.' );


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

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 ...


24

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 ...


24

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 ...


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

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 ...


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

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 ...


21

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 ...


19

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

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 ...


18

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 ...


18

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 ...


16

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 ...


16

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 ...


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 ...


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

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 ...


15

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 ...


15

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 ...


13

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: ...


13

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

@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-...


13

This turns out to be due to a somewhat obscure error message which happens when the schema you are dumping from with pg_dump does not exist on the target database/machine. I will leave this question, in the hope it might help someone else.


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