Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.
10,978
questions
0
votes
0answers
3 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
6 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
11 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 ...
4
votes
2answers
290 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
14 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
24 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
44 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
20 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
18 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
20 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
21 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
8 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
16 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
29 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
31 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
23 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
43 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
26 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
17 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
20 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
13 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
19 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
29 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 | ...
-------------------------------------------------------------------
...
