Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
3,430
questions with no upvoted or accepted answers
3
votes
1
answer
7k
views
Is there any way to pg_upgrade PostgreSQL 12 to 15 over network?
My situation is a little bit complicated.
I have an older version of PostgreSQL 12.9 installed on an older Centos Stream 8 machine. There are about five custom databases created under the roof, say ...
3
votes
0
answers
996
views
How to design a database schema for multiple user types?
Question:
I have a system where I can have multiple types of users: buyers, vendors, admins, etc. These types of users aren't identical, but they're not utterly unlike each other.
There will be ...
3
votes
1
answer
202
views
How to "merge" rows along with their foreign many-to-many relations without violating unique constraints?
Fiddle: https://dbfiddle.uk/-JLFuIrN
Table
CREATE TABLE files (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text
);
CREATE TABLE folders (
id bigint GENERATED ALWAYS AS IDENTITY ...
3
votes
0
answers
420
views
How to connect to a Postgres database cluster ( running on digital Ocean) from a linux virtual machine (running on digital Ocean)?
I have a postgres database cluster running on digital ocean, and another separate virtual machine also on the digital ocean. How can I connect to the database via CLI to the database I already have on ...
3
votes
0
answers
362
views
Full-text search index across columns and tables
I am building a document database and I would like to utilize the full-text search capabilities of PostgreSQL. The documents do not all follow a clean schema, so I decided to store some values in ...
3
votes
1
answer
3k
views
Error: SSL connection closed unexpectedly. Error while fetching large data using postgres FDW
I am using postgres 13 and created a foreign server with use_remote_estimate: on and fetch_size: 10000. tableA here is a partition table by created_date. The query is running fine if the number of ...
3
votes
0
answers
74
views
More low-level faster replication schemes for PostgreSQL?
From this question: in PostgreSQL REPMGR Master-Slave environment is it necessary to have same H/W config for HOT STANDBY setup?, I was reminded of my experience and disappointment with the hot-...
3
votes
1
answer
2k
views
How can I list all privileges that a PostgreSQL role has?
... including the inherited ones? When organizing access rights through different roles, this seems to be something really useful, yet I haven't found anything that would show how to do this.
AIUI, I ...
3
votes
2
answers
2k
views
Choosing the right database for stock price history
The model is [(stock_id, period, ts), open, high, low, close, last, volume]
We write new prices for all stocks (120,000) each minute and delete old once when they go out of retention time. It doesn't ...
3
votes
0
answers
594
views
How to map child (leaf-level) nodes to specific parents with/without ltree for aggregations?
What would be the best way to aggregate across hierarchies?
I am using Postgres and have the following 3 tables (folder, folder_hierarchies, and work) -
folder
Column
Type
Description
folder_id (pk)...
3
votes
2
answers
2k
views
Dealing with suboptimal postgres query plans related to nested loops
I'm developing an application that dynamically builds up SQL queries and executes them against postgres 13.
Some of the queries are very slow because they use nested-loops but the query planner ...
3
votes
0
answers
489
views
Improve performance of view based on TSDB continuous aggregation
I am currently trying to create a (materialized) view which is based on a TSDB (timescale DB) continuous aggregation. Some context:
I have a table which takes consumption data of meters through a ...
3
votes
0
answers
1k
views
Postgres - limit window function evaluation to once per partition
I have a table of daily stock quotes with ~40M rows and I'm trying to run a query that spits out the most recent date and the number of stocks that made a new one year high on that date. For example:
...
3
votes
1
answer
1k
views
List PostgreSQL extensions per database
I have a Postgres 9.5 server with 30 databases.
I would like to find out which database uses which extension(s) in one go, preferably in a query that is usable in pgAdmin.
I know that SELECT * FROM ...
3
votes
0
answers
717
views
How do I find out details about an error showed iby "brew services list"?
I want test my postgres13 service on mac by using "brew services list".
I got following:
$ brew services list
Name Status User Plist
postgresql error gerd /Users/gerd/...
3
votes
0
answers
1k
views
When is a lock acquired during a trigger's execution?
Following Situation
I'm trying to determine when a lock is acquired during the execution/firing of a trigger. I have the following setup.
Table Definition
CREATE TABLE a(
id serial PRIMARY KEY,
...
3
votes
0
answers
81
views
Postgis ST_Intersects behaves erratically
I am using Postgres 13.0 on a Mac, with Postgis 3.0. I encountered a problem where ST_Intersects produces the wrong result, and after narrowing it down, I arrived at the following:
With the following ...
3
votes
0
answers
172
views
Create a foreign-key-like constraints that checks something more generic than equality
There are some situations where I would like to set up some constraints in a table that depend on values in another table.
For example, I can define two tables as:
CREATE TABLE foo (
foo_id serial ...
3
votes
0
answers
1k
views
pgAdmin 4, force "The application has lost the database connection:"
Whenever I put my computer in sleep mode pgAdmin 4 loses the connection to the remote Postgresql database (and that's fine). When I start up the computer again I have two options. Either restart ...
3
votes
0
answers
345
views
Ordering of columns in a GIST index
I am modelling an application where events for factories can be registered. There are usually a small amount of factories (around 500), but each factory can register a large set of events (millions). ...
3
votes
0
answers
869
views
Why is PostgreSQL not using composite Index on JSONB Fields?
Two of a PostgreSQL DB table's columns are JSONB field (j1, j2). My queries are on (j1->>'f1')::int and (j2->>'f2'), for example:
SELECT id from table where (j1->>'f1')::int = 1 AND (...
3
votes
0
answers
319
views
Is there a reliable way to determine the order in which Postgres transactions were committed without logical decoding?
Is there a reliable way to determine the serial order in which Postgres transactions were committed without logical decoding? From what I understand pg_xact_commit_timestamp can’t necessarily be ...
3
votes
0
answers
206
views
data-modifying CTEs and triggers
If a data-modifying CTE inserts into two tables t1 and t2 that both have triggers, is there any guarantee on the order in which statement-level triggers are run?
-- t1 and t2 both have insert ...
3
votes
1
answer
131
views
generating all substrings (n-grams) for bit-strings
I followed SELECT all substrings (n-grams) of length n? to get a function for generating all n-grams for text types. Works great. I figured out how to cast my bit-strings to text, and the function ...
3
votes
0
answers
731
views
How to monitor query load, counts, and durations in PostgreSQL
I'd like to understand load (average active), count, and, average time of various queries.
This is an AWS RDS Performance Insights dashboard. This is the sort of information that I want, though as ...
3
votes
0
answers
2k
views
Fast long-to-wide reshaping in PostgreSQL
I am trying to change a really long table (1955451890 rows -- 278 Gb on the DB) to a wide format using crosstab from the tablefunc extension. Following several examples from SO (i.e. Crosstab with ...
3
votes
2
answers
1k
views
macOS Catalina - postgreSQL - sysctl.conf still relevant?
I had the following settings in /etc/sysctl.conf before Catalina:
# PostgreSQL Config for the MAC
kern.sysv.shmmax=2147483648
kern.sysv.shmall=524288
kern.sysv.shmmin=1
kern.sysv.shmmni=32
kern.sysv....
3
votes
1
answer
223
views
Is it possible to load data into Postgres using COPY, where the data includes a SELECT statement?
(I think the answer is probably "no", but I haven't managed to find anything where anyone tried to do this. Which maybe is a sign in itself).
So, I have a parent and child table than I'm generating ...
3
votes
0
answers
3k
views
How do I fix multixact wraparound after it has already occurred?
I have a database which has a single table for which MultiXact wraparound has already occurred. Neither manual vacuums nor automatic vacuums are able to fix this condition.
Manual vacuums fail almost ...
3
votes
0
answers
2k
views
Postgresql v10.1 - How to fix ERROR: duplicate key value violates unique constraint
I'm not sure how this happened to this table but have an issue with ERROR: duplicate key value violates unique constraint trying to use AWS's DMS service to move a postgres v10.1 DB to RDS v10
The ...
3
votes
0
answers
875
views
Creating a custom thesaurus dictionary on RDS
We're deployed on RDS using Postgres 11.4, and I'm hoping that there is some way to define a custom thesaurus dictionary. I think the answer is a hard no, based on this StackOverflow thread
and this ...
3
votes
0
answers
1k
views
How to reassign all privileges to another role in PostgreSQL?
On an existing PostgreSQL server let say that I have two users "Alice" & "Bob" and I want to clean up my policy because they actually have the same job and should have exactly the same privileges ...
3
votes
0
answers
2k
views
Grant Privileges on Production Database Throws "Error: tuple concurrently updated"
I would like to grant read access to all tables in my schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO XXX;
While it works on our alpha environment, it fails with our production database:
[...
3
votes
0
answers
627
views
How to optimize this recursive postgresql query?
I've been searching a lot on this topic but I'm stuck at optimizing a query.
I've created a complete dbfiddle to see the problem, if you increase the amount of inserts for the comments table you'll ...
3
votes
0
answers
3k
views
Storing GTIN/EAN values with PostgreSQL. Which data type to use?
I am building a product database and I need to store GTIN/EAN (Global Trade Item Number/European Article Number) values. Probably except GTIN-14 as it does not seem to be used at retail sales? I read ...
3
votes
0
answers
808
views
Postgres configuration for high-latency storage
I'm trying to setup a postgres server using Ceph as storage. After taking benchmarks, it was obvious that even though the storage has a really good throughput, the higher latency brought by network ...
3
votes
0
answers
1k
views
Do hstore and jsonb have the same plumbing internally?
Is hstore internally implemented identically as jsonb, just with a constraint that the data must be flat? If not, when/why should hstore be used instead of jsonb?
3
votes
0
answers
83
views
How do I prevent PostgresQL users/roles from having access to any administrator functions?
When I create a user with CREATE ROLE user123 LOGIN on my system it is able to login and immediately perform \du and interact with pg_stat_activity, and probably many other things.
I have ...
3
votes
0
answers
329
views
TCP Resets when using psycopg2
I'm trying to do a simple health check for keepalived and other services via a python script and psycopg2 library. All seems to be alright, until I close the connection, at which point a packet with ...
3
votes
0
answers
85
views
Update column without dropping dependancies
I have a table with many dependancies such as: functions, views and materialised views. I'd like to update a column from an int to a bigint.
The issue I have is that doing so requires dropping a ...
3
votes
1
answer
73
views
Randomly sort all non-ordered rows
Is there a way to get PostgreSQL to randomly order query results that haven't been ordered by an ORDER BY? I think this would be a useful way to seek out bugs caused by an implicit reliance on the ...
3
votes
0
answers
9k
views
Steps to unlock a locked select in postgres
I have tried to update a table in Postgresql 9.6 (AWS) based in two inner joins.
Since it was taking too long, I cancelled it.
Now I'm trying to run a query to count records from the inner join but ...
3
votes
0
answers
7k
views
Huge speed difference between left join and inner join - Postgres
It is known that left join is generally slower than inner join, but this difference seems out of proportion.
I have table A with 2,542,526 rows and table B with 30,444 rows.
I ran this query, which ...
3
votes
0
answers
1k
views
AWS Aurora Database Corruption
I'm running Aurora Postgres and noticed that metric Maximum Used Transaction IDs is increasing a lot. Trying to manually vacuum the tables that's was not automatically vacuumed lead me to the ...
3
votes
0
answers
498
views
Postgres Replication out of sync
We have master server on one Datacenter, and the Replicate is in another Datacenter
Intermittently, on the replicate server, we see this as below.
“2018-12-19 07:24:25 UTC: : @: [25775]: [2-1] ...
3
votes
0
answers
89
views
Poor query planning in Postgres 10
The query planner in Postgres10 seems to be ignoring the fact that my tables are clustered. I have to explicitly put "hints" in my query to have it perform the correct type of join.
Here's my ...
3
votes
0
answers
2k
views
PostgreSQL 10.3 pg_dumpall fails: server closed the connection unexpectedly
System: Windows 10 Pro 64bit, Version 10.0.17134 Build 17134. with Postgres 10.3 db server.
I backup my postgres db using the following statements:
"C:\PostgreSQL\pg10\bin\pg_dumpall.exe" -U ...
3
votes
0
answers
6k
views
change column type from integer to varchar
I have an integer column size in my table product in PostgreSQL 10.5 database. I want to change it's type from int to varchar(20) using alter table if possible.
The obvious way which worked for me ...
3
votes
0
answers
3k
views
ERROR: invalid input syntax for type oid: "english"
While creating an index in Postgres I am getting an error:
invalid input syntax for type oid: "english"
LINE 1: ...s ON entity_attributes2 USING gist ( (to_tsvector('english',...
The query ...
3
votes
1
answer
1k
views
How can I make full text search on larger text rows more efficient?
The problem is I tried the LIKE and ILIKE and it was faster then pg_trgm and the postgres full text search, because they often run in a timeout or throw an error:
NOTICE: word is too long to be ...