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
0 answers
17 views

Advice for applying locks in my data processing pipeline

I currently have a Python program that enters rows into a Postgres table that essentially works as a list of data I need to process. These processes create on-disk files and trigger other behavior so ...
user avatar
  • 1
0 votes
0 answers
17 views

Best way to manage old data in postgres

I have a postgres db where everyday a significant number of rows across multiple tables are considered outdated and soft deleted. My understanding is that there's no point in keeping these rows around ...
user avatar
0 votes
1 answer
9 views

postgresql change clustername but pg_lsclusters say it not changed

SELECT current_setting('cluster_name'), current_setting('server_version'); return: current_setting | current_setting -----------------+---------------------------------- my_cluster_14 | ...
user avatar
  • 67
0 votes
0 answers
7 views

upgrading postgres 9.2 cluster

I am looking to upgrade a running CentOS based postgres 9.2 cluster to version 14. One database is relatively small with ~400k records, however the second is quite a bit larger and holds several ...
user avatar
0 votes
1 answer
14 views

Speed up Postgres Query and Function

I have a tables that looks like this: create_table "cboe_raws", force: :cascade do |t| t.date "quote_date", null: false t.string "underlying_symbol", null: false t.string ...
user avatar
1 vote
1 answer
13 views

How to query dates in different timezones?

I have a table and index in a PostgreSQL 10.18 database: CREATE TABLE some_table ( expires_at timestamptz ); CREATE INDEX ON some_table(expires_at); Is there a way to write this query in a way to ...
user avatar
  • 163
1 vote
1 answer
20 views

WAL efficient alternative to VACUUM FULL

Performing a VACUUM FULL produces a lot of WAL. We need downtime to perform this as well. What if we manually created a new table and copied over all the data. Then we could simply drop the ...
user avatar
  • 145
1 vote
1 answer
23 views

Is there a way to see which tables contribute most to WAL size?

I'm maintaining a postgresql database and am considering the use of clustering so that update queries have to touch fewer pages. With clustering based on indices I'm seeing a significant speedup for ...
user avatar
  • 111
-4 votes
1 answer
28 views

slow postgres performance with simple querys

