Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
16,378
questions
2
votes
1
answer
26
views
PostgreSQL: Update multiple data while using the binding variable
I create the new table and insert 10 records into the new table as below:
userdb=> CREATE TABLE test_tbl(ID INT PRIMARY KEY, STR VARCHAR);
CREATE TABLE
userdb=> do $$
begin
for r in 1..10 loop
...
- 23
1
vote
2
answers
39
views
Unable to drop user due to assigned privileges?
How do you drop a user with read-only permission to PostgreSQL?
I created a user, with read-only access to all tables, on an RDS Postgres database with:
CREATE ROLE myuser WITH LOGIN PASSWORD '...
- 1,284
0
votes
1
answer
20
views
Is there any possibility to delete all data directory of PostgreSQL by PgPool?
Recently I found that suddenly all data directories were deleted both primary and standby. I am assuming that it happens due to failover.
Example: Suppose there are 4 nodes where 1 is primary and ...
0
votes
1
answer
30
views
Why pg_wal directory holding 16MB WAL file of last 24hr in PostgreSQL13?
In my PostgreSQL pg_wal directory total wal segments sum about 200GB where max_wal_size 8GB.
We archived WAL file at desired location using archive_command but wal file not removed from pg_wal ...
0
votes
1
answer
17
views
How to add information about two same named databases on different hosts in PgBouncer config file?
I want to add details for databases in a config file of pgbouncer.
I have two same database names but their hosts are different, as there is no information about server endpoints in the config file, ...
0
votes
0
answers
21
views
Cleanup Logical Replication slot Postgres
I have a logical slot in my Postgres (version 13). My consumer was down for some time and the slot size shot up to 100GB. I don't care about the logs generated during the downtime. Basically the wal ...
- 101
0
votes
0
answers
16
views
How can i search Mamtakumari by passing regex on to_tsquery(ku) in full text search implimention of postgressql
I want to use full text search functionality over postgres where when I'm using to_tsquery('ku:*') then it's showing result like Kumar, Kusum etc. but not like Mamtakumari and my requirement is to get ...
2
votes
1
answer
49
views
pg_cron: "policy ... for table ... already exists" error while restore postgres database from dump
I'm trying to restore postgresql database from dump on fresh new server. Source database uses pgcron extension, target server have pgcron extension installed.
This is how I create dumps on "...
- 123
0
votes
1
answer
23
views
PostgreSQL - WALs are piling up after promoting the standby
I have one primary and one standby Postgres database nodes set up for streaming replication. Last month I promoted my standby, and swapped places with the primary. That is, I edited my postgresql.conf ...
- 1
0
votes
0
answers
14
views
Enable syncing DB dump between tenants in a multi-tenant Postgres database
Our Java application stores data pertaining to multiple tenants in one single DB instance on AWS RDS. This setup is replicated across regions in prod. There are multiple schemas and each table within ...
- 101
0
votes
1
answer
15
views
postgresql RLS enablement : enable if not enabled check - worth it?
In our application, we check at startup,
if a postgresql table exists
if it doesn't, we create it, and then enable row level security
If it does exist, we still aren't certain if row level security is ...
- 249
0
votes
0
answers
16
views
Data validation for RDS PostgreSQL migration
I'm planning to replace a bunch of RDS PostgreSQL databases using this method defined by AWS.
The DMS task will run will be CDC-only. I came up with data validation requirements for the migration and ...
0
votes
0
answers
40
views
pg_cron jobs are not being executed
We have installed pg_cron extension on PostgreSQL 13 database.
postgresql.conf file:
shared_preload_libraries = 'pg_cron'
cron.database_name = 'pg_cron_db_name'
Also we enabled trust authentication ...
1
vote
0
answers
26
views
What’s the penalty if I enable `track_commit_timestamp`?
I just learned about PostgreSQL’s xmin system column, pg_xact_commit_timestamp(xid) function and track_commit_timestamp option.
I’m considering enabling this option on my PostgreSQL databases, as a ...
- 193
-2
votes
0
answers
15
views
After downloading and installing PostgresSQL. When I try opening it, it shows 'entry point not found‘ how can I resolve this?
I downloaded and installed postgresql version 12 software in my system (4Gb and 64 bits) but whenever I try opening the software, it shows 'The procedure entry point DiscardVirtualMemory could not be ...
-1
votes
0
answers
27
views
invalid memory alloc request size at postgresql pg_dump
Unable to backup database in PostgreSQL because of data corruption. I found it is only one table, rest of the tables found ok. Most case I found this error happen is at the large table.
pg_dump: error:...
1
vote
2
answers
51
views
What is acceptable level of BTree index fragmentation in Postgres?
I wonder at which point it makes sense to rebuild BTree index on relatively large tables (couple hundred GB).
To me anything above 30% or 40% appears to be a good candidate, but I saw some say even 70%...
- 8,965
1
vote
1
answer
36
views
Create an index on a very large table to filter on date, or date + id
I am creating a table where I will have a large volume of data and the search will always be based on a date, or a date + an ID. I want to create an index to support the queries, but I have no idea ...
- 123
1
vote
1
answer
22
views
Merging wifi session data if time between them is less than 15 minutes
I am trying to process network logs and join sessions together if the time between them is less than 15 minutes. The relevant fields are start time, end time, mac address, and wifi access point.
I am ...
- 13
-1
votes
2
answers
25
views
PostgreSQL: Issue Declaring Variables in Query
I'm trying to declare a variable for later use in a PostgreSQL query. I've done this often in TSQL, but I wasn't sure about the syntax.
The stuff I've seen online all points to something like this:
...
- 3
2
votes
0
answers
34
views
Strange behaviour when `RAISE LOG ...` is executed with interpolated value
Recently had a script that failed on Postgres v13 ran but successfully ran on v14.
I believe the behaviour in v14 to be strange and potentially a bug, but wanted to check here before submitting a bug ...
- 21
0
votes
1
answer
23
views
Optimize import data from one table into another
Edited on mustaccio request.
I have simple table:
-- DROP TABLE IF EXISTS public.objects;
CREATE TABLE IF NOT EXISTS public.objects
(
id integer NOT NULL DEFAULT nextval('objects_id_seq'::...
- 1
0
votes
1
answer
41
views
PostgreSQL Processor Queue Length
Is there a similar metric to MSSQL's Processor Queue Length in PostgreSQL? I had a situation recently where a system ran several concurrent long-running queries that essentially blocked all other ...
- 105
0
votes
0
answers
32
views
On select specific single row showing different result in PostgreSQL
Selecting a specific single row showing different result in PostgreSQL which is very strange. There is a table named Employee. When I search like " SELECT UserId FROM Employee WHERE UserId='3123'&...
0
votes
2
answers
48
views
Performance difference using CTE and subquery
I have run the following queries.
CTE
WITH temp AS (SELECT id, timestamp FROM table group by timestamp, id)
SELECT COUNT(*) FROM temp WHERE timestamp = '2023-01-01 08:28:45'
Subquery
SELECT COUNT(*) ...
- 111
0
votes
1
answer
26
views
PostgreSQL - Locking a Table That Is Almost Never Changed
I'm a software developer and I'm trying to implement a job board website for my own experience and learning.
One of the entities of this database is industry. And companies can be associated with ...
- 3
0
votes
1
answer
19
views
Join 1 row out of multiple 1:1 tables and return as array of json objects
I have a database structure like so (in Postgres):
An entity has a list of actions associated with it. Each Action has one associated row out of all action-specific tables. Each of these action types ...
- 1
-2
votes
0
answers
32
views
File processing in sql server/postgres
good morning guys,
I have a new project that works with processing files in edi format, all with layouts (txt) or by spreadsheets (csv/excel).
Today we received three files, one with registration data ...
- 1
0
votes
0
answers
44
views
TimeZone Setting On PostgreSQL Databases
I have a Patroni cluster with two Ubuntu 22 db server. The timezone of my instances was set to UTC. I have needed to edit them to set the local timezone to Europe/Istanbul. I have made the timezone ...
user274715
0
votes
1
answer
16
views
Recommendation on storing 3D flight data in PostgreSQL. Use POINTZM?
I am looking to store latitude, longitude (both decimal) and altitude (meters) for use with flight data in PostgreSQL 15.
I have decided on using the POINT data type in PostgreSQL for latitude and ...
- 439
0
votes
1
answer
32
views
How to convert numeric to Polish currency
In PostgreSQL I would like to achieve something similar to mysql query:
select price, format(price,2,'pl_PL') as pricePL from table
*************************** 1. row ***************************
...
- 1
-1
votes
1
answer
29
views
What are existing tables on a new PostegreSQL 15.1 installation?
I've just installed PostegreSql 15.1
Previous I installed and uninstalled it several times
After this last installation, if I try via PgAdmin to see "default" PostegreSql tables, the list is ...
0
votes
1
answer
20
views
How to find missing number from different ranges
I have a table with two columns, one containing name and other numbers. For each name there is range of numbers and there are around 250 names in there. Range of every name is different. It may be 1 ...
0
votes
1
answer
25
views
Create named PK when create table
I'm creating a table in Postgresql-11, and I need create a primary key for it at same time, to prevent from duplicated data being inserted before the PK created, so I want to these two operations be ...
- 285
-2
votes
2
answers
84
views
Which databases implement a difference between index scan and index seek?
Let's say I have a table:
CREATE TABLE transactions (
id NUMERIC(20, 0) PRIMARY_KEY,
account_id VARCHAR(512),
...
transaction_date TIMESTAMP
);
So with SQL Server, an index:
CREATE ...
- 169
0
votes
1
answer
52
views
PostgreSQL Query Performance: Sorting Array Length vs. CTE
I'm having a significant performance difference between two PostgreSQL queries that I'm trying to understand and optimize. I have a table with around 2TB of data, and both queries use the sequential ...
- 213
1
vote
0
answers
20
views
Postgres hot standby WAL history file mssing
I'm trying to create a hot standby. I've replicated the primary with pg_basebackup and configured recovery on the standby however the logs repeatedly show:
cp: cannot stat '/var/lib/postgresql/15/...
- 23
2
votes
0
answers
52
views
Efficient Partitioning Strategies for PostgreSQL Table with KSUID and High Volume
I'm working with a PostgreSQL table containing terabytes of data, and it grows by millions of rows weekly. Each row is identified by a KSUID, and my primary read patterns are:
Retrieve a row by its ...
- 21
0
votes
2
answers
43
views
How to delete old records while keeping certain history
I have a table that has a, b, and timestamp fields.
Table is getting very long, and I would like to remove records older than a year, with some exceptions - I need to keep:
at least 4 latest records ...
- 385
1
vote
1
answer
41
views
How to handle deadlocks while multiple batch inserts?
I need suggestions/ideas of handling a issue with deadlocks.
I'm having multiple batch inserts commands to same table:
insert into ip (id, p_id, m_type, m_id, sha256, path) VALUES ($1, $2, $3, $4, $5, ...
- 111
1
vote
0
answers
23
views
ST_SnaptoGrid not working properly
I want to perform ST_SnaptoGrid on a line but the result is very strange. I tested this line of code on 3 separate postgis sessions and the results of all of them was the same:
SELECT ST_AsText(...
- 13
3
votes
1
answer
92
views
PostgreSQL Query Planner overestimating Row Count leads to slow Hash Join
I have a table with two columns and about 10M rows. About half the records are NULL in the first field, and about half the records are NULL in the second field, although there are a few hundred ...
- 31
0
votes
1
answer
38
views
Connecting to AWS RDS succeeds via terminal but fails in pgAdmin 4. Why?
I can establish an SSH tunnel using ssh -L 2222:<RDS-Endpoint>:5432 ec2-user@<EC2-IP> -i ~/.ssh/key.pem and then connect to my RDS using psql "host=localhost port=2222 user=postgres ...
- 103
0
votes
1
answer
28
views
How can I avoid writing a loop when allocating orders to a stock impacted by the orders?
I have three tables in my database representing product storage, order items, and shipments:
CREATE TABLE storage (
product_id INT PRIMARY KEY,
storage1 INT,
storage2 INT
);
CREATE TABLE ...
- 113
1
vote
0
answers
19
views
Writing .spec files for PostgreSQL isolation tests
I am developing a PostgreSQL extension for which I would like to run some tests with concurrent connections. If I understand the documentation correctly, I should be able to do so using the PGXS ...
- 624
1
vote
1
answer
52
views
creating several records from one, based on a numerical value stored in a field of the source record
In a PostgreSQL db, I have a table with a numerical field, among others
In such field, some natural numbers are stored and they represent the number of occurrences of something
like this, roughly
...
- 113
0
votes
0
answers
43
views
connect to mssql with pgloader
I'm trying to convert some data from MS SQL Server to Postgres from a Windows 10 WSL partition.
From the Ubuntu prompt on WSL, I can run the following with no issue:
~$ sqlcmd -C -S tcp:localhost,1432 ...
- 229
0
votes
1
answer
26
views
What is the proper isolation level needed in this postgres query with concurrent transaction?
I have 2 concurrent transactions
on table X with the following column
id
count
and
table Y with the following column
id
somefield
currently there is only 1 row in tableX
id=1
count=0
and a million ...
- 101
-4
votes
1
answer
37
views
Find people absent from the country for a certain time
I have a table that has the "document number" field, the type of flow (entering or leaving the country), date of birth, and the date the flow occurred.
Knowing that a person can leave or ...
- 1
0
votes
0
answers
24
views
How to filter extraneous Unicode values from a column?
I am cleaning up / priming our PostgreSQL 12 database for future data-related activities (e.g. data encryption). I have tried the following methods to delete non-basic Latin / basic accented Latin / ...
