Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
13,432
questions
0
votes
1answer
19 views
One-row postgres query as CTE/subquery much slower when passing subquery field into function / maybe related to inlining?
I'm using postgres 13.3 with inner and outer queries that both only produce a single row (just some stats about row counts).
I can't figure out why Query2 below is so much slower than Query1 (they ...
4
votes
1answer
55 views
Ordering rows by repeating pattern
Given a table in Postgres like
CREATE TABLE t1 (
type text NOT NULL,
value int NOT NULL
)
where type could be A, B or C and given an arbitrary, repeating pattern like
A B B C B B
how can I query ...
0
votes
0answers
26 views
Can't create a Postgres extension for pgaudit
I compiled pgaudit on Windows10 using Visual Studio. I added shared_preload_libraries = 'pgaudit' and restarted the server.
When I try to add the extension, I get:
ERROR: could not find function &...
0
votes
1answer
45 views
Using subquery in WHERE makes query extremely slow
I have this rather basic query that is very slow for reasons I can't figure out:
SELECT s.id
FROM segments s
WHERE
ST_DWithin(
s.geom::GEOGRAPHY,
ST_Envelope((SELECT ST_COLLECT(s2....
2
votes
1answer
53 views
Disabling triggers makes DELETE fast, but the table has no triggers?
I am deleting 130k out of 750k rows from a PostgreSQL table.
The first time, it took 8 hours to complete the delete query.
Second time, I added an index to the table, and rebuilt that index. Now it ...
0
votes
1answer
26 views
Look for a list of specific characters using RegEx
I'm trying to write a query (Postgres 8.2.15) which is checking whether the value in column1 consists of:
English alphabet only (A-Z), there should be no specific characters with umlaut/ablaut coming ...
0
votes
0answers
23 views
How to restore a standalone server from a pg_basebackup using the fetch option
I am attempting to make a backup & restore of a production Postgres server (9.6 Windows) for development testing. This server is standalone and does not need to do streaming replication.
I have ...
1
vote
0answers
19 views
Postgresql stale result for insert into select
I've noticed when two processes (let's call them A and B) insert a record into the same table in Postgresql and don't commit those changes, the 2nd (later process, B) blocks until the 1st process (A) ...
0
votes
0answers
24 views
Postgres locking issue
I have the following Postgres table:
CREATE TABLE my_db.url_data (
url varchar(2500) NOT NULL,
status int,
priority int,
...
last_update_ts timestamp,
CONSTRAINT url_data_pk ...
1
vote
1answer
24 views
Redshift - Insert data into a temp table with a condition being column doc_id should be unique and not null
I'm trying to create a temp table and insert data into it in Redshift.
My goal is to create a single record for unique doc_id WHERE doc_id IS NOT NULL.
Here's my code:
-- Creating temp table to load ...
0
votes
0answers
13 views
Why is this logical replication slot using so much space?
I have 3 RDS postgres instances. Each has a single database, and in each database I've created a logical replication slot:
SELECT pg_create_logical_replication_slot('test', 'wal2json');
Two of the ...
3
votes
0answers
49 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:
...
0
votes
1answer
26 views
PG_upgrade with Rsync 9.6 to 13 on Standby Server - Postgres password prompt issue
I am utilising the pg_upgrade documentation for 9.6 to 13. I can upgrade from 9.6 to 13 with the --link issue without issue on the master server. I have a slave machine which I want to upgrade, ...
0
votes
1answer
17 views
Can WAL files be read and modified at WAL_LEVEL = replica [closed]
There are multiple client DBs (one for each client) in our org. We have one backup server where all the client DBs are formed as schemas and stores the data to query for internal business purposes. ...
0
votes
1answer
15 views
PostgreSQL V13 Master-Slave Replication on Window Server 2016 Not Replicating
I have installed and configured PostgreSQL V13 on window server 2016 but replication is not working. I don't know where the error is because we are not facing any error regarding replication and ...
0
votes
0answers
13 views
Postgres jsonb field: remove an element from an array by the element id
Figure a Postgres 12.5 jsonb field containing the following:
[
{id:1, name: 'one'},
{id:2, name: 'two'},
{id:3, name: 'three'}
]
How to write the request that will match: "drop the object ...
0
votes
1answer
24 views
Measuring how long PostgreSQL waits for locks
I want to figure out how long PostgreSQL spends waiting for locks during a period of time (during this period of time, many requests are served by PostgreSQL).
What I know
PostgreSQL system table ...
0
votes
1answer
25 views
PostgreSQL + how to clean pg_xlog log without damage the PostgreSQL service
we are using postgresql for ambari DB , in our linux server - rhel 7.2
postgres -V postgres (PostgreSQL) 9.2.13
we noticed that pg_xlog folder consuming 198g , and its gives /var to get 100% size
so ...
0
votes
1answer
30 views
Almost 100% CPU Postgresql
When I restart Postgresql 11, the CPU shows basically no CPU usage. But after a while the CPU is occupied by "kdevtmpfsi". What is this and how can I fix this?
1275 postgres 99.8 % /...
0
votes
1answer
19 views
Check constraint to guarantee ltree ancestors
I ran into an issue where we had a tree structure that looked like this:
select title, tree_path from my_table where tree_path <@ '3';
title | tree_path
-----------------------
item A | 3
...
1
vote
1answer
44 views
Database is 300 GB, but I only see small tables?
I wonder why my database is so large. I type:
SELECT pg_size_pretty(pg_database_size('defaultdb'));
and it says defaultdb is 259 GB. That is huge.
So I list my tables in psql with:
\dt
For each ...
0
votes
1answer
19 views
How can I have multiple foreign keys in one table? [duplicate]
I have a setup where I have accounts and teams. One account can belong to multiple teams and one team can belong to multiple accounts.
How should I go about designing this in Postgres?
0
votes
1answer
16 views
Extract JSONB column into a separate table
I have a table with roughly the following columns:
CREATE TABLE records (
id integer NOT NULL,
...
metadata jsonb DEFAULT '{}'::jsonb
)
... includes the rest of the columns which have ...
0
votes
0answers
10 views
Postgres Switchover Failed - ERROR: requested starting point AF/7D000000 on timeline 1 is not in this server's history
My switchover to the standby replication was failed. So I have moved towards bringing it up. DBs are online but I see the log file like this:
2021-06-15 16:59:40.339 CEST > ERROR: requested ...
2
votes
1answer
16 views
Is vacuuming a PostgreSQL materialized view necessary?
I understand it makes sense to analyze a Materialized View (MV), but since MVs are not updateable, does that imply that we need not ever vacuum a MV?
1
vote
1answer
96 views
Adding a WHERE clause slows down query
We run a database on
PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by Debian clang version 10.0.1, 64-bit
We are trying to get the top rows of a result set where the most recent entry has a ...
0
votes
1answer
18 views
How to run postgres in-memory on windows and sync to FS when a temer runs out?
So Progress is slow for me (in terms of query and update times) on SSD and it is ok in my case: I use it as a mix of Graph database + Table processor + NoSQL JSON trees so lots of loops, graphs, trees,...
1
vote
1answer
35 views
Database architecture of high frequency sensor values
I have a lot of sensors (around 1000) and want to save their values. The sensors
will mostly work around a sampling rate of 10Hz.
Regular queries will be used for a live-view application. It can be ...
0
votes
0answers
12 views
Authenticating Postgresql users with Active Directory
Following this procedure, I've arrived at the step:
(if you do not have a ticket already, run: kinit sfrost@DOMAIN.LOCAL)
But when I do that...
$ hostname
blarg
$ kinit -V ron@EXAMPLE.COM
Using ...
0
votes
2answers
31 views
How to set user-based .psql_history in linux?
If use postgres in linux, it has a postgres user. So when use psql from this user, it will generate a .psql_history file under this user - /home/postgres/.psql_history.
When set many users on the ...
-2
votes
0answers
17 views
How to calculate difference between two days in hours in postgreSQL and return as ceil of days? [closed]
I want to calculate the difference between two dates in days such that for hours from 1-23 we consider it as a day. for example: Date1 = '2021-06-15 01:52:00.926+00' Date2 = '2021-06-15 02:52:00.926+...
3
votes
1answer
99 views
Suggestion for nearly gap-less sequences in postgres
In PostgreSQL sequences are designed to have gaps for reasons mentioned in this post https://stackoverflow.com/questions/9984196/postgresql-gapless-sequences. My question is: could sequences not be ...
0
votes
0answers
15 views
Pg_rewind Uitility - PostgreSQL Snapshot standby [closed]
We have Primary-secondary PostgreSQL Streaming replication.
I have done some testing.
Steps.
1.Promote secondary
2.Run some DML on secondary which is in Write mode(another new primary)
3.Down new ...
0
votes
1answer
26 views
Postgresql Physical Replication vs Logical Replication?
I have reading about difference between logical replication slots and phyiscal replication slots in Postgres. I am confused about some paramters and how they are used.
For a replication slot, we have ...
0
votes
1answer
18 views
What is difference between START WAL LOCATION, STOP WAL LOCATION, and CHECKPOINT indicated in the backup history file?
I am a bit confused about these locations. Could someone give a short definition for these locations.
0
votes
1answer
27 views
Why can't PostgreSQL identity columns be used in CREATE TYPE?
My impression is that each PostgreSQL table has a corresponding composite type associated with it. But it seems that the PostgreSQL identity column cannot be used in a CREATE TYPE statement:
=> ...
0
votes
1answer
23 views
Export Postgres sequences only
Is there a way using PG_DUMP or any other commands to export only sequences from an existing database?
0
votes
3answers
48 views
Create unique index on Postgres with any column combination
I have a companies table:
CREATE TABLE "companies" (
"id" bigserial,
"name" varchar(255) NOT NULL
PRIMARY KEY ("id")
)
I want companies to have parents and ...
1
vote
0answers
19 views
PostgreSQL does not start: FATAL: xlog flush request is not satisfied — flushed only to 0
I have a Docker PostgreSQL (TimescaleDB) developer instance with a local Linux volume mapping.
version: '3'
services:
dex-timeseriesdb:
image: timescale/timescaledb:latest-pg12
# https://...
0
votes
1answer
33 views
Same query shows significant differences in performance on different data [closed]
I'm having a problem with writing a Postgres query that really appreciates anyone's help. I have 3 tables related to each other as below:
Order:
id (pk)
updated_at (datetime)
owner (foreign key, to ...
0
votes
0answers
15 views
AWS-RDS database catalog is restricted
I use PostgreSQL 13.2 in AWS-RDS.
The database is up and working properly; I can connect with the user provided by Amazon.
We need to alter the catalog to enable the implicit cast of integer to ...
1
vote
1answer
38 views
Quickly fetch rows based on bits
I'm dealing with slow queries due to the sheer amount of possibilities. I'm looking for a better solution or help with the solution I already have in motion.
Background Context:
We have people in our ...
2
votes
2answers
116 views
Enforce that every company has at least one CEO
I've a requirement to ensure that every company has at least one CEO.
Example database model with requirements db<>fiddle.
Basically, a CEO can't be removed from a company without naming another ...
-2
votes
1answer
15 views
POSTGRESQL Query and insert based on results [closed]
In this case I'm trying to perform a SELECT based query followed by an insert of new records into the same table based on those results. For example a simple table:
Table "samples"
Fields:
...
-1
votes
0answers
26 views
Create Primary Key for Table [closed]
I am trying to identify a primary key but ended up having no idea. Can anyone please give me suggestions?
I have the following table:
racer_id | team_id | timestamp | races | time | typed | ...
0
votes
1answer
31 views
Create Postgres Index for OR query
This is the query I want to optimize:
SELECT *
FROM invited_member_events
WHERE (user_id = '422820341791064085'
OR inviter_user_id = '422820341791064085')
AND guild_id = '...
1
vote
1answer
38 views
Can PostgreSQL build an index without storing the source data?
In Elasticsearch we can configure an index to not store the source data. Is it possible to recreate this in PostgreSQL?
Today, we are building an application that encrypts all (sensitive) data before ...
0
votes
1answer
21 views
How to list all sequences by psql with postgres?
I tried \ds didn't get any relations data.
But I use SELECT * FROM information_schema.sequences; can get all the data.
Why doesn't \ds work?
0
votes
1answer
24 views
Actual used and free space in WAL segments
I am new to the PostgreSQL world. I have an experience in administrating Microsoft SQL Server and Oracle databases.
In SQL Server, we are able to find how much space is actually used in pre-allocated ...
2
votes
1answer
24 views
What are the possible meanings of NULL and zero for autovacuum_count, last_autovacuum, and autoanalyze_count in PostgreSQL?
I have a big table (let's call it big_table) in a PostgreSQL 12.7 database running on a Debian GNU/Linux server. The machine has 8 GB of RAM and 4 CPU cores, and mostly dedicated to this PostgreSQL ...
