Tag Info

Hot answers tagged

4

Generally, you can always wrap a query as subquery if you don't want to output all rows: SELECT title FROM (SELECT ...) sub; But you can also use expressions in ORDER BY, not just input or output columns. So there is no need for this (like @ypercube already commented). For the case at hand, it must be mentioned that you are using the additional module ...


4

You can cut this down quite a bit - the first thing to notice is that you don't need the information in the author_titles table at all to get your results: select author_id , sum(case max_sold when 1 then 30 when 2 then 60 when 3 then 100 else 0 end)/count(*) max_items_sold_wavg , sum(sold)/count(*) items_sold_wavg , created_at ...


2

You want current_setting: SELECT current_setting('pubsubpull.request_id');


2

Simpler view For this objective: The point of which is to give averages of the numeric columns for each hour .. it seems just as good to truncate to full hours, which is simpler and cheaper: CREATE OR REPLACE VIEW spp.rtprices_hourly AS SELECT date_trunc('hour', "interval") AS hour , node_id , round(avg(rtlmp), 2) AS rtlmp , ...


2

Tip: use psql's -E flag to get it to show you how what queries it is issuing for e.g. its \dm meta-command, and adjust from there. Here's a rough and not at all cleaned-up query, but it should do the trick... WITH matviews_with_keys AS ( SELECT c.oid, c.relname, c2.relname AS idx_name FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, ...


2

As of PostgreSQL 9.4: Different to the documentation of CREATE VIEW, the documentation of CREATE MATERIALIZED VIEW does NOT mention the REPLACE keyword. There seems to be no shortcut aside from dropping all dependent objects and rebuilding each one. When you do so, I can only recommend two small things: Use DROP MATERIALIZED VIEW blabla CASCADE to get a ...


2

First of all, this is an error message from PostgreSQL, which is not the same as psql (the command-line client). The SET command sets run-time parameters of the Postgres server, which is completely independent from the client (psql in this case). Either way, it works for me. Testing the standard distribution of Postgres 9.4.1 on a Windows client set to a ...


2

I Will answer my own question, From: http://stackoverflow.com/questions/13505785/postgresql-database-size-is-less-after-backup-load-on-heroku The reason of this is its MVCC system. Every time you UPDATE any record in a database it creates another "version" of this record instead of rewriting the previous. This "outdated" records will be deleted by VACUUM ...


1

Each pgAdmin query window has it's own session. You can run one transaction at a time in each session. It's not possible to run two transactions concurrently in the same session (at least not in pgAdmin or psql). You can open and bind more sessions to the same query window with the "connection bar" in the menu of the query window, then switch between ...


1

Unfortunately that's not that easy because there is no "select any view" privilege (as e.g. Oracle offers). If you can put all views into a single schema, then this could be achieved by granting the select privilege on that schema. Something like this: First you need to create a role that can be used for this create role view_reader; The role only ...


1

It's hard to tell what exactly your graphs say. But commit is designed to be slow. By default, Postgres waits for changes to be written to disk before it lets commit return. You can let commit complete before changes are written to disk with this option: synchronous_commit = off That should certainly help to spread the disk I/O required for commit over ...


1

You can simply do UPDATE my_tbl SET (a1, a2) = (a1 || 123465, a2 || 132456789) WHERE id = 321; To obtain a function, you simply have to replace the integer constants with the parameters. You can safely do it in a query language (aka SQL) function, no need for pl/pgsql at all.


1

Core feature is the window function lag(). Also pay special attention to avoid deadlocks and race conditions with concurrent deletes and inserts (which can affect which rows to delete!): CREATE OR REPLACE FUNCTION remove_vendor_price_dupes(_vendor int) RETURNS integer AS $func$ DECLARE del_ct int; BEGIN -- this may or may not be necessary: -- ...


1

I'm afraid it's not possible - at least according to the documentation Check this out here. To enable messages to be translated to the user's preferred language, NLS must have been selected at build time (configure --enable-nls). All other locale support is built in automatically. You have to configure the database when you install it. Is your ...



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