Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
15,276
questions
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 ...
- 883
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 ...
- 101
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 ...
- 113
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
('...
- 23
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 ...
- 189
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 ...
- 1
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 ...
- 575
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 ...
- 3
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:...
- 109
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
...
- 281
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,
...
- 123
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