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
7 views

PostgreSQL 11 trigger can't select a value in a table to use it in a second table

Here's a sample of log : gid alti numlog 1 79.55 1 2 79.65 2 Here's a sample of us : gid numlog numus sommet base alti_sommet alti_base 1 1 1000 0 0.25 (79.55-0) (79.55-0.25) 2 ...
0
votes
0answers
9 views

Recover corrupt archive log file in postgres

I am using postgreSQL 11. I want to know how can I recover the corrupt archive log file available in postgresql.
0
votes
0answers
10 views

multiple pg_dump

My Postgres 9.3 cluster has 9 databases. I want to take backup of these databases using pg_dump utility of PostgreSQL 10. Since individual dbs are too large in size (~350GB), I want to run pg_dump ...
0
votes
0answers
14 views

Unable to deny remote access to Postgresql even if there is no entry in pg_hba.conf

I have 5 Postgres Servers, with IP addresses let's say as A,B,C,D and E. A is the master server and remaining are the child servers which need to access A. In the postgresql.conf file of A, the listen ...
1
vote
0answers
19 views

HIVE + understanding the hive-metastore logs

we have HDP cluster version - 2.6.4 , and we are runs spark streaming app and we are uses presto cluster in order to run Hive queries when we look on the hivemetastore logs ( under /var/log/hive ) ,...
0
votes
0answers
12 views

How to perform schema migration from array to many-to-many to lookup table?

I currently am storing values in a column of type array like follows (this is just an example): CREATE TABLE blog_posts( id INTEGER PRIMARY KEY, blog_title TEXT, tags TEXT[] ); With the ...
0
votes
1answer
9 views

Limiting user access by subnet to Amazon Aurora PostgreSQL

How do I limit user access by IP? For example, assuming I have two non-superusers (user1 and user2), how can allow general access (only limited by security groups) to the instance/cluster by user1 but ...
0
votes
0answers
11 views

How to create user(read-write & readonly) and admin roles for an existing postgres database?

We have an existing postgres database in production with a superuser adm that is being used to do everything. Our web application connects to the database using the same user and also the ...
0
votes
1answer
28 views

Listing function input and output details for stored functions in Postgres 11.5+

I'm trying to build a query that extracts the input and output definitions for the functions in a schema. We've got multiple client types written in multiple languages, and plan to move many queries ...
5
votes
2answers
439 views

What is the `TABLE example` syntax called?

In PostgreSQL, I've been using syntax like TABLE example1 UNION ALL TABLE example2 which I understand to be equivalent to SELECT * FROM example1 UNION ALL SELECT * FROM example2 But a colleague ...
0
votes
1answer
15 views

Prevent overlapping TSRANGE

I have the following PG (12) reservations table id uuid reservable_id uuid reserved_between tsrange How can I ensure there are no 2 reservations for the same reservable_id ...
0
votes
2answers
27 views

How to completely restrict a postgres role to a single schema

I'd like to create a readonly role for a third party to access a handful of tables from the public schema without being able to view the rest of the tables in public. My initial idea was to create a ...
1
vote
1answer
45 views

How to denote separate records as “one and the same”?

Let's say there are two separate Person records V. Baker Vincent Baker each with their own various associations. These could be things like photos, contact information, etc. And let's say that it ...
0
votes
0answers
21 views

PostgreSQL database cluster

I installed PosgreSQL 12 using the certified installer by EnterpriseDB. I went through the docs. In Chapter 18.2. Creating a Database Cluster, it is said that you need to initialize a database ...
0
votes
1answer
19 views

How often is Postgres parsing a foreign table based on file_fdw?

I have a foreign table defined with file_fdw, which reads the data from a CSV file. Postgresql parses the CSV file and converts the date for example from YYYYMMDD to YYYY-MM-DD. This works fine. My ...
0
votes
1answer
33 views

Does CREATE TABLE AS lock the table?

In Postgres, does this query lock films for reads and/or writes? CREATE TABLE films2 AS TABLE films;
2
votes
1answer
25 views

Simple select query hangs

I have a simple query: SELECT a.name AS a_name, b.description AS b_description FROM a JOIN b ON a.id = b.a_id WHERE b.table_name IS NULL; (Names changed to protect the innocent) ...
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
19 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
22 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
13 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
41 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
0answers
16 views

How to force PgScript to behave like pSQL in pgAdmin 4

I need to use the pgScript below to call a function that should load 500 streetsegments at a time, in batches. However, PgScript does not seem to be supported anymore in pgAdmin 4. Is there another ...
0
votes
2answers
38 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
27 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
12 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
11 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
30 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
32 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
24 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
20 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
16 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
29 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
47 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
19 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
1answer
35 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 ...