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

Detect corrupted Indexes

How to detect corrupted indexes in PostgreSQL? When I pull data from a column with primary key attribute in a table with where condition, it brings incomplete results. Likewise, when I sort with order ...
0 votes
0 answers
24 views

Reducing Pg_largeobject size in AWS RDS

We have RDS PostgreSQL database originally ~35GB (now extended to 60 GB). All our core tables are not occupying more than 20MB space but our Pg_largeobject occupies ~35GB though we are not having any ...
  • 1
1 vote
0 answers
49 views

Root cause of Postgres database corruption

I've been suffering from occasional (but regular) database corruption recently. Errors like this. compressed lz4 data is corrupt and Npgsql.NpgsqlException: invalid page in block 1787279 of relation ...
0 votes
1 answer
34 views

Calculate the number of days that the field value has not changed

Please consider the following table: User Date amount John 1/1/2022 40 John 1/2/2022 -30 John 1/6/2022 50 ... ... ... Jack 4/1/2022 80 Jack 4/1/2022 -60 We have three records for John and two ...
  • 115
0 votes
0 answers
20 views

Is there a way to UPSERT an Oracle Foreign Table with Foreign Data Wrapper from PostgreSQL?

I need to transfer lots of row from my PostgreSQL database to a remote Oracle DB through the Oracle Foreign Data Wrapper (FDW). The preferred way to do it is by UPSERT. However as INSERT INTO ... ON ...
  • 179
1 vote
0 answers
22 views

Can the citus data coordiator and worker node be on the same machine?

Reading citus data's tutorial, I was wondering if in a small cluster, I can have the coordinator and one of the workers installed on the same machine? I am testing this on a small scale cluster with ...
  • 2,930
0 votes
1 answer
72 views

Add value on one column based on values of a group of rows

Imagine something like "uber Eats": there is a driver's base (mall), other restaurants outside the mall and customers locations. Each row is a travel between two points (base, restaurant_# ...
  • 15
0 votes
0 answers
34 views

How to create an index for exact string search in PostgreSQL

I use PostgreSQL 14, and I have a table responsible for storing all session IDs that are created for users of an account. As a user can do many logins, over a long period there can be thousands of ...
  • 315
0 votes
1 answer
18 views

How to change all similar rows in PSQL if one value is changed?

I have a table like this: name email domain rank contact 1 email website.com null contact 2 email website.com null contact 3 email website2.com null The first two contacts are from the same ...
  • 1
0 votes
2 answers
84 views

Restrict delete statement on certain table in postgres?

I have a problem where my customers are complaining that records get deleted even though I see all id's are correct in that table, just to make sure everything is like it should be, is there a way to ...
0 votes
0 answers
30 views

Compare two tables - GPS points

postgresSQL 13.6 no query built yet, as this is a initial concept question. Two tables, each with a timestamp and lat and lon columns plus one ID column (not anywhere common between tables), ...
  • 15
0 votes
0 answers
60 views

Delete rows from multiple tables with foreign key constrain

I have a a database with tables worker_info, worker_skills, worker_url, worker_joining_date, worker_leaving_date, etc. The worker_info had a column called id which is foreign key to other tables.There ...
  • 1
0 votes
1 answer
22 views

Create a schema for each user in a group

I'm trying to find a way to create a schema within a database, for each user in a group... The goal is to automatize the creation process for a structure in a school. Say, each class is a database, ...
0 votes
0 answers
17 views

Postgres composite PK where one component is sequence different for each value of other field

Let's say I have a a_table with following fields: id_a: bigint id_b: int some_third_value: text I'd like to create a composite primary key on id_a and id_b so that id_a would be a sequence, but it ...
  • 101
0 votes
1 answer
44 views

VACUUM FULL using another disk

Current database takes almost all of SSD free space ~802GB. 800 GB of memory is taken by single table and it was never vacuumed since 2019. Recently i deleted old rows from the table and now i need to ...
0 votes
3 answers
125 views

Why is my big Postgres query faster if I separate it into 3 smaller queries?

I have a big/complex query (with an OR clause and 2 subqueries that have multiple joins and an IN condition). Executing the big query is slow. However, if I break my big query down into 3 smaller ...
0 votes
1 answer
42 views

Import postgresql database with Latin encoding

I'm going to upgrade from postgresql 9 to postgresql 11, the encoding used is Latin. I've exported the database using this command pg_dumpall | gzip > /tmp/db.sql.gz I extracted the exported ...
1 vote
2 answers
59 views

