Tag Info

Hot answers tagged

6

The name of the additional module hstore is derived from "hash store", because it was inspired by perl hash. Oleg Bartunov and Teodor Sigaev - the authors of the module - say so in their presentation here: Hstore — key/value storage (inspired by perl hash)


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

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


3

Using debugging parameters like enable_nestloop should not change the results of a query. If it does that'd generally be a bug. However, you should not use enable_nestloop = off or similar in production. It's a very big hammer, and while it might appear to fix your immediate problem, it'll create performance problems elsewhere. A nested loop is often the ...


2

Quick and dirty If it's ok to raise an exception, simply cast to regclass: SELECT 'foo'::regclass If 'foo' is found, you get back the oid, output as text representation, so that's just the table name, escaped if necessary. Else you get an error message complaining about the invalid cast. This has two major shortcomings: The search includes implicit ...


2

To convert an hexadecimal string to characters, the encoding must be specified. This is necessary because the same series of bytes may produce different characters depending on their encoding. For instance, if the hex codes represent an utf-8 string, use: select convert_from(decode(your_hex_string, 'hex'), 'utf-8'); If the encoding is not supported by ...


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

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, '[]')), ...


1

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


1

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


1

You can convert the config value to an array and replace the $user with the current user name. The array can then be used in the where condition: where n.nspname = any(string_to_array(replace(current_setting('search_path'), '$user', current_user), ','))


1

Exclude users without emails Assuming we only want users that actually have emails. Users without emails are ignored. The reason I went with this assumption at first is that all your queries do that already: LEFT JOIN emails on users.id = emails.user_id WHERE emails.email LIKE 'a' || '%%' By adding a WHERE condition on emails.email you effectively ...


1

What you are seeing is SQL Server data pages in a backup file. Or a SQL Server data file (.MDF). Either way, is is a collection of 8k data pages. These would be restored or attached to a SQL Server. They are not conveniently readable without a SQL Server install. No SQL Server = no access to the data. Quite simple. If you don't have access to a SQL ...


1

If you don't always want to pass all parameters, create a function with parameter defaults. The basic, simple form would be an SQL function without dynamic SQL: CREATE OR REPLACE FUNCTION func( _a int = NULL , _b text = NULL , _c text = NULL) RETURNS TABLE(...) AS $func$ SELECT ... FROM ... WHERE (speed = $1 OR $1 IS NULL) AND ...


1

You can use Dynamic queries. Here is a page about it. Check out about 2/3rds of the way down after "Example 40-1. Quoting Values In Dynamic Queries". But like it states, you need to be VERY careful. Doing this type of SQL creation on the fly can be a huge hole for SQL injection. http://www.postgresql.org/docs/current/static/plpgsql-statements.html


1

You can't use on delete set null if any of the foreign key columns don't accept null: create schema stack; set search_path=stack; -- create table t1( foo integer , bar integer , primary key(foo,bar) ); -- create table t2( foo integer , baz integer , bar integer , primary key ...



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