A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.

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
2 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 ...
4
votes
1answer
1k views

Slow postgres query that uses quick sort instead of hash-aggregate

I'm on postgres 9.5 and working with a web-analytics database that records visitor traffic. I'm trying to optimize a very slow query gives me a count of the unique people that visited a given page ...
0
votes
1answer
9 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 ...
2
votes
2answers
202 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
13 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 ...
1
vote
1answer
97 views

PostgreSQL unique continue on violation

I am creating ETL where I rely on the database to refuse duplicate values for a business key. In SQL Server this works like a charm. The database gives an error, the ETL ignores the error, continues ...
-2
votes
2answers
108 views

Wrong Output(Null values) for a column

I am unable to get data from bnrcno column while using the below query. select 'insert into table values('||id||','||bnrcno||','||chargetype||','||domno||','||coa_id||','||liability_gl||','||...
4
votes
1answer
2k views

ERROR: improper qualified name (too many dotted names) when trying ALTER FUNCTION

Working with Amazon Aurora Postgres Version 2.0, which is basically PostgreSQL 10.4 with a modified storage layer. Trying to restore a dump that contains multiple lines like: ALTER FUNCTION ...
0
votes
2answers
23 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 ...
0
votes
1answer
89 views

pg replication connection not returns wal events to java code client , it hangs some times

Describe the issue We are facing issue of pg replication slot not listen database wal events. Once pg database restarted and we attach Driver Version: 42.2.6 Java Version: JDK1.8 OS Version: Window ...
1
vote
1answer
43 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
1answer
86 views

postgres performance tuning - what to try next?

I have a simple (but large) postgres database. I have a table defined like this…. CREATE TABLE TABLE_A ( MessID SERIAL UNIQUE, rawData CHAR(250), loadedTime TIMESTAMP)...
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
1k views

Multiple PostgreSQL running as primary server on pgpool

I'm facing a problem with my pgpool setup. Currently, this is my requirement based on the pgpool documentation here : Server A : Install with PostgreSQL database. Run as primary server Server B : ...
0
votes
1answer
673 views

pg_restore: [archiver] could not find entry for ID 12

I'm trying to restore a dump (-Fd --data-only) from PostgreSQL v9.4.9, but I've come across the following error: pg_restore: [archiver] could not find entry for ID 12 Exactly the same happens if ...
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;
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
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 ...
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) ...
1
vote
1answer
16 views

Remove the deeper jsonb element in Postgres

[ { "type":"test.added", "values":[ { "field":"field1", "value":"fieldValue1" }, { "field":"field2", "...
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 ...
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 ...
1
vote
2answers
90 views

DBEaver Vs PG Admin

When I ran a query in Postgres database using the Pg Admin tool it took only 2 SECS ( I can see the parallel workers being used in the plan) but when I ran the same query in DBeaver, it took around 3 ...
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 ...
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
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
2answers
142 views

PostgreSQL 10 Optimizing slow query performance

I have the following query: select i.id as id, i.ts_updated as updated, i."name" as name, cast(attr.data_norm as text) as data_norm, cast(_59.json_agg as text) as _59, ...
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
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 ...
4
votes
1answer
7k views

postgres system catalog query columns from table

I am building an integration test against an app that provisions a database for a user. The User created for it is not a super user, and does not have access to schema_information.tables because when ...
2
votes
1answer
167 views

pg_hba error while setting up repmgr

Please help me to figure it out, help is much appreciated! ERROR: FATAL: no pg_hba.conf entry for replication connection from host "172.18.0.4", user "repmgr", SSL on The following is a list of my ...
1
vote
1answer
2k views

How to connect to a Postgres server in a Docker (1.12.0) container on a Mac?

The latest release of Docker doesn't use a virtual machine anymore, instead using a hypervisor to connect to the containers. This means I can no longer login to postgres with psql: ➜ postgres git:(...
2
votes
2answers
10k views

After fresh install getting `psql: command not found`

I downloaded and installed PostgreSQL and PostGIS from KyngChaos for my OS X 10.10 install. When I run psql I get, bash: psql: command not found According to the site How to run psql on Mac OS X? it ...
28
votes
1answer
9k views

Why is my PostgreSQL ORDER BY case-insensitive?

I have Postgres 9.4.4 running on Debian and I get the following ORDER BY behavior: veure_test=# show LC_COLLATE; lc_collate ------------- en_US.UTF-8 (1 row) veure_test=# SELECT ...
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
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 ...
0
votes
1answer
19 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 ...
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....
1
vote
1answer
365 views

Row level security in PostgreSQL for different groups

Need a little help with row-level security in Postgres. I have several tables and three group roles (group1, group2, and group3). I have several login roles that are members of each group, while "...
0
votes
1answer
133 views

Why could pgloader fail with 'undocumented reason' while migrating large MySQL database to PostgreSQL?

The database I would like to import is approximately 55GB in binary format, about 36 GB exported by mysqldump. But as we know even the postgres compatible mysqldump is not 100% compatible, so I'm ...
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 ...
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
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
30 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
1answer
417 views

Can I grant privileges to a Postgres user on all schemas belonging to a database with one command or script?

Let's say I have 20 different schemas on a Postgres database. I create a new user theUser and now I want to grant only the SELECT and INSERT privileges for this user on all schemas. Is there an easy ...
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
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 ...
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 ...