What does it mean by PostgreSQL's ERROR: schema XX is not a member of extension YY?

I have two PostgreSQL (14.5, Ubuntu 22.04) extensions A and B that each creates some functions in a common schema schm. Now I am having trouble re-creating the two extensions as there seems to be ...
  • 2,930
1 vote
1 answer
47 views

Foreign Key - from same table and parent table

I have 2 tables. First table is parent COLLECTIONS_OF_MAPS id title 50013 Geological Map Series 50014 Climate Map Series Second table is a child (but whilst describing map layers, it also has ...
  • 155
0 votes
1 answer
46 views

How to join two tables A and B and have the most recent status from B?

I have two tables: A and B A provider_id date 111 date 222 date 333 date B provider_id status date 111 initialized date 111 released date 222 failed date The result I want provider_id ...
  • 103
1 vote
1 answer
29 views

How do you write ordered multi-inserts wih CTEs?

With the table like this: CREATE TABLE test_1 ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY ); CREATE TABLE test_2 ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY ); CREATE TABLE ...
0 votes
1 answer
20 views

Archive failed but new WAL never generated

Currently I am experiencing a weird situation. Archive failed_count soaring from 0 to 2475. Here some parameter that I check : select * from pg_stat_archiver; archived_count 3567; last_archived_time ...
  • 43
-1 votes
1 answer
32 views

Convert the categorical values in a column into columns themselves (Transpose)

Experts, I have this data - ID | buckets | SUM (USD)| --------- --------------- --------- 1 | 10 - 20 | 500 | --------- --------------- --------- 1 | 30 - 40 ...
  • 23
0 votes
1 answer
27 views

Postgres 11 query shows blank rows due to NULL values

Issue: Executing a query that generates INSERT statements to migrate data from Postgres to Oracle. Results show the expected number of rows, except more and more rows are blank. NULL values in the ...
  • 1
1 vote
1 answer
37 views

How do I bulk export data from an RDBMS without killing performance?

I'm building an app that lets the user export their data. When they click the Export button, we need to do a SELECT * WHERE user_id =, save it to a CSV file, and download it. There may be hundreds of ...
0 votes
0 answers
20 views

Is it possible to add encryption capabilities to an already existing table?

I'm using pgcrypto module for PostgresSQL, and in the document it was indicated that this line: CREATE EXTENSION pgcrypto; should be executed before creating the table which stores encrypted data. ...
1 vote
1 answer
130 views

Add key and value to elements of a jsonb array where the key does not exist yet

