Tag Info

Hot answers tagged

6

This unicity constraint can be enforced with this unique index: CREATE UNIQUE INDEX idxname ON mytable(least(col1,col2),greatest(col1,col2)); Demo: test=> insert into mytable (col1,col2) values(1,2); INSERT 0 1 test=> insert into mytable (col1,col2) values(2,1); ERROR: duplicate key value violates unique constraint "idxname" DETAIL: Key ((LEAST(col1, ...


5

Use the ONLY key word: TRUNCATE ONLY public.history_uint; Per documentation: If ONLY is specified before the table name, only that table is truncated. If ONLY is not specified, the table and all its descendant tables (if any) are truncated. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are ...


5

This is a known issue regarding Postgres optimization. If the distinct values are few - like in your case - and you are in 8.4+ version, a very fast workaround using a recursive query is described here: Loose Indexscan. Your query could be rewritten (the LATERAL needs 9.3+ version): WITH RECURSIVE pa AS ( ( SELECT labelDate FROM pages ORDER BY labelDate ...


4

Your query, given your data, is equivalent to this one: SELECT emp.name, role.role_name, emp.emp_id FROM emp INNER JOIN role ON emp.role_id = 1; -- because this is the role_id for 'tech' This means that you don't define a condition about how to join the two tables. In turn, this results in a Cartesian product of role (without any restriction about ...


4

Use substring() with a regular expression instead: substring(ls.attribute_actions_text FROM 'name="(.*?)"/>') The dot (.) matches any character, *? is the non-greedy quantifier for a sequence of 0 or more matches and the parentheses (()) mark the substring to be returned. Like your code, this selects the first string matching the pattern and does not ...


4

Use a FULL [OUTER] JOIN, combined with two rounds of window functions: SELECT ts , min(foo) OVER (PARTITION BY foo_grp) AS foo , min(bar) OVER (PARTITION BY bar_grp) AS bar FROM ( SELECT ts, f.foo, b.bar , count(f.foo) OVER (ORDER BY ts) AS foo_grp , count(b.bar) OVER (ORDER BY ts) AS bar_grp FROM foo f FULL JOIN bar b ...


4

You could use the function age() to simplify your expression (returns interval). But it's much more efficient to use a sargable expression to begin with. This operates with the exact time difference (current time is relevant): SELECT * FROM accounts WHERE status = 'PENDING_PAYMENT' AND status_updated_at < now() - interval '10 days' To operate ...


3

TL;DR: No, except for some basic cases. Some lock-strength reductions for ALTER TABLE have been added to PostgreSQL 9.5. You can't do anything that requires a full table rewrite without an exclusive lock though, in 9.5 or below. Some operations, like ALTER TABLE ... DROP COLUMN or ALTER TABLE ... ADD COLUMN ... without a DEFAULT and NOT NULL can be done ...


3

why didn`t the first query fail? Because this is valid SQL. First of all consider that it is valid to run a SELECT against the table that doesn't reference any columns from that table. SELECT DISTINCT 'foo' FROM product_template WHERE type = 'import'; The above will return a single row result of foo if any rows exist that match the WHERE ...


3

Dynamic fields are notoriously difficult in plpgsql. In particular there's no way we can write new.variable := something where variable stands for a column name. See How to set value of composite variable field using dynamic SQL for ways that involve querying the catalog at runtime. Personally, I'd suggest a simpler solution with the plv8 language. CREATE ...


2

Here are a few quick tips which can help improve your performance. I'll start with the easiest tip, which is almost effortless on your part, and move on to the more difficult tip after the first. 1. work_mem So, I see right off-hand that a sort reported in your explain plan Sort Method: external merge Disk: 5696kB is consuming less that 6 MB, but is ...


2

It sounds like the code might be using SAVEPOINTs to handle errors, and not releasing the savepoints before proceeding. That would explain the large number of virtual xid locks. RELEASE SAVEPOINT after you're done with a step. You might also want to consider batching the work into smaller chunks, as the: SAVEPOINT Try it ROLLBACK TO SAVEPOINT if it ...


2

I put in a little time to try and develop an answer for this question which may fit your needs, but since I don't have detailed criteria, it may not be perfect. Hopefully, though, it is close enough so that you can manipulate to meet your design needs. Initial assumptions To begin, I had to make a few initial assumptions to design the algorithm. 1) When ...


2

Here's a description of PostgreSQL's date handling I found on the pgsql mailing list from January 2012: As best I can tell, that document is talking about issues that are beyond Postgres' ken. When you tell us a timestamp value is '2012-01-30 21:13:28.097017-05', that's what we store --- whether you meant it to be in TAI, UTC, UT1, or whatever is ...


1

Try this form: EXECUTE 'insert into ' || child || ' values ($1.*)' USING NEW; It requires at least PostgreSQL 8.4, but previous versions ought to be retired nowadays. An even more modern and cleaner version (quote the table's name if necessary): EXECUTE format('insert into %I values ($1.*)', child) USING NEW;


1

According to the docs PL/pgSQL Under the Hood, you can use the configuration parameter plpgsql.variable_conflict, either before creating the function or in the start of the function definition, declaring how you want such conflicts to be resolved (the 3 possible values are error (the default), use_variable and use_column): CREATE OR REPLACE FUNCTION ...


1

The LIKE operator can't do this. No built-in function does what you are trying to do. Write a stored function, or make this transformation in the application code.


1

I just ran into the same issue on Postgres 8.3.11. Although I could not identify the root cause, the fix was simple enough: REINDEX INDEX tbl_cust_id_idx; This page contains hints as to what may have caused the error, although they are vague: An indicated table index was corrupted (may be a result of recent postgres or system failure, there was a lack ...


1

The best query very much depends on data distribution. You have many rows per date, that's been established. Since your case burns down to only 26 values in the result, all of these solutions will be blazingly fast as soon as the index is used. The partial index below will be a bit faster if you have many NULL values. For more distinct values it would get ...


1

JSONB may be easy to read, but it's complicated and inefficient to write into. See for example this question: PostgreSQL update and delete property from JSONB column, on how it looks like. It's an order of magnitude harder than an update/delete with classic EAV tables. Possibly when you'll have written the parts to append/merge/delete key/value pairs, the ...


1

It is difficult to explain your precise case without explain plans and engine profiling information. You may use pg_stat_statements module or PostgreSQL perf for tracing, but I am convinced your issue is the transaction size. It is known that the pattern of one transaction per row must be avoided for performance reason as commit overhead will slow down the ...


1

The sub-select: SELECT role_id from role WHERE role_name = 'tech' returns exactly one row, so your query can be transformed to: SELECT emp.name, role.role_name, emp.emp_id FROM emp JOIN role ON emp.role_id = 1 This can be transformed to: SELECT emp.name, role.role_name, emp.emp_id FROM emp CROSS JOIN role WHERE emp.role_id = 1 Therefor the ...



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