Use this tag to indicate that your question is about PostgreSQL version 14. Questions concerning database administration should go to https://dba.stackexchange.com/
Questions tagged [postgresql-14]
227 questions
0
votes
1 answer
How to handle null and text values when finding average in postgres?
I have a table that looks like below (only a sample provided)
date | memory | cpu
----------------------------+-----------+----------------
2022-10-06 02:03:30.102194 | 37 | -1
2022-10-06…
Souvik Ray
- 2,899
- 5
- 38
- 70
0
votes
0 answers
hello i was trying to install postgis to one of my database with the following command CREATE EXTENSION postgis;
CREATE EXTENSION postgis; but it returned an error of ERROR: could not open extension control file "/Library/PostgreSQL/13/share/postgresql/extension/postgis.control": No such file or directory
i'm on mac OS
Postgresql 14 and PGadmin 4 and have…
Azeez Soliudeen
- 29
- 4
0
votes
0 answers
Creating procedure (that should be a function) in Postgresql and getting error message when calling procedure
I am new to Postgresql/coding in general, so please forgive me for this question. Yes, I have searched SO for the answer, as this is seemingly a common error message, but I keep coming up empty-handed.
I am having issues trying to create a table and…
CleverCacti
- 1
- 1
0
votes
1 answer
More than one row returned by a subquery with triggers
I am looking to subscribe my code to listen to insert events produced by postgres. My frontend can detect insert queries to a specific table, but when I try to return a payload I get two types of errors.
I removed some business logic, so it might…
0
votes
0 answers
ERROR: attempted to read an unexpected stripe while reading columnar table daily_p2022_10_11, stripe with id=281134 is not flushed
I am getting this error in postgres alert log:
ERROR: attempted to read an unexpected stripe while reading columnar table daily_p2022_10_11, stripe with id=281134 is not flushed.
While trying vacuum, getting below :
ccap_proddb=> vacuum verbose…
0
votes
1 answer
How to get partitioned tables' partition keys
select
relname as partitioned_tables
from pg_class
where relkind = 'p';
I get partitioned_tables with the code.
I need to get information about these tables' partition types (range,hash,list etc.) and partition column names.
Solution:
select…
Deniz Gölbaş
- 13
- 4
0
votes
0 answers
How to reuse old phiscal PostgreSQL drive after ubuntu drive crashed and reinstalled
I've got a huge database (2.6TB) installed over a 4TB drive.
I had to reinstall Ubuntu 22.04 after the main drive crashed (not the database drive).
Now I'm trying to reinstall PostgreSQL and reuse the old drive, however, I failed to find how to do…
Ze'ev Ben-Tsvi
- 1,174
- 1
- 3
- 7
0
votes
1 answer
How query bit operator in postgresql jsonb column
I have a table as follows in PostgreSQL:
id | access
--------------------------
1 | [{"id": 1, "user":"user1", "permission": 1}, {"id": 2, "user":"user2", "permission": 3}]
2 | [{"id": 1, "user":"user1", "permission": 3}, {"id": 2, "user":"user2",…
Morteza Malvandi
- 1,656
- 7
- 30
- 73
0
votes
1 answer
Optimize Postgres query, taking time to execute
I have some sql query that taking time to execute. Is there anyway I can optimize, which will takes few ms to execute.
select
item0_.dim_Item_ID as dim_item_id ,
item0_.description as description ,
vendorfact1_.vendor_id as vendor_id,
…
Subhamoy Ghosh
- 79
- 9
0
votes
0 answers
Caused by PG::InvalidObjectDefinition: ERROR: infinite recursion detected in rules for relation
I have a view which is not updatable one, so I have created a rule after getting the below error
ActiveRecord::StatementInvalid: PG::ObjectNotInPrerequisiteState: ERROR: cannot delete from view ""
DETAIL: Views containing GROUP BY are…
Kiran Kumawat
- 82
- 1
- 10
0
votes
1 answer
How do I solve SQL Scan error on column in Go?
Technologies used
Go
Gorm
PostgreSQL 14.5 (In Docker container)
OpenAPI
oapi-codegen package v1.11.0
I am building an API for CRUD operations on Personas from the Shin Megami Tensei Persona spin-off series of games. I have an issue when trying to…
Bradley Marques
- 129
- 1
- 16
0
votes
0 answers
how to view WAL logs of GCP Postgres SQL instance (cloudsql)
try to find out wal log path ? and how i can export it ?
gone though official doc -https://cloud.google.com/sql/docs/postgres/replication/configure-logical-replication
tried firing Select * from pg_current_wal_location(); but getting error.
0
votes
1 answer
PgBouncer configure auth_user without plain text password
I am working on configuration of pgBouncer and I was able to make it work with auth_query. Is it possible to somehow encrypt password for auth_user?
Initially I tried to use SCRAM hash but then connections would fail. I've read that md5 is possible,…
gluetornado
- 35
- 5
0
votes
0 answers
Rollup result set with custom condition - rollup by multiple columns and N 'latest' events
I want to count certain Esports events rolling up results by tournament, tournament stage, last 5 and last 10 games, all matches.
I'd like to get a single query that I could put into materialised view - new events happen not too often, so even a few…
Bohdan Shulha
- 74
- 10
0
votes
0 answers
Getting output in table format using procedure/function in plpgsql
I've two tables one with product_name and product_id(table1) and other one with product_id and sale_amount(table2).
I need to write a procedure in postgresql(version 14.4) which should have product id as parameter. So whenever required I call…
MANOHAR N
- 26
- 5