Questions tagged [postgresql-13]
Specifically for PostgreSQL version 13
85
questions
0
votes
0
answers
19
views
Foreign Key to Partitioned Table
I have the following basic schema for an ever growing huge transaction table. In order to query speedup & phase out stale data, I need to partition by time & country.
CREATE TABLE transaction (...
- 103
0
votes
1
answer
22
views
How to organize postgresql databases in pgadmin4?
I'm working with postgresql on my local machine with Windows 10, in pgadmin4 I can create new Server Groups and new Servers, if I create 2 new server groups and select localhost for the host, I get ...
0
votes
1
answer
38
views
How to optimized self-join view of 50 millions rows table
I've the following simplified table with ~50M rows.
table sample (
id uuid not null primary key,
measured_date timestamp with time zone not null,
segment_id uuid not null,
activity_id ...
0
votes
0
answers
20
views
How to include new key-value pair inside each object of JSON array in Postgres when retrieving records from a table?
I have below two tables in my database schema
spec_attribute
spec_attr_id
spec_attr_name
1
Value1
2
Value2
3
Value3
product_variant_attribute
product_id
variant_attributes
1
[{"...
- 133
0
votes
1
answer
36
views
Collation won't set when creating new database
I am trying to set the collation for a new database in PostgreSQL 13 but it does not seem to take effect:
postgres=# CREATE DATABASE assets ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'en_US.UTF-8';
...
- 153
0
votes
1
answer
41
views
Converting Query from DB2 to PostgreSQL, Now takes hours to run
I have a project for converting some web pages from using DB2 to the same data/tables/etc within PostgreSQL. I'm running into an issue where a query that would normally run in less than a second for ...
- 101
1
vote
1
answer
36
views
Filter table lookup by array value, but returning groupby unfiltered
I have multiple lookup tables and each need to be filtered by an array of values:
select
array_agg(joined_properties.name) as properties,
items.name
from items
left join (
select properties.name, ...
0
votes
1
answer
15
views
is it possible to replace the window function with custom function
I have a pagination sql with PostgreSQL 13 like this:
select
*,
COUNT(*) OVER ()
from
(
select
"article"."id",
"article"."user_id&...
- 477
1
vote
0
answers
20
views
pg_stat_statements vs slow query
Now I want to query the PostgreSQL 13 slow query that take more than 1s. Some told that should open the PostgreSQL slow query log, some recommand to use pg_stat_statements, which one should I choose? ...
- 477
-1
votes
1
answer
41
views
How to integrate Postgres advisory lock functions into SELECT and UPDATE?
Using Postgres 13 in a Node.js application, I have a simple task to do:
SELECT a row (identified by its primary key)
Run some application code that cannot be done in the database* with that data.
...
- 289
3
votes
3
answers
72
views
Do I need an explicit FOR UPDATE lock in a CTE in UPDATE?
In Postgres 13, I have a table which gets updated frequently. However, the update query is rather complicated and uses the same values multiple times. So, using a CTE seems quite a logical thing to do....
- 289
0
votes
1
answer
33
views
Returning in query whether a column has been changed by the current query or not
I have a query like
UPDATE my_table
SET my_value =
CASE WHEN random() > 0.5 THEN my_value * 2 ELSE my_value END
RETURNING *;
Now, inside the RETURNING statement I'd like to have a boolean ...
- 289
3
votes
1
answer
79
views
Performance penalty for COUNT(1) OVER (PARTITION BY NULL)
In my application server, I would like to paginate a dataset using LIMIT and OFFSET, and additionally return the total count of the dataset to the user.
Instead of making two remote calls to the ...
- 155
0
votes
0
answers
32
views
postgres - why does ALTER COLUMN run out of space?
Why does this POSTGRES command run out of space?.....
ALTER TABLE table1 ALTER column field1 TYPE VARCHAR(250);
field1 is currently VARCHAR(200), I need to increase it to VARCHAR(250) but after ...
- 801
2
votes
1
answer
51
views
postgresql Recursive Update resulting in Sequential Scan
I have a table with a self-referential foreign key, and I would like to update a given parent, and all of the descendants of the parent, with the same recursive update.
When using an UPDATE with a ...
- 155
0
votes
1
answer
30
views
GROUP BY date column and then group by custom daytime
So I have to group a table according to the date and then the timeday when a product arrived, the timedays would be:
morning = [5, 6, 7 , 8, 9]
mid_morning = [10, 11]
midday = [12, 13, 14]
evening = [...
- 49
3
votes
1
answer
60
views
Add a new row for each jsonb value pairs
I have this table from were I'm running a query:
CREATE TABLE IF NOT EXISTS public.preprocess_things
(
preprocess_id integer NOT NULL DEFAULT nextval('preprocess_things_preprocess_id_seq'::...
- 49
1
vote
1
answer
104
views
UPSERT with UPDATE on variable column gives error "command cannot affect row a second time"
Hey I'm trying to insert or update(if the constraints are duplicated) based on a query result, these are the create table statement:
CREATE TABLE IF NOT EXISTS public.inventory (
inventory_id serial ...
- 49
0
votes
1
answer
62
views
Upsert on a table that has multiple fields and jsonb column using WITH statement
So I was trying to do an upsert based on a query result:
/*
Querying from this table:
id | arrive_date | arrive_location | thing_type | thing_count
*/
CREATE TABLE IF NOT EXISTS public....
- 49
0
votes
0
answers
43
views
Postgres13 logical replication degrading slowly
We're halfway through a postgres upgrade (from 11 to 13) and have run into a pretty unusual blocker, being that our 13 logical replica can't seem to keep up with changes from our 13 master. For ...
0
votes
0
answers
26
views
PostgreSQL Constraint Where Value Mustn't be Present in Another Column
I have the following table in a postgres database (version 13)
CREATE TABLE public.links (
head_id int4 NOT NULL,
link_id int4 NOT NULL,
CONSTRAINT links_pk PRIMARY KEY (head_id, link_id),
...
- 1
1
vote
0
answers
78
views
Postgresql trigger insert on another table after update and update other tables
I have a post table and when I soft delete a post I want to also soft delete rows on the comment and post_image tables using a trigger.
I have an intermediate table deleted_post where I insert the ...
- 111
0
votes
1
answer
63
views
Postgres 13: Subscription stuck at initialize state
Postgres version: 13.5
I have 2 sets of publication/subscription between my 2 DBs.
One of them is working fine and the other one is stuck at initializing state for all the tables.
sourcedb=# select * ...
- 31
0
votes
1
answer
105
views
PostgreSQL partitions consuming huge amounts of memory
I have a decent sized (~10M row) table that recently has started slowing down quite a bit. As a solution to this, we decided to partition this table into 1,000 tables, partitioned by a hash of the ...
- 103
2
votes
2
answers
185
views
improve query performance in postgres
i have a table addressfabric_v2_2021_12
CREATE TABLE IF NOT EXISTS public.addressfabric_v2_2021_12(
country character varying(10) COLLATE pg_catalog."default",
areaname1 character varying(10)...
- 31
0
votes
0
answers
18
views
How do I change to the new server version?
I have versions 13 and 14 of postgres installed on my system. When I start the server using systemctl start postgresql it always starts version 13.
The client application shows version 14.
# psql -V
...
- 181
2
votes
1
answer
220
views
ERROR: invalid byte sequence for encoding "UTF8": 0x00 in pg_stat_statements
I'm attempting to use pg_stat_statements to optimise my queries but ran into an unexpected roadblock.
SELECT total_plan_time + total_exec_time as total_time, query
FROM pg_stat_statements
ORDER BY ...
- 33
4
votes
1
answer
39
views
PostgreSQL – Can queries that require all partitions be blocked or rejected?
We are planning to partition a dataset of ~500million rows using list/range partitioning in PostgreSQL 13.4 on RDS. Our read access patterns should only require a very small number of partitions to be ...
- 141
1
vote
0
answers
80
views
Debugging connection leak in postgres instance in RDS
Trying to debug a connection leak in postgres database instance in RDS
SELECT * FROM pg_stat_activity;
Running the above query shows around 500 sessions, but all of them have wait_event_type Client ...
- 11
1
vote
1
answer
129
views
Upgrade to PostgreSQL 13 fails
I'm trying to upgrade PostgreSQL 12 cluster to version 13, with following script:
/usr/lib/postgresql/13/bin/pg_upgrade --check \
--old-datadir=/var/lib/postgresql/12/main \
--new-datadir=/var/lib/...
- 832
1
vote
1
answer
30
views
Does `USING (column)` provide any memory advantage over an an explicit JOIN condition with ON?
Are there any performance differences between
JOIN bar USING (foo_id)
JOIN bar ON (foo.foo_id = bar.foo_id)
I ask this because at least in theory there is more data to project (a duplicate column). ...
- 55.7k
1
vote
1
answer
149
views
How to utilize partition pruning with subqueries or joins?
I have a partition table...
CREATE TABLE erco.rtprices
(
scedtime timestamp with time zone NOT NULL,
node_id integer NOT NULL,
lmp numeric(12,6),
CONSTRAINT rtprices_pkey PRIMARY KEY (...
- 601
1
vote
0
answers
68
views
Error "set-valued function called in context that cannot accept a set"
I have sql script which runs correctly on postgres 11.3 but not on 13.4
I have about 5 tables, used in script - code below
SELECT "enterprise_workplace"."id",
"...
0
votes
1
answer
102
views
Pass a variable with INSERT, UPDATE, DELETE statements
I am looking for the ways where could be possible to send some data with psql query statement. For example:
insert into mytable (id,name,site_id) values (default,'test',1);
Real structure of mytable:
...
- 3
1
vote
0
answers
78
views
max_replication_slots in RDS replica
We have enabled the on-prem PostgreSQL (Source) to AWS-RDS (Target) replication by following the official guide and which is worked well. Our RDS cluster has multiple replica's and am trying the ...
0
votes
1
answer
64
views
UPDATE column with previous value
Imagine a sample table
CREATE TABLE mytable (myid integer, myval double precision);
I want to update myval to the previous value, ordered by myid, if a condition is met (that myval is equal to 0).
...
- 181
1
vote
1
answer
59
views
Postgres 13 Sort Performance
Sort performance on one of our query is very bad that it takes up to 14 seconds to run the query.
Here is the Query:
SELECT "stock_quant".id
FROM "stock_quant"
WHERE ((((("...
- 11
0
votes
1
answer
59
views
Postgres: Generate 1-per-parent id sequences for child ids with no holes
I want a – can I call it a sub-sequence ? – to generate identity for child lines. A sequence per parent entity, starting at 1 with no holes. That is, each parent's childrens Ids should start at 1 and ...
- 194
1
vote
1
answer
96
views
Postgres creating 'histogram_bounds' statistics on JSONB column
I have two database tables (A and B), each containing roughly 1.6m rows, and each having the following structure:
CREATE TABLE tablename (
identifier text NOT NULL,
geometry geometry(GeometryZ,...
- 11
0
votes
0
answers
268
views
Postgres is extremely slow through localhost, for any command
Postgres 13 in my Windows 10 machine is extremely slow. Even with Postgres shell, every single command needs 22 seconds to be executed. Either it is a single query in a nearly zero-rows DB, or a ...
0
votes
1
answer
150
views
Occasional incorrect values for generated columns in PostgreSQL 13
I have a PostgreSQL 13 server running in Google Cloud and one of our tables has a few generated columns and occasionally I find rows where the value saved in the generated column isn't up-to-date.
For ...
- 173
0
votes
1
answer
2k
views
ERROR: syntax error at or near "END" [closed]
I am trying to play around with trigger and found some samples online which I pretty much copied before except changed table name and row counts. But somehow I kept on getting syntax error and ...
- 115
0
votes
1
answer
58
views
data model limit rows per user
This is regarding to postgres that I was asked to create data model which has a relationship with user.
For example a new data model called Comment M:1 User, but while creating the data model is it ...
- 115
1
vote
1
answer
298
views
Why is my spatial query slower in Postgres 13 than in Postgres 11?
Postgres versions
PostgreSQL 13.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit
PostgreSQL 11.11 (Debian 11.11-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by ...
- 113
1
vote
1
answer
292
views
SELECT rows from table one by one, comparing one column values with values in an array
I have an array of timestamps in a PLPGSQL code block: timestamp_array.
For each element of timestamp_array I want to get the element itself (timestamp_element) and some columns from a table (my_table)...
- 181
0
votes
1
answer
847
views
POSTGRESQL Subtract to each row the previous row
I'm writting a function which at some step I have query result like the following one:
timestamp
Consumed Fuel
Processed Volume
23-09-2021 5:00:00
3000000
982135
23-09-2021 6:00:00
3000010
982136
...
- 181
1
vote
1
answer
37
views
Selecting related data without selecting foreign key from subquery [closed]
cities
id
name
county_id
1
City1
1
2
City2
1
3
City3
2
4
City4
2
counties
id
name
1
County1
2
County2
Hello,
I would like to select all city names from related county by enetring a city name....
- 13
2
votes
1
answer
219
views
postgresql materialized view ERROR: a negative number raised to a non-integer power yields a complex result
I have complex view with calculations but one line makes issue
ROUND((((latest_revenue::numeric/ oper_reve_3y_old::numeric) ^ (1/3::numeric)) -1::numeric)*100::numeric)
When I execute the query, ...
- 123
0
votes
0
answers
45
views
How to view/track queries in PostgreSQL
How can I see, in real time, queries being executed on or arriving to PostgreSQL server, ideally filtered by a database?
Watching in log files using text editor is not very comfortable :)
- 109
0
votes
0
answers
38
views
postgresql 13 sync replication - does primary complete flushing to local before sending wal to slaves?
In a pgpool-II 4.2 and postgresql 13 environment:
3 servers (each hosting a pgpool and a postgresql)
I understand we can set the below parameter for different durability's.
synchronous_commit = off, ...
