89

Postgres 9.4 or newer Obviously inspired by this post, Postgres 9.4 added the missing function(s): Thanks to Laurence Rowe for the patch and Andrew Dunstan for committing! json_array_elements_text(json) jsonb_array_elements_text(jsonb) To unnest the JSON array. Then use array_agg() or an ARRAY constructor to build a Postgres array from it. Or string_agg() ...


66

If I understand you correctly, you are looking for a filtered (conditional) aggregate: SELECT a.agent_id as agent_id, COUNT(a.id) filter (where disposition = 'Completed Survey') as CompletedSurvey, count(a.id) filter (where disposition = 'Partial Survey') as partial_survey FROM forms a WHERE a.created_at >= '2015-08-01' AND a....


45

Try here for a basic intro to PostgreSQL and JSON. Also, PostgreSQL documentation is pretty good, so try it here. Check out the pretty_bool option. Your original question was "Is there a way to export postgres table data as JSON". You wanted it in this format {'id':1,'name':'David'} {'id':2,'name':'James'} ... I didn't have a running instance of ...


22

You can query the system catalog with a recursive query, in particular pg_auth_members: WITH RECURSIVE cte AS ( SELECT oid FROM pg_roles WHERE rolname = 'maxwell' UNION ALL SELECT m.roleid FROM cte JOIN pg_auth_members m ON m.member = cte.oid ) SELECT oid FROM cte; BTW, INHERIT is the default behavior of CREATE ROLE and doesn't have to ...


22

About SSDs, the main advice is to lower 'random_page_cost' to 1 (equals to 'seq_page_cost') in postgresql.conf, in addition to others usuals settings.


21

No automatic predicate pushdown for CTEs PostgreSQL 9.3 doesn't do predicate pushdown for CTEs. An optimizer that does predicate pushdown can move where clauses into inner queries. The goal is to filter out irrelevant data as early as possible. As long as the new query is logically equivalent, the engine still fetches all the relevant data, so produces the ...


21

pg_trgm is an extension, so: CREATE EXTENSION pg_trgm; If you get the following error ERROR: could not open extension control file ".../extension/pg_trgm.control": No such file or directory" then you need to install the module for your operating system Ubuntu/Debian: sudo apt install postgresql-contrib Redhat/Centos sudo dnf install postgresql10-...


20

There are two tools which regularly get "honourable mentions" in this category. They are: http://www.liquibase.org/ and https://flywaydb.org They are specifically for database version control - Open Source, Apache licence. I believe that it's good to go to the source (pardon the pun...) in these matters. A Google of "database version control" leads one ...


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


18

Short version: SELECT a.oid FROM pg_authid a WHERE pg_has_role('maxwell', a.oid, 'member'); Here we use a version of pg_has_role that takes a role name as the subject and role oid to test for membership, passing member mode so we test for inherited memberships. The advantage of using pg_has_role is that it uses PostgreSQL's internal caches of role ...


17

"Streaming replication" refers to continuous sending of WAL records over a TCP/IP connection between the master and the replica, using the walsender protocol over replication connections. The master reads its own WAL from pg_xlog and sends it to the replica on demand. It's configured with a primary_conninfo directive in recovery.conf and pg_hba.conf entries ...


17

This is because it is the way pg_restore works. pg_restore manual reads as follow : -C, --create Create the database before restoring into it. If --clean is also specified, drop and recreate the target database before connecting to it. When this option is used, the database named with -d is used only to issue the initial DROP DATABASE ...


17

You probably don't want to hear this, but the best option to speed up SELECT DISTINCT is to avoid DISTINCT to begin with. In many cases (not all!) it can be avoided with better database-design or better queries. Sometimes, GROUP BY is faster, because it takes a different code path. In your particular case, it doesn't seem like you can get rid of DISTINCT. ...


17

You have at least two options. The first one makes use of a small query and a text editor. We have to collect the schemata of our interest: SELECT nspname FROM pg_namespace; You can add a WHERE clause if you want to limit the scope. Copy the output and amend it, so you get a number of GRANT USAGE ON SCHEMA ... TO your_role; commands. Then just feed ...


16

