102

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


97

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


56

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


32

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


27

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


26

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


23

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.


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

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


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


19

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

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


19

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


18

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


18

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


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


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

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


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

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

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.


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

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


14

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


14

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

The error occurs when pg_restore set the ACLs : you can use --no-acl to prevent GRANT commands. With the -Ft option in pg_dump, you can skip roles and ACLs only in pg_restore. You can also edit the catalog with --list if you need more details.


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

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


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