Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

Filter by
Sorted by
Tagged with
0 votes
1 answer
17 views

How to merge and transform data in Postgres

A sql query yields data that looks like this: name quality magnitude john kindness 7 john greed 2 jane kindess 3 jane greed 7 john temper 9 jane temper 4 I am wondering if there is a way to ...
-3 votes
0 answers
19 views

i am getting zero entry of user session in PostgreSQL database session table [closed]

i am working on a website and i am using PostgreSQL for database. When i tried to see user session in user session table, i found zero entry.I Have already configured session in settings file, still ...
1 vote
2 answers
178 views

Index conditional aggregate query on PostgreSQL

How does one optimize a conditional aggregate query on PostgreSQL? A example of such query would be: SELECT SUM(score) FROM "games" WHERE "PlayerId" = 23 AND "status" = 'FINAL'; Indexing (PlayerId, ...
1 vote
1 answer
54 views

Calculating Z-Score

How do I calculate the Z-Score for a set of values in SQL (Postgresql). Calculating the z-score involves:
0 votes
1 answer
279 views

Table with only one column because of many-to-many relationship?

Right now I have three tables: person, alias, and person_alias. As suggested by the names of tables, one person can have multiple aliases and one alias can be used by many people, and hence I add a ...
0 votes
1 answer
25 views

PostgreSQL: does the WAL group commit affect synchronous replication performance?

I'm curious if PostgreSQL's Group Commit mechanism (controlled by commit_delay and commit_siblings parameters) only affects disk fsync calls. Or does it also allow multiple on-the-fly transactions to ...
1 vote
1 answer
807 views

PostgreSQL - Replica Recovery Process Stuck frequently (epoll_wait taking too much time)

We have Master Slave Setup with Asynchronous Streaming Replication (Replication Slots). Frequently our Replica's entire Postgres processes seems to be doing nothing (or stuck?). Since ...
0 votes
0 answers
11 views

pg_restore error when using `--single-transaction`: options c/--clean and -a/--data-only cannot be used together (even so the -a flag was not used)

I met a strange PostgreSQL (v.13.8) on a Windows server 2019 machine today when trying to restore a custom dump file. The following command worked: > pg_restore -d postgres://postgres@localhost:...
9 votes
3 answers
7k views

Slow connect time to postgresql on Windows 10

Connect time to postgresql on my Win 10 machine incredible slow. It take 2.5-3.5 seconds for a connect. $ time 'c:/Program Files/PostgreSQL/10/bin/psql.exe' -h 127.0.0.1 -p 5433 -U xxx -c 'select ...
31 votes
4 answers
36k views

Query the definition of a materialized view in Postgres

I'm wondering how to query the definition of a materialized view in Postgres. For reference, what I hoped to do is very similar to what you can do for a regular view: SELECT * FROM information_schema....
0 votes
1 answer
216 views

support of multiple databases on different hosts by pgpool2

Let's assume We have 2 databases: Database1 is located on host1 and host2 Database2 is located on host3 and host4 If I'll use pgpool2 to connect to these 2 databases with single pgpool config for ...
0 votes
1 answer
384 views

'Could not fsync file' while running VACUUM FULL on a table

I'm getting an error for around half of my tables in Postgres while trying to run VACUUM FULL. For example, I have a table named 'qwerty', and when i try to vacuum it i get the following error: ERROR:...
0 votes
1 answer
12 views

Passwordless access to psql from Administrator WITHOUT any password (Windows)

Yes, I know there are plenty of answers on how to access psql without password... while specifying password somewhere. I do not have context this luxurious. I do not have any password. What I do have ...
3 votes
1 answer
219 views

Postgres: Create unique index with conditional

I have a table in Postgres with records of type [event_id, user_id, status]. The user is scoped per event, so I have a unique index on [event_id, user_id] so that a user has a unique status. The ...
1 vote
2 answers
727 views

Postgres: Insert all valid data into a table while logging errors

I am attempting to devise a method that will enable me to load data into a table which will load all valid data and log the invalid records. Ideally, I'd like to do this without looping through the ...
0 votes
1 answer
519 views

Blocking automatic vaccum

I have a table message. I saw that queries for this table where long running and caused a memory leak (scheduled job in java app). In pg_stat_activity I saw: wait_event_type wait_event state IO ...
-1 votes
2 answers
13 views

Track row affected by a plpgSQL's DML from Python code block

I just wanted to know that how could I hold the value of a DML(affected rows) using python code. Suppose a DML is affecting 10 rows so how will I print it using python. db = psycopg2.connect(dbname=...
0 votes
1 answer
34 views

Why on earth is this INSERT statement failing?

I am trying the most basic INSERT statement on a table in a PostgreSQL database and running into an error (an error which mind you doesn't actually reveal the problem at all...) All I am trying to do ...
0 votes
0 answers
17 views

List all users and their roles - runs too slow

I am using this query to list out all users and their parent roles using Postgres 12.8 running on RDS. select distinct on (user_role_name) pgr.oid as user_role_id, pgr.rolname as user_role_name, pgr....
1 vote
1 answer
21 views

How do you run "SELECT" on the composite type argument of an SQL function?

Example: https://dbfiddle.uk/bCSwVpd9 Database: CREATE TABLE entities ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY ); CREATE TYPE entity_id AS ( id bigint ); Function: CREATE FUNCTION ...
2 votes
1 answer
21 views

What are the usecases for stored procedures (`CREATE PROCEDURE`) as of Postgresql 14?

I've had an impression it was some go-to way of encapsulating complex SQL logic. But digging related responses, such as this one, it doesn't seem to be the case. So are CREATE PROCEDURE-related tweaks ...
1 vote
2 answers
21 views

Duplicate key issue with a Foreign constraint

I have a database with the following tables: items, categories, and category_items. The (simplified, fields omitted) schema is as follows: CREATE TABLE IF NOT EXISTS items ( id SERIAL primary key ...
1 vote
1 answer
24 views

Cross join 2 table with json data and get all rows

I'm tryng to use a join function in my SQL request. I have two table : tbl_jsontesting ------------------------------------------------------------------------------------------------------------------...
-1 votes
0 answers
14 views

How to find time difference based on id

I have a selection from a database of this format How can I calculate the time difference by a specific orderid. I need to find the time difference across each order and statuses (eg time difference ...
0 votes
0 answers
28 views

Efficiently sort by a column whose sort order almost matches primary key in PostgreSQL

We have a table of events in PostgreSQL 14 that looks like this event (event_id integer primary key, event_time integer, ... other columns) event_id is automatically set from a PG sequence, so it ...
0 votes
1 answer
36 views

Expensive column causes slow query with large offset

I am attempting to do a query that retrieves an expensive column on a table with a large offset. However, as the offset gets larger the query goes slower and slower until it does not resolve in a ...
0 votes
0 answers
9 views

psql: FATAL: no pg_hba.conf entry for host "x.x.x.x", user "postgres", database "cis", SSL off

Please help to resolve below error. psql: FATAL: no pg_hba.conf entry for host "x.x.x.x", user "postgres", database "abc", SSL off pg_hba.conf: host all ...
6 votes
1 answer
195 views

What am I missing in my crosstab query here?

I am having difficulty understanding how to correctly transpose this table from a database I am using. This table was created by someone who designed the database years ago, but I managed to retrieve ...
2 votes
1 answer
348 views

Installing Postgresql parray_gin extension on mac, include not found

I am trying to install parray_gin in PostgreSQL 10 on macOS, but I can't get it to build. I'm running pgxn install parray_gin. This first gave me a missing stdio.h error, so I installed the xcode ...
2 votes
2 answers
29 views

For each row create a linked row in another table with auto-generated ID

In Postgres, I have an existing table: things thing_id thing_name destination_id 10 thing 10 null 15 thing 15 null For each row in that table, I want to add rows to two new related tables one of ...
1 vote
1 answer
438 views

postgresql 12.3 convert existing DB to use case-insensitive collation?

postgresql 12.3 in a container with Alpine Linux 3.10 I would like to convert an existing DB to use case-insensitive collation. Is it possible? I mean, without dropping it and recreating from scratch. ...
1 vote
1 answer
182 views

Massive slowdown after doing an ALTER to change index from int to bigint, with Postgres

I have a table like this: create table trades ( instrument varchar(20) not null, ts timestamp not null, price double precision not null, quantity double ...
0 votes
1 answer
2k views

"undefined function pg_pconnect()" when connecting to Postgres from CodeIgniter

I just installed Postgresql and pgadmin in my Ubuntu system. I can create a database from the pgadmin interface and everything seems ok, but when I try to connect to the database from my CodeIgniter ...
0 votes
0 answers
31 views

Datas on postgress logic replica are not visible

I'm trying to set up logical replication on postgress 13 and have a problem with accessing datas on replica. Publication and subscrinption is set up properly, I can see that successive pg_walls are ...
0 votes
1 answer
41 views

Make sure that such a field combination exists in another table when inserting

Table A: id | name | user_id Table B: id | type Table A_B id | table_a_id | table_b_id | user_id Table A_B is a standard linking table for a many-to-many relationship except that probably using a ...
12 votes
2 answers
13k views

PostgreSQL - Create view with autoincremental column

I have a PostgreSQL table, and I need to create a view with a new column. This column needs to be an auto-incremental column starting at 1 and going to N. Is this possible to do without effecting the ...
0 votes
0 answers
42 views

How to optimize insertion 10+ million data to PostgreSQL

I have a ticketing_system table and I try the insertion of 10+ million rows. So this cost 50 seconds. But I need almost a max of 10-15 seconds. CREATE TABLE ticketing_system ( ticket_id UUID not ...
38 votes
6 answers
87k views

pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists

I am using pg_dump / pg_restore to backup and restore a PostgreSQL database, but am getting some error messages (and a non-zero exit status) from pg_restore. I tried a super simple base case (outlined ...
2 votes
1 answer
2k views

Find tables with columns with empty and NULL values in Postgresql

After some research I found an example where I can find tables and its columns with NULL values. But the function also returns true when the column is empty. It gives me errors when I try to add an or ...
58 votes
3 answers
98k views

How to view data in pgAdmin III

I can't believe they make it this hard. I am at a loss about how to view the data in my database. Is there an easy way to see what data is in my tables with pgAdmin III? Alternatively, is there a ...
0 votes
2 answers
75 views

Wal position to wal archive number

I want to manage the copy of archiving to remote location. For that I want to delete all the archives I copied unless they are required by the standby. So, for running pg_archivecleanup $ARCLOC $...
0 votes
1 answer
33 views

Is there any impact on PostgreSQL database to run AUTO VACUUM frequently?

I have few(10-15) large table it is about 5GB to 30GB. And rest of them less than 1GB. Some tables data is huge and where daily 5lakh(500000) dead tuples generated. And autovacuum start almost 7 days ...
1 vote
1 answer
186 views

Reusing the same connection to read uncommited transaction

My question is similar to: how to spy on a transaction that is not yet committed in postgres for debugging What I tried: I start a pgbouncer instance, and set the max-db-connection to 1 From my node ...
11 votes
2 answers
14k views

Why wouldn't VACUUM ANALYZE clear all dead tuples?

We run a "manual" VACUUM ANALYZE VERBOSE on some of our larger tables after we do major DELETE/INSERT changes to them. This seems to work without issue although sometimes a table's VACUUM job will run ...
1 vote
1 answer
553 views

Django Multi Table Inheritance and Preserving Child and Child History

Place can have many different one to one fields, that’s why it can have bookstore, restaurant, and hardware store. But can the same place be both a bookstore and a restaurant at the same time? Now if ...
1 vote
1 answer
601 views

postgresql statement taking ages to cancel in pgAdmin III

I was running a query in pgAdmin III and it was taking a long time. After 9 minutes I decided to cancel it using the stop button. The UI stated that it was cancelling and stayed in that state for ...
0 votes
1 answer
16 views

How to filter a table remove duplicated keys and keep unique value for each key in one table?

The input data and output example as the picture. I tried to use pandas to filter the data after I try query: select * from tablename; is there any better way like only using a SQL query to do this ...
1 vote
1 answer
304 views

Prevent consecutive duplicate values without a trigger

Within a group, I'd like to prevent INSERTs of consecutive duplicate values, where "consecutive" is defined by a simple ORDER BY clause. Imagine a set of experiments which is regularly ...
1 vote
1 answer
27 views

Postgresql system tables frequently seen in autovacuum and DDL operations are taking time

I'm using Postgresql 11 on Azure and constantly seeing postgresql system tables showing up in autovacuum. I also see DDL statements take longer than what they used to (logged table or unlogged). Some ...
3 votes
1 answer
398 views

How to obtain the path to the match of a JSONPath query in PostgreSQL 14?

Here is a minimal working example that illustrates my point, the query finds all the values of keys named "$" at any level in the given JSON: select jsonb_path_query( $$ { "id": &...

1
2 3 4 5
306