My locale settings were not properly configured when PostgreSQL was installed. Purging and reinstalling didn't help. I followed the instructions here and that did the trick for me. Essential parts of the linked information reproduced below: The problem showed itself in the following manner: warning: Please check that your locale settings: LANGUAGE = (...


16

PG 9.4+ The accepted answer is definitely what you need, but for the sake of simplicity here is a helper I use for this: CREATE OR REPLACE FUNCTION jsonb_array_to_text_array( p_input jsonb ) RETURNS TEXT[] AS $BODY$ DECLARE v_output text[]; BEGIN SELECT array_agg(ary)::text[] INTO v_output FROM jsonb_array_elements_text(p_input) AS ary; ...


16

so is there anything I should be afraid of? Not having backups. Like any storage device, it can die. Keep backups. If the data load is going to take ages, I'd back up the read-only db once I'd done the data load, by stopping it and copying it. That way if something went wrong it'd be easier to re-create later. Can I put the entire DB on the SSD, or just ...


15

This is a simplified version of Craig Ringer's answer that a non superuser can use directly: SELECT oid, rolname FROM pg_roles WHERE pg_has_role( 'maxwell', oid, 'member'); pg_roles is essentially a view on pg_authid accessible to public, as it doesn't reveal passwords, contrary to pg_authid. The base oid is even exported into the view. When not ...


13

Try this instead: CREATE INDEX user_reputation_idx ON users(cast("user"->>'reputation' AS int)); The Postgres syntax shortcut :: for casts is not allowed without additional parentheses in an index definition (see @bma's comment). It works with the standard SQL function, though: cast(expression AS type) This is not related to the json type per se. ...


13

For insert performance, see speeding up insert performance in PostgreSQL and bulk insert in PostgreSQL. You're wasting your time with JDBC batching for insert. PgJDBC doesn't do anything useful with insert batches, it just runs each statement. <-- This is no longer true in newer PgJDBC versions, which can now batch prepared statements to reduce round-...


13

Using OUT parameters achieve basically the same thing as in @klin's answer, but without creating user-defined types. Just move all your variables from the declare block into the argument-list as OUT parameters: create or replace function get_user_info( IN _id varchar, OUT is_banned boolean, OUT reputation integer, OUT is_vip boolean, ...


12

Looking at it as a grammar problem, ANY is defined as (in Row and Array Comparisons): expression operator ANY (array expression) But is distinct from is not an operator, it's a "construct" as we're told in Comparison Operators: When this behavior is not suitable, use the IS [ NOT ] DISTINCT FROM constructs Since PostgreSQL has user-defined ...


12

You could try it yourself: WARNING: there is already a transaction in progress It starts no new (sub)transaction as nested transactions are not implemented in PostgreSQL. (You may do some magic in a pl/pgsql function, for example, that mimics that behaviour, though.) With PostgreSQL 11, one could think the new real stored procedures and their ability ...


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

Use max(coalesce(logincount, 0)) to avoid NULLs According to Postgres docs (9.6): The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display, for example:


11

You seem to be trying to replicate from one server to another that wasn't set up using a copy of the original server. That's why: database system identifier differs between the primary and standby. The primary's identifier is 6022019027749040119, the standby's identifier is 6033562405193904122. Because each newly initdb'd PostgreSQL gets a new random ...


11

PostgreSQL (true up to at least 9.4) doesn't currently support removing a column with CREATE OR REPLACE VIEW. The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list. There is no ...


11

If you're using psql then there is no reason to use \COPY at all. \t \a \o file.json SELECT row_to_json(r) FROM my_table AS r; This is the same method we use to get png/jpgs/tifs out of the database with PostGIS for quick tests, and also to generate script files with PostgreSQL extensions.


11

Adding a new value into a unique index locks out (until commit) any other transaction from inserting the same value. When doing that with multiple values in the same transaction, these locks accumulate. Then when several parallel transactions do that concurrently in no particular order, that creates the opportunity for transactions to inter-lock each other, ...


11

(Obvious error in the trigger logic aside.) In Postgres 9.2 or later, use the function pg_trigger_depth() that @Akash already mentioned in a condition on the trigger itself (instead of the body of the trigger function), so that the trigger function is not even executed when called from another trigger (including itself - so also preventing loops). This ...


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