17
votes
Accepted
PostgreSQL: How can I list the tables to which a sequence belongs?
The information is stored in pg_depend:
SELECT t.oid::regclass AS table_name,
a.attname AS column_name,
s.relname AS sequence_name
FROM pg_class AS t
JOIN pg_attribute AS a
ON a....
- 54.8k
15
votes
Accepted
How to set statement timeout per user?
Yes, Postgres allows settings per user or even per database and user, including statement_timeout:
ALTER ROLE foo SET statement_timeout = 12345; -- milliseconds
Related:
How does the search_path ...
- 180k
8
votes
Accepted
Postgresql numeric and decimal is automatically rounding off
It seems to be the default behaviour of SQL Workbench, have a look at this article:
How do I change the resolution or scale of decimal data type on SQL Workbench.
Quoted from the article:
...
- 23.8k
8
votes
Accepted
Why EXPLAIN doesn't show heap fetches for index scan
Index scans fetch from the heap for every single row. That is what makes it not be an index-only scan. It only makes sense to display the count for index-only scans, as that is the only case in ...
- 40.6k
8
votes
How to swap primary key between records in postgres
You could use a deferred constraint. For that you need to drop and re-create the primary key:
CREATE UNIQUE INDEX mytable_primkey ON mytable (id);
ALTER TABLE mytable DROP CONSTRAINT mytable_pkey;
...
- 54.8k
7
votes
ERROR: insufficient columns in PRIMARY KEY constraint definition
Yes, this is required. There is no concept of an index over all
partitions in PostgreSQL. The requirement of having the partition key
a subset of the primary key allows us to implement primary keys ...
- 8,592
6
votes
Accepted
PostgreSQL How to DEFAULT Partitioned Identity Column?
I don't know of a better solution in general. A few minor things, though:
pg_get_serial_sequence()
If you don't know the name of the parent's implicit sequence, use pg_get_serial_sequence().
SELECT ...
- 180k
6
votes
Accepted
PostgreSQL - PUBLICATION tries to copy the data which already exists in another database?
By default, CREATE SUBSCRIPTION will first copy all the existing data from the source tables. The idea is that you usually start with an empty destination database created with something like ...
- 54.8k
6
votes
Accepted
json_object_agg errors on null in field name
It sounds like you've found a bug.
You can report it at the bottom of their home page, and monitor the discussion of the bug on the pgsql-bugs mailing list, I don't expect them to change the way ...
- 3,583
6
votes
Accepted
Select previous value in row without using a subquery
As Akina has pointed out in comments LAG() function requires an ORDER BY clause to show the properly values.
Given the next example:
CREATE TABLE t (id int, bar int);
INSERT INTO t VALUES
(1, 7),
(...
- 23.8k
6
votes
Accepted
Database Size is Different after pg_restore (389 GB vs 229 GB)
Dump doesn’t account for dead tuples and only takes live tuples into account but dead tuples do account for space hence the space difference.
The reason is dump being a logical one it will only ...
- 186
6
votes
Accepted
Determine Nodes in Network with PostgreSQL
Approach 1:
At first sight, it seems that I could apply a basic solution because, according to your sample data, each single connection is included in another connection.
SELECT
e1.node,
e1....
- 23.8k
5
votes
Accepted
Do BRIN indexes support ENUM types?
Aren't enums already ordered?
No.
This commit, from 2014, implies that BRIN indexes should work for ENUM types.
That's actually not what that commit says. From the commit-message on the link you ...
- 64.1k
5
votes
Accepted
Returning multiple result sets from server-side procedures in Postgres 11
Did this feature indeed appear in the Postgres 11 release?
No. I think the blog post refers to the "Dynamic result sets from procedures" patch, which did not make it into the released version.
- 31.9k
5
votes
Accepted
Safe to use the same temporary table name in parallel transactons?
documentation means:
if you have permanent table TABLE_A
and you create a temporary table with the same name TABLE_A,
a permanent table will be not visible for your code.
But you could request ...
- 3,655
5
votes
Postgres Column Level Security
As the administrator or boss, you can create user-specific views which include only the columns you want that user to be able to view. Then grant the user access to their specific view, and not to the ...
- 40.6k
5
votes
Accepted
Does SELECT remove dead rows like VACUUM does?
From this post on /r/PostgreSQL to an answer by Laurenz Albe it seems that Heap Only Tuples (HOT) updates may be responsible. From the description of HOT updates in src/backend/access/heap/README.HOT
...
- 64.1k
5
votes
How to swap primary key between records in postgres
The only way that I know this is possible is with a deferred constraint.
You will need to drop the primary key,
alter table x drop constraint x_pkey;
and add it again as deferrable:
alter table x ...
- 9,354
5
votes
Accepted
creating new table partition with values already existing in the default partition
The non-hack would be to not get into this situation in the first place, by not using a default partition when you are not really sure that that is permanently what you want. When we fail to ...
- 40.6k
5
votes
Accepted
What is the correct way to make an exact copy of a database with pg_dump?
I'm not a PostgreSQL expert but I think their documentation is rather clear and I believe the command you're using is pretty much all you need (you might need to specify the password with a flag - not ...
- 38.6k
5
votes
PostgreSQL 11.9 Unable to restart the server with error -- pg_ctl: directory "/var/lib/postgresql/11/main" is not a database cluster directory
directory "/var/lib/postgresql/11/main" is not a database cluster
directory
It means that this directory does not contain a PG_VERSION file, or it's somehow unreadable.
This file contains ...
- 31.9k
4
votes
Accepted
Syntax error when using exception inside for loop
As stated above in the comment, what I was missing was another BEGIN enclosing the exception.
Updated code:
CREATE PROCEDURE run_all_scheduled_tasks()
LANGUAGE plpgsql
AS
$$
DECLARE
rec record;...
- 161
4
votes
Accepted
Index partitioned table to prevent Postgres from doing a sequential scan
Try adding the partition key explicitly (redundantly!). Like:
SELECT *
FROM entries
WHERE identifier = 'some_identifier_from_subtable_s'
AND lower(left(identifier, 1)) = 's' -- 1st letter of ...
- 180k
4
votes
Accepted
Fastest way to extract full table in Postgres
Your current idea of using a range query on the primary key is probably the best bet. I don't see how partial indexes could help with that. You would need a series of partial indexes which would in ...
- 40.6k
4
votes
Accepted
Get the max value from a slice of a JSON Array?
You can unnest json array:
Postgres WITH ORDINALITY:
When a function in the FROM clause is suffixed by WITH ORDINALITY, a bigint column is appended to the output which starts from 1 and increments ...
- 23.8k
4
votes
Accepted
JSONB Array of Strings (with GIN index) versus Split Rows (B-Tree Index)
The optimal DB design always depends on the complete picture.
Generally, there is hardly anything faster than a plain btree index for your simple query. Introducing json or jsonb or even a plain ...
- 180k
Only top scored, non community-wiki answers of a minimum length are eligible