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
Filter by
Sorted by
Tagged with
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 ...
cidy.long's user avatar
  • 133
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 ...
the thinker's user avatar
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 ...
Biller Builder's user avatar
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 ...
SL4566's user avatar
  • 41
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 ...
ennui's user avatar
  • 81
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 ...
Sushma Yadav's user avatar
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-...
Gunther Schadow's user avatar
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 ...
Gerhard's user avatar
  • 131
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 ...
Kaplan Ilya's user avatar
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)...
GopherGopher's user avatar
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 ...
valenterry's user avatar
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 ...
Streamline's user avatar
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: ...
MattL922's user avatar
  • 131
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 ...
copossum's user avatar
  • 131
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/...
Gerd's user avatar
  • 143
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, ...
Mendes's user avatar
  • 81
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 ...
Jeroen's user avatar
  • 31
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 ...
user2233709's user avatar
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 ...
grofte's user avatar
  • 131
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). ...
ItIsJustMe's user avatar
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 (...
Sleepster's user avatar
  • 131
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 ...
Jordan's user avatar
  • 141
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 ...
Chris Chudzicki's user avatar
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 ...
Marcin's user avatar
  • 131
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 ...
Paul Draper's user avatar
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 ...
topcat's user avatar
  • 187
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....
Crashmeister's user avatar
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 ...
khafka's user avatar
  • 79
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 ...
Freiheit's user avatar
  • 259
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 ...
Mike's user avatar
  • 175
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 ...
Morris de Oryx's user avatar
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 ...
MarHoff's user avatar
  • 223
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: [...
linqu's user avatar
  • 183
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 ...
Elger Mensonides's user avatar
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 ...
yurtesen's user avatar
  • 237
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 ...
Mohammadreza MontazeriShatoori's user avatar
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?
davidtgq's user avatar
  • 709
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 ...
eadsjr's user avatar
  • 153
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 ...
JPm's user avatar
  • 31
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 ...
Royal Wares's user avatar
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 ...
ldrg's user avatar
  • 679
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 ...
nariver1's user avatar
  • 131
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 ...
A.T.'s user avatar
  • 131
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 ...
regisxp's user avatar
  • 131
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] ...
Manideep's user avatar
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 ...
offbynull's user avatar
  • 131
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 ...
Michael's user avatar
  • 295
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 ...
Eir Nym's user avatar
  • 131
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 ...
Piyush Sharma's user avatar
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 ...
Spedo De La Rossa's user avatar

1 2
3
4 5
69