Tag Info

Hot answers tagged

6

This appears to be the cause of the problems, from the same page quoted by ypercube: Trying to update the same row twice in a single statement is not supported. Only one of the modifications takes place, but it is not easy (and sometimes not possible) to reliably predict which one. This also applies to deleting a row that was already updated in the same ...


6

This should work but I'm not really sure if it's the best regarding efficiency: WITH copy_to_other_table AS ( INSERT INTO other_table (column_a, column_b) SELECT column_a, column_b FROM main_table WHERE column_a = 1 ), main_table_deleted AS ( DELETE FROM main_table WHERE column_a = 1 AND NOT EXISTS ...


3

create table as needs a select statement: DROP TABLE IF EXISTS lookup; CREATE TEMP TABLE lookup as select * from ( VALUES (0::int,-99999::numeric), (1::int, 100::numeric) ) as t (key, value); You can also re-write this to use a CTE: create temp table lookup as with t (key, value) as ( values (0::int,-99999::numeric), ...


3

If you just want to select from some values, rather than just creating a table and inserting into it, you can do something like: WITH temp (k,v) AS (VALUES (0,-9999), (1, 100)) SELECT * FROM temp; To actually create a temporary table in a similar fashion, use: WITH vals (k,v) AS (VALUES (0,-9999), (1, 100)) SELECT * INTO temporary table temp FROM ...


3

First off, you do not want to use char(50). Use varchar(50) or just text. Read more: Any downsides of using data type “text” for storing strings? Assuming the following rules: Basic slugs never end with a dash. Duplicate slugs are suffixed with a dash and a sequential number (-123). Note that all of the following methods are subject to a race ...


3

Use conditional counting: select count(case when year <= 1945 then 1 end) as pre1945, count(case when year between 1946 and 1964 then 1 end) as period2, count(case when year between 1965 and 1974 then 1 end) as period3, ... from ... where ...; This works because count() ignores null values and the case statement returns a null for ...


2

If your data structure is fixed and out of your control, then the best way to do this is to use a recursive common table expression (CTE) like in this question. If you can change the structure then there are ways to make such queries considerably more efficient. What you have there is often called a "naive tree" - while it allows easy construction and easy ...


2

That spike is probably caused by dirty data pages being flushed to disk. Raise the checkpoint_segments parameter in the postgresql.conf file. http://www.postgresql.org/docs/current/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-CHECKPOINTS http://www.postgresql.org/docs/current/static/wal-configuration.html


2

If you want the result as rows in instead of as columns as in @a_horse's answer then create the year ranges in a CTE and join the table to it with years(year_range) as ( values (int4range(1900, 1945, '[]')), (int4range(1946, 1964, '[]')), (int4range(1965, 1974, '[]')), (int4range(1975, 1991, '[]')), (int4range(1992, 2005, '[]')), ...


2

2nd query: Of course you get duplicate rows. 1 row per producto each fabricante is connected to - multiplied with the number of rows in pais each combination is connected to. 1st query: An explicit JOIN binds before (groups of) comma-separated items in the FROM list. This is why you cannot reference fabricante in the JOIN condition between pais and ...


2

It's supposed to be the "distance" operator from the additional module pg_trgm. Per documentation: text <-> text real Returns the "distance" between the arguments, that is one minus the similarity() value. To use it you first have to install the module with: CREATE EXTENSION pg_trgm; More details: How is LIKE implemented? Theoretically, ...


2

It's not possible to save query plans "for all future sessions". Query plans are only ever saved for the current session. And only reused under a number of favorable conditions. Plans for ad-hoc SQL queries are not saved at all. All queries inside PL/pgSQL functions are treated like prepared statements. And there are more steps than just the query planning. ...


1

"a boolean column stating whether or not a group has any users" Use EXISTS: CREATE VIEW group_info AS SELECT g.name, NOT EXISTS (SELECT 1 FROM users u WHERE u.group = g.id) AS empty FROM groups g; This returns 1 row per group, no matter whether there are users or not - not one row per user like you had, but probably didn't want - so we don't need ...


1

select u.id, u.name, bool_or (a.admin) as is_admin, bool_and (a.admin) as is_complete_admin from users u inner join associations a on u.id = a.user group by u.id, u.name


1

Your combinations in order of typical performance: 1. > 2. > 4. ( > 3.) 3. is invalid. If rows are only unique per (code, class_id), the lookup by code alone can return multiple rows and is different from the rest. 2. is pointless. If code is unique, there is no point in adding another predicate on class_id - except to verify that a given code ...


1

To the extent your bottleneck is in streaming realtime reads and writes, you may want to look into the open source PostgreSQL extension: pg_shard It shards and replicates your PostgreSQL tables for horizontal scale and high availability. It also distributes your SQL statements, without requiring any changes to your application. ...



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