Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
14,763
questions
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 ...
- 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 ...
- 101
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 | ...
- 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 ...
- 13
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 ...
- 1
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 ...
- 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 ...
- 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 ...
- 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 ...
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 ...
- 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 ...
- 141
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 ...
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) ...
- 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": ...
- 272
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 ...
- 1
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 ...
- 3
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 ...
- 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.
...
- 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 ...
- 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 ...
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 (...
- 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),...
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 ...
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&...
- 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,...
- 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 ...
- 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(...
- 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=$...
- 811
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;
...
- 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.
...
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', ...
- 103
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 ...
- 11
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 ...
- 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 ...
- 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 ...
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 ...
- 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
-------------+----------+-----------+----------+---------
...
- 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 | ...
- 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
...
- 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 ...
- 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" ...
- 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
-----------------+---------------+-----------+----------+----...
- 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 ...
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 ...
- 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 ...
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
-----------------+---------------+-----------+----------+----...
- 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?
- 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: [...
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 ...
- 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
...
- 95