Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

Filter by
Sorted by
Tagged with
0
votes
0answers
21 views

Adding a column and new foreign key constraint in one line

Let's say we have a table called Salesman with the folling properties Create Table SalesMan( EmplyeeId int Primary Key, Name varchar(20) Not Null, LastName Varchar(20) Not Null, Email Varchar(...
0
votes
2answers
20 views

Count violated unique constraint caused by INSERT statement

I would like to add rows from table2 into table1 and see how many conflicts of the type "duplicate key value violates unique constraint" result from this in PostgreSQL. pkey is a primary key, so there ...
1
vote
1answer
22 views

Pglogical - multiple database tables to single table

I have 4 databases, each database is having 2 tables. The table structure on all the databases are same. So i want to replicate these two tables from all 4 databases to a single database. So my ...
0
votes
1answer
9 views

how to make postgresql in cluster(HA Active-Active) and auto sync the data between them [closed]

For one of our application we need to build the database as mentioned in the title. We have one of our application which need to run in postgresql ha active-active cluster . Were postgresql cluster ...
1
vote
1answer
20 views

Remove the deeper jsonb element in Postgres

[ { "type":"test.added", "values":[ { "field":"field1", "value":"fieldValue1" }, { "field":"field2", "...
0
votes
0answers
3 views

Looking the way to install pg_rman on Cento6.10 i686 with postgresql11

Information: CentOS release 6.10 (Final) Linux Localhost 2.6.32-754.24.3.el6.i686 i386 GNU/Linux I have tried to install pg_rman on my CentOS release 6.10 i686 server but no luck. I also tried to ...
0
votes
2answers
24 views

Need help in optimizing a slow Postgres Query

So, I need to calculate a rolling Monthly active users on everyday for last 100 days. In output, one column should represent the dates of last 100 days from today and against every single day, number ...
0
votes
1answer
14 views

PostgreSQL How to get friend requests in table by seeing if there is a duplicate of the reverse of a column?

I've a table, CREATE TABLE friends ( friend1 serial references users(id), friend2 serial references users(id), unique (friend1, friend2) ); Friends are in no particular order, but I ...
0
votes
1answer
43 views

Calculating the value of a column based on consecutive values of rows in another column

It is straight forward to compute column value based on values of other columns, e.g. speed/time AS acceleration where speed and time are columns. Is there a way to compute column's value based on ...
0
votes
2answers
40 views

Is it Possible To Take Full And Differential Backup in Postgresql Like Sql Server?

I have a postgresql server. I want to take its backup full and differential mode as it is like in sql server. Is it possible to do this in postgresql? How can i do it, if it is?
0
votes
1answer
29 views

How do I deal with the inevitable scenario when a table's bigserial runs out of ids but many of the old records are deleted?

I use PostgreSQL. I have many database tables which contain data of the kind that is regularly "replaced entirely", as in, I first clear (delete) all existing records, then just insert all-new records....
0
votes
1answer
20 views

Share single index for multiple linearly correlated columns

I've got a table with two columns whose values have a perfect linear correlation, for example CREATE TABLE measurements ( sensor int PRIMARY KEY, num serial PRIMARY KEY, time timestamptz ...
1
vote
1answer
18 views

postgresql count null and blank value from jsonb column

In following JSON structure, [ ["a", 921], ["b", ""], .. ... ["c", null], ] how to count occurrence of blank value "" null value
0
votes
0answers
13 views

Get Maximum query execution time on amazon RDS replica

On amazon rds I have a single master database with 2 read-only replicas. Because of ERROR: canceling statement due to conflict with recovery error I thought instead of setting hot_standby_feedback=on ...
0
votes
0answers
13 views

Use pgpool in order to serve the mostly recently replicated Postgresql read-only replica databases?

As seen in this question setting the max_standby_archive_delay and the max_standby_streaming_delay as -1 on a single replica it may cause the replica on long-running queries to have stale data. So I ...
0
votes
1answer
25 views

Manually VACUUM bloated tables once `hot_standby_feedback` is on on postgresql?

As seen in this answer by setting postgresql's hot_standby_feedback on it may cause having bloated table on long running queries. Therefore, in case that size goes out of control (my database consumes ...
1
vote
1answer
34 views

54001 stack depth limit exceeded in recursive stable function [closed]

I have a function which calls itself to resolve a hierarchy. Without any volatility declaration, it gives me the desired result (that means the recursion ends). As a performance optimization, I ...
0
votes
2answers
35 views

Missing data for one schema in pg_dump

I have a Postgres 9.6 database with several schemas, and a regular backup of this database is done with a command like pg_dump -h localhost -d db -U postgres -Fc -f file.dump When I restored a ...
1
vote
0answers
34 views

Recursive CTE based on function values significantly slower on Postgres 12 than on 11

Following up on my question about some queries in Postgres 12 being slower than in 11 I think, I was able to narrow down the problem. It seems like one recursive CTE based on function values is the ...
0
votes
1answer
25 views

How I can monitor Amazon RDS Postgresql table size once the `hot_standby_feedback` is on

I have a postgresql Master-Slave replication scheme and because of query cancelation due to conflict with wal I need to set the following setting to each replica: hot_standby_feedback = on But ...
0
votes
0answers
17 views

Improve PostgresSQL aggregation query performance

I am aggregating data from a Postgres table, the query is taking approx 2 seconds which I want to reduce to less than a second. Please find below the execution details: Query select a....
1
vote
1answer
35 views

Could once `max_standby_archive_delay` and `max_standby_streaming_delay` with value -1 can cause replication stop on heavy loads?

In my replication servers on a postgresql master-slave replication scheme on aws RDS I get the following error: SQLSTATE[40001]: Serialization failure: 7 ERROR: canceling statement due to conflict ...
0
votes
1answer
50 views

Doubt about Database design (newbie)

I am learning about Database design, I have doubt about some database design conepts I want to know which is better design between two design Design 1 there is two tables: Object and Field ...
0
votes
1answer
20 views

paginating over large number of postgresql rows in a sorted order [closed]

The goal is to retrieve many millions of rows ordered by their timestamp. Since there are so many rows, I need to retrieve them in chunks. I've read about paginating over results, but all the ...
0
votes
0answers
17 views

Insert multiple values with associations from json

Say we had a schema like this: create table person ( id serial primary key, first_name text ); create table vehicle ( id serial primary key, name text not null, person_id int unique not ...
0
votes
2answers
44 views

Load PostgreSQL database from corrupt drive [duplicate]

I suffered corruption of a hard drive but managed to recover files for a PostgreSQL server. We are running on the assumption that the files themselves are not corrupted (yet to be able to tell). My ...
0
votes
1answer
20 views

List foreign keys dependencies that refer to a given table

RDBMS: PostgreSQL 9.6 I desire to list the foreign keys for a given table and schema. I am about 90 % there but I also desire to know what options were used to make the fkey. Example on delete ...
1
vote
2answers
48 views

Is it better to have a CASE expression or a JOIN with a dimensional table?

I have a fact table which has a timestamp and I want to express the day of the week. I could do something like this: SELECT CASE WHEN date_part(timestamp,'week') = 0 then 'Monday' WHEN date_part(...
0
votes
0answers
12 views

Best practices for keeping protobuf spec in sync with relational database (e.g. PostgreSQL)

1. We have a schema definition in Google's Protocol Buffers format that we use to generate code for different gRPC implementations (Python, PHP) 2. We have a database (PostreSQL) that is initialized ...
-1
votes
0answers
27 views

Simple inventory management db design - polymorphic association?

I’m designing a web application for internal use to inventory-manage returnable/reusable widgets (a physical product). I anticipate around a 100 or so ‘stock/widget transactions’ per day. In simple ...
1
vote
1answer
32 views

Postgres 11+: are covering indices (INCLUDE) useful for join/where conditions?

I'd like to better understand when covering indices can be useful to make index-only scans possible in Postgres 11+. As the documentation says, given the covering index CREATE INDEX tab_x_y ON tab(x) ...
0
votes
0answers
24 views

trigger updating same table

I have an inventory_movements table, and every time a sale is done, a new row is saved through a trigger (called "regularizestockmov"). If you delete or update a row that is not the last row, it has ...
0
votes
1answer
29 views

Returning values for Stored procedures in PostgreSQL

I was reading PostgreSQL Tutorials there I found In case you want to return a value from a stored procedure, you can use output parameters. The final values of the output parameters will be returned ...
1
vote
1answer
18 views

Is SELECT FOR … UPDATE *necessary* to DELETE or UPDATE multiple rows without deadlock?

Consider the following when the transaction isolation level is Read Committed (the default): UPDATE example SET foo = 'bar' WHERE ... and DELETE FROM example WHERE ... Neither DELETE nor UPDATE ...
0
votes
1answer
22 views

Using pg_dump to create 144 backups per day

Imagine the following scenario (this may not be a viable or sensible idea but please imagine that it is). Using Ubuntu, a cron job calls pg_dump and backs up a Postgres database every 10 minutes. In ...
1
vote
1answer
15 views

Can I improve the way pg_dump exports inherited tables?

Given a postgresql 12 database with some table inheritance, and the child table adds no additional columns: CREATE TABLE parent ( id integer NOT NULL PRIMARY KEY, name text NOT NULL ); ...
1
vote
2answers
22 views

Does PostgreSQL btree_gin extension uses btree or gin data structure?

In order to define a GIN index on an array and scalar field (for example) you need to enable the btree_gin extension in PostgreSQL. Then you can define your indexes as a GIN index. However I don't ...
1
vote
3answers
30 views

PostgreSQL (big data): search by tags and order by timestamp

We need to add a search feature on a large table (200M+ rows): item_id | tags | created_at | ... ------------------------------------------------------------------- ...
1
vote
2answers
43 views

PostgreSQL remove numbers before first comma

I am new to SQL however, I am looking to update a table where column called "scrap_value" and rows contains a set of numbers in an array e.g. {100000,125000,150000,175000,200000} I want to remove the ...
0
votes
0answers
25 views

Extract Image URLs from html text using Postgres sql Query

I have a column in postgres db in which html contain stored. Like this <p style="text-align: justify;"><strong><img alt="" src="https://images.unsplash.com/photo-1579121477063-...
-1
votes
2answers
15 views

How to transfer the incremental MS Access table data to PostrgreSql Tables [closed]

I have an attendance punching data stored in MS Access database. It's increasing day by day basis. Can we convert the MS Access table data to the PostgreSql table on the incremental bases. What is ...
-1
votes
0answers
17 views

Postgres: handy way/function to compute bearing change from coordinates

I am going to compute point-based acceleration and bearing relative to true/magnetic north from the sequence of GPS points with timestamp and instantaneous speed. Here's how the dataset looks like: ...
1
vote
1answer
29 views

Slow bitmap heap scan on group/count query in a large table

I run an app with a database of PostgreSQL 10.10 I am optimizing a bunch of queries in our application, with great results so far, but there is one specific query that takes about 1 minute if not ...
1
vote
2answers
44 views

Determining Whether Query Load is Too High for PostgreSQL Server

I just started experiencing some write-heavy load on a PostgreSQL 11.2 system running inside a Docker container on an Ubuntu 18.04 server. PostgreSQL is receiving about 1-4k INSERT queries/second. ...
0
votes
1answer
16 views

Fail to copy all PostgreSQL databases between servers although there is no error message

I want to copy all databases from PostgreSQL 9.3 on Server-1 (Windows 7) to an empty PostgreSQL 12.0 on Server-2 (Windows Server 2012 R2). The steps are as follows: On Server-1: C:\Program Files\...
1
vote
2answers
40 views

Optimize INSERT performance with a UNIQUE constraint on md5(JSONB_COLUMN::text)

I have the following table and indices defined: CREATE TABLE IF NOT EXISTS data ( id serial, job_id bigint NOT NULL, payload jsonb NOT NULL, tags jsonb NULL ); CREATE UNIQUE INDEX IF ...
0
votes
1answer
30 views

PostgreSQL Export to CSV. Each element of array in new line [closed]

I have next request \copy ( SELECT users.name, ARRAY_AGG(emails.email), ARRAY_AGG(addresses.address) FROM users LEFT JOIN emails USING(id) LEFT JOIN addresses USING(id) GROUP BY users....
2
votes
2answers
22 views

Calculating tsrange based on parent/child created dates

Given the table: CREATE TABLE node ( id serial PRIMARY KEY, created timestamp without time zone NOT null, path ltree NOT null UNIQUE ); And the records: INSERT INTO node (id, path, ...
0
votes
1answer
20 views

Table Partitioning Using Inheritance and Triggers

I am trying to do table partitioning in PostgreSQL using inheritance and triggers but the problem that have occured to me is that the data does not move from the parent table to the children tables I ...
-2
votes
0answers
7 views

Why PostgreSQL lose connections after checkpointing start on Flink? I'm using a RichSinkFunction [closed]

19:59:21,934 INFO org.apache.flink.runtime.taskmanager.Task - Flat Map -> GeoIsp Freq count -> (Sink: Freq Isp User Sink, Sink: Freq Isp Device Sink) (4/4) (...