Questions tagged [postgresql-13]

Specifically for PostgreSQL version 13

Filter by
Sorted by
Tagged with
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 (...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 [{"...
user avatar
  • 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'; ...
user avatar
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 ...
user avatar
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, ...
user avatar
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&...
user avatar
  • 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? ...
user avatar
  • 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. ...
user avatar
  • 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....
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 = [...
user avatar
  • 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'::...
user avatar
  • 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 ...
user avatar
  • 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....
user avatar
  • 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 ...
user avatar
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), ...
user avatar
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 ...
user avatar
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 * ...
user avatar
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 ...
user avatar
  • 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)...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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/...
user avatar
  • 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). ...
user avatar
  • 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 (...
user avatar
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", "...
user avatar
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: ...
user avatar
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 ...
user avatar
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). ...
user avatar
  • 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 ((((("...
user avatar
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 ...
user avatar
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,...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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)...
user avatar
  • 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 ...
user avatar
  • 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....
user avatar
  • 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, ...
user avatar
  • 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 :)
user avatar
  • 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, ...
user avatar