111

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


102

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


57

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


36

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


31

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, oid::regrole::text AS rolename FROM cte; -- oid & name The manual about ...


30

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


24

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


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


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.


22

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


21

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


21

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


21

Quoting the manual: There are two ways to delete rows in a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the USING clause. Which technique is more appropriate depends on the specific circumstances. Bold emphasis mine. Using information that is not contained in another table ...


20

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[] LANGUAGE sql IMMUTABLE AS $function$ SELECT array_agg(ary)::text[] FROM jsonb_array_elements_text(p_input) AS ary; $function$; Then just do: SELECT ...


20

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


19

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


18

Create a sequence and use it as the default value for the column: create sequence measures_measure_id_seq owned by measures.measure_id; alter table measures alter column measure_id set default nextval('measures_measure_id_seq'); commit; That essentially what serial does. See the manual for details: http://www.postgresql.org/docs/current/static/...


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

You can do this with a function that runs in the security context of its owner. Function that refreshes the view (create it with the user that owns the MV/table): CREATE OR REPLACE FUNCTION refresh_mvw1() RETURNS void SECURITY DEFINER AS $$ BEGIN REFRESH MATERIALIZED VIEW mvw1 with data; RETURN; END; $$ LANGUAGE plpgsql; Grant execute on the function to ...


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

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


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


15

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


15

What you would need is a so called "autonomous transaction" (a feature provided by oracle). At this point this is not possible in PostgreSQL yet. However, you can use SAVEPOINTs: BEGIN; INSERT ... SAVEPOINT a; some error; ROLLBACK TO SAVEPOINT a; COMMIT; It is not entirely an autonomous transaction - but, it allows you get "every transaction" right. You ...


15

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.


15

You forgot to mention that you installed the additional module pg_trgm, which provides the similarity() function. Similarity operator % First of all, whatever else you do, use the similarity operator % instead of the expression (similarity(job_title, 'sales executive') > 0.6). Much cheaper. And index support is bound to operators in Postgres, not to ...


15

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:


14

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


14

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


14

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


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