I have a table tbl with the following structure: CREATE TABLE tbl ( org text , data jsonb ); The data in the jsonb field is an array structured in the following way: INSERT INTO tbl VALUES ('...
0 votes
0 answers
40 views

PostgreSQL — considerations when adding new values to rows with a long-running migration

I have a table of ten columns containing 370 million rows and I need to add six new columns to it. Some new columns will take null values, others will be defined with NOT NULL DEFAULT. After altering ...
  • 101
0 votes
0 answers
17 views

How to skip over a constraint record and continue?

I have a script that copies records from one psql DB (origin db) to another (destination db). At the moment, the script gives me an error because in the destination DB, one of the columns is set to ...
  • 1
0 votes
0 answers
23 views

-- lateral injecting a strange outer join condition

dbfiddle code source: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/join.sql;h=7f866c603b8d65524584f16ae96205e1dad32e0b;hb=9eacee2e62d89cab7b004f97c206c4fba4f1d745#...
  • 163
0 votes
0 answers
14 views

PostgreSQL text search with negation context

We have a managed PostgreSQL database on AWS (RDS) and we use it for text search using to_tsvector and phraseto_tsquery with the english configuration. All functions properly except from when looking ...
  • 103
0 votes
1 answer
38 views

Add row from table B based on minimum distance between table A and B

I am a beginner with PostgreSQL and have two tables A and B, both with multiple columns, including a point column (geom): TableA includes a given 'ID', geom, etc , TableB is a "DB" of ...
  • 15
-1 votes
1 answer
52 views

count the number of people who like me, number of people I like, and the intersection

i have two tables users and votes suppose I have a row in the votes table where uid = alice b_uid = bob liked = 1 this means alice likes bob I want to count the stats for a user Charlie, i.e. how ...
0 votes
0 answers
37 views

PostgreSQL as an external data source

I'm trying to get an external data source to work with PostgreSQL. This is what I've done so far: Downloaded and installed the latest 64bit PostgreSQL ODBC drivers Created a System DSN using the ANSI ...
0 votes
0 answers
99 views

pg_dump permission denied for relation error for owner user

I am attempting to dump out a PostgreSQL database with pg_dump: /usr/bin/pg_dump -f /home/crmpicco/rfc_754622.sql -F c -x -E UTF8 -b --disable-triggers -U root -h tillman.ff1lvb.us-west-2.rds....
  • 145
-1 votes
1 answer
52 views

pg_restore relation error

I am trying to run pg_restore on a file so I am able to use it in postgres. The file is a psql file. I try the command: pg_restore --no-privileges --no-owner -d test backupfile.psql -c -U postgres ...
0 votes
1 answer
52 views

Simple join on large table is very expensive

I have two tables, a (id, timestamp) and b (id, value). They have equivalent number of rows (in fact b is materialized view of a and several other tables). There are around 1.3m rows on each table. I ...
  • 101
0 votes
0 answers
19 views

PostgreSQL replication checking pg_stat_subscription more than 1 row

I'm new to DB replication. I performed a replication task on 3 production DB. On the first 2 DBs (low size) on the subscriber side, they only have 1 row result when i query pg_stat_subscription. On ...
0 votes
0 answers
43 views

Subtracting preceding cumulative values from current value using SQL

I have the following table of distances: loc_id distance 2 1844 3 4781 4 12678 and would like to subtract the cumulative sum of the preceding distance rows. loc_id distance between_dist 2 1844 ...
  • 111
0 votes
0 answers
73 views

postgres 11: remaining connection slots are reserved for non-replication superuser connections

I've run into the following problem. From time to time, my app receives remaining connection slots are reserved for non-replication superuser connections from postgres. It lasts for quite noticeable ...
0 votes
1 answer
25 views

Postgres FDW: Is it possible to execute a foreign function in a foreign server after importing the foreign schema?

I imported a foreign schema using IMPORT FOREIGN SCHEMA. Now I'm able to access all the table content in that schema, but still unable to access any of the functions. Is it possible to execute a ...
1 vote
0 answers
44 views

Why this aggregate function cannot be converted with pg_upgrade from PostgreSQL 9.5 to PG 14.3?

I am trying to port old PostgreSQL v9.5 database (or family of databases) to PostgreSQL v14.3, with pg_upgrade utility. After many hassles with old v9.5 binaries, source files (had to be recompiled to ...
  • 23
0 votes
1 answer
61 views

logical replication initial phase

I am about doing replication for 16 millions record. Just want to ask about the initial phase of logical replication : Do I need to copy the initial table from publisher to subscriber first ? Or is ...
  • 43
0 votes
1 answer
32 views

PostgreSQL size of used shared buffer vs actual result size

I am debugging a production issue where a regular index scan used in a join reports very high buffers usage (sometimes gigabytes) when inspecting using explain (analyse, buffers). Because it's reading ...
0 votes
0 answers
27 views

How to consider special characters in ts_query and ts_vector as plain english

How to include special characters &, *, / in to_tsquery and to_tsvector . It converts boyce & doyl as {boyce:1 , doyl:1}. How to treat them as char so they can be searched in a query ?
  • 1
0 votes
1 answer
35 views

How are HashAggregate row estimates calculated?

I have the following query: SELECT foo.* FROM foo WHERE foo."id" IN ((SELECT bar."id" FROM bar WHERE bar.user_id IN ( 100, 102 )) ...
  • 207
0 votes
0 answers
30 views

Can't cancel own query in AWS Aurora PostgreSQL 10.18 using pg_cancel_backend

I'm trying to cancel queries in AWS Aurora PostgreSQL 10.18 when logged in as not the master user, using pg_cancel_backend. However, when I run SELECT pg_cancel_backend(1234) say, then I get the error:...
0 votes
1 answer
74 views

UNION types text and jsonb cannot be matched PostgreSQL

I have 3 tables on which I am trying to union all, one of the tables contains many jsonb columns. I am doing this query, CREATE OR replace view datasets_dashboard AS WITH core AS ( SELECT ...
2 votes
1 answer
43 views

Aggregate query for pairs of arrays of events

db<>fiddle for all of the data and queries below I have a table events with the following structure: create table events ( correlation_id char(26) not null, user_id bigint, ...
1 vote
1 answer
29 views

How could this UPSERT query be made shorter?

I have a table storing the result of some simulations. Besides the few columns that identity the row, every single column in the row is getting replaced at each update. The query feels quite long and ...
  • 217

1
3 4
5
6 7
306