after alot of reading i found multiple sources saying postgres should be able to handle ~100 million rows i set up a simple table with: CREATE TABLE trade ( id INT PRIMARY KEY NOT NULL, symbol ...
user avatar
2 votes
0 answers
34 views

Postgresql 14 Performance regression versus PG13

I migrated a database from Postgresql 13 to Postgresql 14 recently, and I saw a x2 execution time in a specific case which I can't understand. The issue has been seen on a Postgres on Linux, and I ...
user avatar
  • 21
0 votes
0 answers
13 views

postgresql is my approach to row-based access control not simpler and more efficient under my stated assumptions?

When you have a 3 tier system with an application server in the middle, using a single database connection user but managing application users differently as it is typically done, how do you implement ...
user avatar
0 votes
1 answer
18 views

Is there any equivalent of Oracle's XMLQUERY in PostgreSQL?

I'm trying to find equivalent of XMLQuery in PostgreSQL. Following results I got from Oracle's LiveSql. Please suggest how to get same results on PostgreSQL. CREATE TABLE person_data ( person_id ...
user avatar
0 votes
0 answers
13 views

Optimizing queries that involve rank computing PostgreSQL full-text search

Suppose we have a MATERIALIZED VIEW that has to be searched using PostgreSQL full text search. View contains both fields with information about the object and precompiled (and annotated with weigths) ...
user avatar
  • 1
0 votes
0 answers
16 views

Join two jsonb on keys and output values

The example setup: CREATE TABLE configs ( device_id int PRIMARY KEY, elements jsonb not null -- more columns omitted ); INSERT INTO configs (device_id, elements) VALUES (1, '{"00": ...
user avatar
0 votes
1 answer
24 views

Amount of clients that have subscription by years and month

I got 2 tables as follow CREATE TABLE subscriptions( client_id INT, open_date DATE, close_date DATE ); CREATE TABLE calendar(date DATE); subscription table contains data about clients ...
user avatar
0 votes
1 answer
33 views

Fuzzy searching through multiple fields in postgreSQL

I've to do fuzzy search on multiple fields (in an attempt to create something like autocomplete similar to product search in amazon). I tried this through ElasticSearch but was wondering if there's ...
user avatar
1 vote
1 answer
32 views

A query containing multiple joins becomes too slow when adding one more condition after the joins

I have this query that finishes in less than a second on average: SELECT a.trea_code, a.fin_year, a.bill_no AS tran_id, ((('Transfer FROM ' :: text || (a.ddo_code) :: text) || 'ON Bill ...
user avatar
  • 13
0 votes
1 answer
16 views

Calculate difference based on timestamp

in my PostgreSQL DB I have got table like that: calendar_tab calendar_id - int user_id - int type - varchar(16) start_date - timestamp end_date - timestamp Users add different events to this table. ...
user avatar
  • 101
0 votes
0 answers
22 views

Why is my time range query slow even when I have BRIN index on the timestamp?

I have a table that stores chat messages, like so; id (int8), timestamp (timestamp), author (text), channel(text), content(text) I have BTREE indexes on all of the fields, except for timestamp, which ...
user avatar
  • 101
3 votes
2 answers
35 views

Inconsistent data when combining (LEFT) JOIN with SELECT FOR UPDATE

I have recently stumbled upon a seemingly odd behavior of SELECT ... FOR UPDATE when combined with (LEFT) JOIN. Here is the table structure as well as a scenario to reproduce the result: Table ...
user avatar
0 votes
1 answer
32 views

Importing CSV with a json array of strings in one of the column

The columns of the CSV are of the format text (can include empty lines), date and a JSON array of strings (something like ['a', 'b', 'c']. I've been trying to copy that CSV to a PostgreSQL table (...
user avatar
  • 3
-1 votes
0 answers
15 views

Alternative to slow SRF in SELECT?

I have the following case select my_srf_func(otherfunc(h)) from (values (‘…’::mytype), ‘…’::mytype), (‘…’::mytype), ‘…’::mytype), (‘…’::mytype),...
user avatar
0 votes
0 answers
19 views

Wrong postgres evaluation for plan rows

We've encountered an issue where a query for some entities takes thousands of times longer. After analyzing, I came to the conclusion that the problem is that posgres expects the wrong 'plan rows' and ...
user avatar
1 vote
1 answer
27 views

Get the difference for a column value by all its previous rows data

Please consider these two tables: Vault id BIGSERIAL PRIMARY KEY name VARCHAR(30) NOT NULL Transaction id BIGSERIAL PRIMARY KEY transaction_type VARCHAR(6) NOT NULL -- Choice between "credit&...
user avatar
  • 113
0 votes
0 answers
15 views

How to grant access to new users in postgresql?

begin; set search_path to test; CREATE USER jian password '1'; GRANT CONNECT ON DATABASE test TO jian; GRANT ALL PRIVILEGES ON DATABASE test TO jian; GRANT USAGE ON SCHEMA test TO jian; GRANT SELECT,...
user avatar
  • 67
0 votes
0 answers
9 views

notification handling in postgresql

Im designing a database for a web app. I'm struggling with the notifications topic: in my web app there will be a forum with messages, comments, and users. I would like users to be notified when ...
user avatar
  • 1
0 votes
1 answer
25 views

How do I find out why CREATE INDEX CONCURRENTLY is failing?

I set off the creation of an index with the command CREATE INDEX CONCURRENTLY index_Name ON namespace.table_name(column_name); and watched the action using this command on another session: select now(...
user avatar
  • 101
0 votes
0 answers
22 views

Using ODBC drivers to connect to Postgres database via Powershell

I've routinely connected to a postgres database via ODBC using a powershell script. Like this.... $DBConnectionString = "Driver={PostgreSQL Unicode(x64)};Server=$MyServer;Port=$MyPort;Database=$...
user avatar
0 votes
1 answer
17 views

Grant All on All tabes in schema?

I have a question about grants... I ran the following command, assuming it would allow me to make any changes to the tables in the public schema: grant all on all tables in schema public to User1; ...
user avatar
  • 11
0 votes
1 answer
24 views

pg_basebackup: error: could not initiate base backup: ERROR: could not stat file or directory

I have postgres 14 running on Windows 2019 server and I am using pg_basebackup to take regular backups. The same powershell script which was working last week suddenly started giving me below errors. ...
user avatar
0 votes
1 answer
39 views

How to incrementally optimize a `COUNT(*) ... GROUPBY` query in PostgreSQL?

I have a PostgreSQL table with ~millions of rows (timestamp, event, distinct_id, properties) where I want to filter based on event (low cardinality) and timestamp, and group by date_trunc('week', ...
user avatar
0 votes
0 answers
33 views

database structure for many to many to many relationships

SQL newby here. I have a complex database situation I would like to implement, involving books. What I have come up with so far is three hierarchal terms that help me define all of this, each of which ...
user avatar
1 vote
1 answer
27 views

Is it possible to reference an inserted column within the same INSERT?

This query gives the error: create table qwe (a bigint, completed bit varying(100000)); CREATE TABLE insert into qwe (a, completed) values (1000, repeat('0', a)); ERROR: column "a" does ...
user avatar
  • 202
2 votes
1 answer
24 views

"column new.total does not exist" error while creating a trigger

I have spent a lot of time investigating this error and couldn't find a solution. I am trying to create a trigger function and a trigger and then call the procedure when the when () condition in the ...
user avatar
  • 21
0 votes
0 answers
27 views

Using a string vs int/guid as the data type when I want to import data from a CSV file too

I'm having a dilemma about how I should build my entities in my case. So, let's say I have a Person entity with many fields. One of those fields will be a FK named City. The value for this field is ...
user avatar
1 vote
0 answers
11 views

Spatial database architecture with Apache Parquet, PostgreSQL, and PostGIS on on-premises bare-metal S3/MinIO cluster

Designing storage architecture for Petabyte-scale geospatial data; starting from scratch. Creating a MinIo cluster to store the objects in S3 buckets. To store the metadata, I’m considering the Apache ...
user avatar
  • 111
0 votes
2 answers
52 views

Table with smaller data types seems to take more space on disk?

I have these two identical tables: Table "public.region" Column | Type | Collation | Nullable | Default -------------+----------+-----------+----------+--------- ...
user avatar
  • 95
0 votes
1 answer
13 views

pg_class returns 0 as relpages

I have this table: Table "public.region" Column | Type | Collation | Nullable | Default -------------+----------+-----------+----------+--------- r_regionkey | ...
user avatar
  • 95
0 votes
1 answer
36 views

Marking/retrieving a long array of numbers as "true/false" in postgres

I have a long list of indexes/numbers from 0 to 10 million. I would like to mark each of these numbers as true/false or set/unset. I want to avoid creating a table with a row for everynumber like ...
user avatar
  • 202
-1 votes
0 answers
12 views

How can I migrate data from one PostgreSQL database to another (with slightly different table/column names)?

I am trying to migrate a large portion of one postgres database to another postgres database that has a slightly different layout/table names/column names. But the data is the same. What is a good way ...
user avatar
  • 99
3 votes
1 answer
91 views

Is it safe to use default value with not null when adding a new column?

We have a Rails app powered by Postgresql v11.4 where I want to add a new column with a default value and a not null constraint like below: ALTER TABLE "blogs" ADD "published" ...
user avatar
  • 235
1 vote
1 answer
31 views

Why is the optimizer not using the clustering index on my table?

I have this table Table "public.lineitem" Column | Type | Collation | Nullable | Default -----------------+---------------+-----------+----------+----...
user avatar
  • 95
0 votes
2 answers
27 views

How to enforce only one NULL value per unique combination of some other columns?

here's the situation, I have a table A with like such CREATE TABLE A ( id UUID NOT NULL PRIMARY KEY, B_id bigint NOT NULL, C_id bigint NOT NULL, some_value ...
user avatar
0 votes
1 answer
37 views

How to fix unicode REPLACEMENT CHARACTER � in postgres database

Is there a way fix the � problem in postgres database: As you can see there are names with accents and are not displayed corretly in the database. I would like to know if there is a way to fix this ...
user avatar
  • 175
0 votes
0 answers
43 views

PostgreSQL- Grouping results without using GROUP BY

I'm trying to understand the logic behind the group by and the possible alternatives to it. That's why, I've been trying to solve a puzzle where I try to make the same as with a group by clause but ...
user avatar
6 votes
1 answer
791 views

Do datatypes of columns affect query performance?

Let's suppose I have this table: Table "public.orders" Column | Type | Collation | Nullable | Default -----------------+---------------+-----------+----------+----...
user avatar
  • 95
0 votes
1 answer
16 views

Express a predicate DATE >= X and DATE < Y in form of DATE in [X,Y)

As per this paper: I can express an upper and lower bound as a single predicate. How can I do it in PostgreSQL?
user avatar
  • 95
-1 votes
0 answers
12 views

Best Databases options for Geo-Spatial data

I have following kind of DB schema in key value pairs. { id1: [{location: [lat1, long1]}, {location: [lat2, long2]}, ..., {location: [lat3, long3]}], id2: [{location: [lat4, long4]}, {location: [...
user avatar
0 votes
1 answer
18 views

Postgresql - how to use bool_and with group by

I have the following schema, that represent collection of messages, where each message can have multiple items (at least 1). Every item belongs to a message. Items can change over time. CREATE TABLE ...
user avatar
  • 147
0 votes
1 answer
46 views

How to speed up subquery

Let's suppose we have: select o_orderpriority, count(*) as order_count from orders where o_orderdate>='01/07/1993'and o_orderdate<'01/10/1993' and exists (select * from ...
user avatar
  • 95

1
2 3 4 5
296