Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

Filter by
Sorted by
Tagged with
0 votes
1 answer
14 views

Seeking Guidance on Storage Models for Frequently Executed SQL Statement

An application frequently executes the following prepared statement in SQL: SELECT AVG(StockUnits) FROM PRODUCT; I'm currently exploring the most suitable storage model for the database architecture. ...
cricket900's user avatar
2 votes
1 answer
116 views

Common Table Expression syntax

Considering a query with CTEs: WITH cte_0 AS ( SELECT SUM(col_0) FROM table_0 WHERE condition_00 AND condition_01 ), cte_1 AS ( SELECT SUM(...
jvkloc's user avatar
  • 123
0 votes
0 answers
11 views

update column in one table with coloumn from another table based on two matching values

I have a table primary_table with the following coloumns id, store_code, item_code, item_name and serial_no. I have another table, secondry_table with the coloumns id_2, store_code, item_code, ...
P Moothedath's user avatar
0 votes
1 answer
35 views

Subtract column value of given row from all other rows

I am trying to select agg_cost for a given id, and subtract that from all other rows. Then split the result into two columns: downstream for negative, upstream for positive results. I would like to ...
MJM's user avatar
  • 123
0 votes
0 answers
11 views

Alerting system relationships and STI in PostgreSQL

I'm no expert in DBMS and trying to build up an alerting system that can attach itself to many tables, so I figured asking the question would help my architecture. The thing that may confuse me a ...
Laurent's user avatar
  • 101
1 vote
2 answers
61 views

Sequence does skip numbers

I have a sequence like this (in PostgreSQL 16.0): CREATE SEQUENCE public.seq_1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 999999999 START 1 CACHE 1 CYCLE; and a table containing all ...
Luuk's user avatar
  • 881
1 vote
2 answers
37 views

Postgresql: Performance - SELECT with function in WHERE condition

On Postgrsql 13.5 - I have a SELECT statement with a stored procedure as condition on the PK column (id). This turns out to be very slow: select * from xyz where id=myfunction('static-parameter') -- ...
bboness's user avatar
  • 21
3 votes
1 answer
29 views

Why does SELECT FOR UPDATE not support aggregations (among others)?

I checked the PostgreSQL docs for https://www.postgresql.org/docs/current/sql-select.html and wondered why there are so many exceptions where FOR UPDATE is not supported, like GROUP BY HAVING WINDOW ...
Mahatma_Fatal_Error's user avatar
0 votes
3 answers
40 views

Modifying PostgreSQL Table for Handling Multiple Delivery Modes

I'm working on a project where I need to modify the PostgreSQL table structure to handle multiple delivery modes for an ordering system. I have a table named seller with columns like id, user_id, ...
Maine's user avatar
  • 1
0 votes
2 answers
45 views

How to check if time difference for TIMESTAMPTZ has exceeded 30 days

I have a column called inserted_at in my postgres(v15.3) db which of type TIMESTAMPTZ. This value when inserted will always be passed in UTC I want to retrieve all records for those, 30 days has ...
user89978's user avatar
0 votes
1 answer
41 views

Len (rec/tot) in WAL in PostgreSQL

I'm learning how WAL is working in postgresql, but I don't really understand how is the len(record) value obtained, and where I can find the record. For tests I've done such inserts: INSERT INTO ...
Ilya's user avatar
  • 1
0 votes
0 answers
22 views

Postgresql update column with window function

Take a look on example: https://www.db-fiddle.com/f/wN8A7pfuGos9KfLokFS3uy/0 It is possible to update payment_line_tab and paid column in efficient way? I would like to set paid column to value: 0 - ...
Robert's user avatar
  • 141
0 votes
1 answer
57 views

What type of queries are considered as SQL injection?

I was testing efficacy of a paid Database security solution which has the ability to detect and block SQLi attack. For the testing purpose I have tried the following query against a PostgreSQL ...
Basil Titus's user avatar
1 vote
0 answers
37 views

Restore but only missing data on the table

I have 2 database already restored on 2 different database on the same instance, call it geo and restore1 the schema is identical, the only difference is that geo database contain only records before &...
Kokizzu's user avatar
  • 1,363
1 vote
1 answer
25 views

Ensuring ascending order of committed event_id in PostgreSQL audit trigger system

I'm using the logged_action audit system in PostgreSQL, and I'm encountering a problem where the event_ids are not guaranteed to be committed to the database in ascending order. This is causing my ...
Gergely's user avatar
  • 11
0 votes
1 answer
27 views

Possible options for SQL and NoSQL combination for social media data

I have searched this forum for an answer but could not find anything relevant. We are designing database schemas for a social media app with posts, groups, users and chats etc and it looks like we ...
dksr's user avatar
  • 101
0 votes
0 answers
18 views

Restoring partitioned table data causes error in PostgreSQL

I have partitioned tables and dumped it with pg_dump pg_dump \ --dbname "${DBNAME}" \ --table 'log_*' \ --format "directory" \ --file ./backup \ --jobs "${...
xvnm's user avatar
  • 101
0 votes
0 answers
27 views

Storing All Properties in a JSONB Column vs. Creating Separate Tables – Is This a Good Approach?

Seeking advice for optimizing the handling of analytics events in my .NET application using PostgreSQL. Currently, I store event properties in a JSONB column (EventProperties) within the Events table. ...
Architrixs's user avatar
0 votes
1 answer
13 views

How do I find missing sub-partitions of an index to make main index complete?

I have a table with many partitions that I wanted to add a new index to. Following the instructions from Postgres I created the new index on the main table using ON ONLY and then proceeded to create ...
Kenny Loveall's user avatar
0 votes
0 answers
19 views

Which query reproduces \dconfig+ command output in postgresql? [duplicate]

Which query reproduce the exact output of \dconfig+? (List of non-default configuration parameters) I've tried SELECT name, setting, source, sourcefile,pending_restart FROM pg_settings WHERE source !=...
Potter's user avatar
  • 630
1 vote
1 answer
43 views

Postgres sorting order not respecting default C collation?

I have a postgres DB running in an AL2023 docker image: postgres=# select version(); select version(); version ...
Richard Wheeldon's user avatar
2 votes
2 answers
146 views

Understanding the Impact of ORDER BY in Window Functions on other Partitions in PostgreSQL

I have a simple table odd_nums with columns num and odd: create table odd_nums ( num numeric, odd numeric ); INSERT INTO odd_nums VALUES (0, 0); INSERT INTO odd_nums VALUES (1, 1); INSERT ...
Leonard's user avatar
  • 23
2 votes
1 answer
10 views

Using pg_repack to remove OIDs from tables in PostgreSQL

I'm prepping a legacy PostgreSQL 9.5 database for upgrade. I need to remove OIDs from tables without locking the tables for long periods of time. I have developed a strategy that seems to work, but I ...
user2458080's user avatar
0 votes
2 answers
39 views

PostgreSQL's unaccent function is unable to remove accents (diacritic signs) from Japanese characters like 'ド'

The postgresql out of box unaccent function is unable to remove accents (diacritic signs) with more then one diacritic in a character. The character 'ド' which is failing for normalisation have 2 ...
shailesh Totale's user avatar
0 votes
1 answer
11 views

High Availability postgres db cluster with Patroni and Etcd

I am building a HA Postgres db cluster with one master and multiple slaves. Please look at the below points and correct me if I am wrong: For each slave node, I need to use pg_basebackup to sync and ...
Abizer Susnerwala's user avatar
1 vote
1 answer
41 views

Aggregate SCD Type 2 data "as of" each day

Problem When working with SCD type 2 data it's easy to see the state of a table "as of" a given point in time by using your date columns, eg: valid_from and valid_to. For example: select * ...
camtech's user avatar
  • 133
0 votes
2 answers
39 views

PostgreSQL: Pgbouncer authentication

We are using authentication file mode in pgbouncer. The authentication part of Pgbouncer is so confusing. Would be helpful if anybody can throw some light on my below queries. Are Pgbouncer ...
Basil Titus's user avatar
0 votes
0 answers
44 views

Best way to bulk create with foreign key

This might have been asked but I have not found a sufficient satisfactory answer yet. Use case is that users can create a (temporary) network on a map via clicks and then press save to finish creating....
sebastian.do's user avatar
0 votes
1 answer
62 views

PostgreSQL : is max_worker_processes cpu bound?

I have seen a lot of PostgreSQL performance tuning blogs and companies like EDB suggesting putting max_worker_processes setting for PostgreSQL to the number of CPUs in the machine. We are using a ...
Basil Titus's user avatar
0 votes
2 answers
59 views

Index optimization for generic UI sorting and filtering [duplicate]

We have a table that may contain 1M-5M or more rows. It has an assortment of string, numeric and date columns. The UI presents a table view, with "infinite scroll" paging, and various ...
e.dan's user avatar
  • 101
1 vote
0 answers
47 views

Continious aggregates (keeping aggregated data in a fresh state) for PostgreSQL

I build a PostgreSQL database for movie db platform. This DB is heavy loaded for readings of many related data. Here is simplified schema what I have: create table items ( id ...
Dmitry K.'s user avatar
  • 143
0 votes
0 answers
9 views

Postgresql in Colab using the wrong link

So I am trying to set up Django and PostgreSQL in Google Colab but I ran into an error when trying to test postgres. I created the user, password and the database and it says that my db called "...
Ran123's user avatar
  • 11
3 votes
1 answer
61 views

Creating VIEW using EXECUTE ignores USING clause parameter?

I am trying to create a UDF that creates a VIEW dynamically using the USING clause feature. The problem is, EXECUTE will not accept the passed parameter into the CREATE VIEW command. CREATE OR replace ...
Zaki's user avatar
  • 33
1 vote
1 answer
51 views

Shared buffers, WAL buffers and Checkpointers

I am taking this EDB PostgreSQL essential course and Instructor explained about PostgreSQL architecture referring to the diagram that, whenever a client make an update request and suppose data is ...
commonSense's user avatar
0 votes
0 answers
40 views

How to create a schema for a combination of values that needs to be unique

How would I create a schema for the following data: Dimension A Attribute X, Attribute Y positive , positive => Description 1 positive , negative => Description 2 negative , ...
Filipe Giusti's user avatar
1 vote
1 answer
55 views

potgresql uses only one worker for index creation

To speed up the CREATE INDEX operation I increased the work_mem and set max_parallel_maintenance_workers to 8 and it worked as expected when I was creating a B-tree index on the table. I saw 7 ...
lian's user avatar
  • 113
1 vote
1 answer
35 views

postgresql: why owner of table is unable to select from it?

In postgresql documentation on GRANT option it is written: There is no need to grant privileges to the owner of an object (usually the user that created it), as the owner has all privileges by ...
pva's user avatar
  • 113
0 votes
1 answer
48 views

PostgreSQL 15: ERROR: operator is not unique: unknown || double precision

we are using a PostgreSQL version 15 and have added 13 conversions from this blog entry: http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html However, only now we ...
Athris's user avatar
  • 11
0 votes
1 answer
41 views

How to query username, its roles and its roles privileges?

I have tried to query the user, the roles that it has and the privileges that the roles that it has have. I have managed to query the user and roles that it has with the following select: SELECT ...
Diogo dos Santos's user avatar
-1 votes
1 answer
44 views

Replacing substrings in array elements

I have a table with an array of URLs in column urls. Now there's a need to update some URLs changing the hostname. E.g.: {https://storage.host1.com/file.jpg, https://storage.host1.com/file2.jpg} ...
minicooper's user avatar
0 votes
0 answers
30 views

Remove server from pgAdmin4 server list

In pgAdmin4, I'm trying to delete "PostgreSQL 15" from my server list since I installed V16 and removed all V15 files. (Windows 11, PostgreSQL V16, pgAdmin v7.8) I can "Remove" the ...
Rick's user avatar
  • 1
1 vote
1 answer
22 views

AWS RDS PostgresSQL 14.7 sum(pg_column_size(t.*)) is way greater than pg_relation_size(t)

I am on a mission to optimize our DB and aiming to move out some text columns out from one of our high traffic tables. I tried to understand which columns are stored in the TOAST table as chunks. For ...
Ahmet İLHAN's user avatar
-1 votes
1 answer
31 views

Full text search in poatgres using tsvector and tsquery [closed]

I have a query like below Select * from abc where (to_tsvector('english', firstname) @@ to_tsquery('english', 'a:*')); is not working But same query aa is working Select * from abc where (...
Amar's user avatar
  • 7
0 votes
1 answer
25 views

What's the difference between autovacuum general setting and autovacuum_enabled table setting in PostgreSQL?

We have a database that is now on v15 PostgreSQL, but was recently migrated from v11, and before that v9. I believe that many of the settings for our database were preserved from prior versions and ...
Brian D's user avatar
  • 111
-1 votes
0 answers
24 views

Generate DELETE statement for multiple related tables

I am currently using PostgreSQL and I have a database with multiple tables that are related by foreign keys. I need to remove something from the parent table, but I have a lot of dependent child ...
Kaiser's user avatar
  • 99
2 votes
1 answer
67 views

Enforce unique constraint without denormalization

Option 1 I have a database schema that looks something like this: https://dbfiddle.uk/JKCTjadD A Task belongs to a Service (like in ECS) A Task can be reassigned to another Service. TaskConfig tracks ...
riwu's user avatar
  • 121
0 votes
1 answer
15 views

Best practices when customizing PostgreSQL configuration files

I am customizing my configuration files, both pg_hba.conf to allow LAN clients to access the DB on the machine and postgresql.conf to tweak my DB engine. By now I am just editing the files I've seen ...
Kodeeo's user avatar
  • 101
0 votes
0 answers
11 views

Can i install haproxy on the same server of postgresql

I'm reading and preparing to create HA Postgresql cluster of 3 servers using patrnoi , etcd , haproxy and keeplived. My question is - Is it ok to install HAProxy on the same servers of Postgres , or ...
Amr Salah's user avatar
1 vote
1 answer
34 views

How to improve custom aggregate function performance & identify bottlenecks

This is a continuation of a question I asked on the best way to compute statistics on a list of rows unique per column, which can be found here (along with the table schema) I have a table, which ...
Eoin Fitzpatrick's user avatar
0 votes
0 answers
34 views

Calculating aggregates grouped by column WITHOUT aggregate functions

I have a table which contains stock data for various companies. The data goes back as far as 2003, and there are approx 40M rows for each timeframe. CREATE TABLE stocks_data.bars ( timeframe ...
Eoin Fitzpatrick's user avatar

1
2 3 4 5
332