All Questions
Tagged with postgres-13 or postgresql-13
399 questions
0
votes
1
answer
164
views
Postgres Select count(*) taking many minutes
I am running select count() against large table in postgresql, over 10 billion rows. The count() query is taking over 25 minutes to return. Slow is one thing, but that extraordinary and leads to to ...
- 1
0
votes
1
answer
248
views
Postgres global .conf file
I manually built postgresql-13.11 on an m1 mac.
When attempting to run either /path/to/postgres/bin/postgres (same for postmaster), i get the following error:
postgres does not know where to find the ...
- 64
0
votes
1
answer
339
views
Timescale DB Create distributed hypertable
I want to create a PoC of a distributed hypertable, using TimescaleDB and docker compose.
I've set up a docker compose with two instances and created the respective table in both instances.
Let's say ...
- 168
0
votes
1
answer
39
views
Why is postgres update not limited to the rows in the cte
This is PostgreSQL 13.10...
This question is about using UPDATE with a CTE on a VIEW (though I tried eliminating the VIEW and still have the same issue).
I am using a REST API frontend that generates ...
- 6,886
1
vote
0
answers
306
views
PostgreSQL query on union - filter condition not being "pushed down" into subqueries resulting in much worse performance
I'm using postgres version 13.3, and I have multiple views, each constructed with a join between a (foreign) data table and a shared metadata tables that serves as a lookup.
each view is constructed ...
- 410
2
votes
1
answer
2k
views
How to install pgcrypto extension on postgres13 on MacOS
I'm using postgres13 and running a somewhat old ruby on rails project, it has a few migrations and one of the first lines on those migration files is:
enable_extension "pgcrypto"
All the ...
- 102
0
votes
1
answer
1k
views
The data directory was initialized by PostgreSQL version 12, which is not compatible with this version 13.10
I am trying to run postgres:13-alpine in Kubernetes cluster.
I have next Kubernetes manifest
PV
kind: PersistentVolume
apiVersion: v1
metadata:
name: open-imis-pv-volume
labels:
type: local
...
- 525
0
votes
1
answer
937
views
How to call function with a custom type array as its parameter from the SQL editor
I have create a custom type like this:
CREATE TYPE my_schema.sample_type
AS
(
id int,
categories int[],
body text
);
This type is used as the array parameter in my function:
CREATE OR ...
- 8,187
0
votes
0
answers
69
views
Is refcursor working in postgresql 13.2 version?
IN my postgresql 13.2 version database, the "refcursor" is not working.
when am i storing the select query output to refcursor variable, it is throwing below error.
create function testing(...
0
votes
2
answers
1k
views
AWS replication failure - could not access file "pglogical"
After 2 months running without problems, my AWS DMS task failed with the following error:
Last failure message
Last Error Stream Component Fatal error. Task error notification received from subtask 0, ...
- 7
3
votes
1
answer
1k
views
Postgres is taking too long to attach partition to a table. Want to understand why
I have a table T1(non-partitioned). Size of T1 is approx 4TB. I have created another table T2(partitioned table). Now I want to attach T1 as child table of T2. So I am running below query to achieve ...
- 33
0
votes
1
answer
55
views
Getting related many-to-one objects with a single query
I have three related tables in PostgreSQL:
objects - top-level table, that has an ID and a name.
object_events - a related table, that has a foreign key to objects
object_aux_data - a table that uses ...
- 3,509
0
votes
1
answer
250
views
Postgresql - How to query nested array elements
I have JSON array data in PostgreSQL 13 table. I want to query this table to see all the nested array data in the output. I tried the below query, but it's not giving the expected output.
select
...
- 907
0
votes
1
answer
466
views
How to enable writing option in replica server postgresql-13?
I have 2 DB servers: 10.1.1.49 - master and 10.1.1.68 - slave. First is prod and the second one is dev server respectively. I need to replicate data from prod server to dev server, also I need to able ...
0
votes
0
answers
844
views
Postgresql - How to truncate table and insert rows in same table within a single transaction
I want to truncate a table in PostgreSQL 13 and then insert rows to the same table within a single transaction, so that when I execute the SQL command it will TRUNCATE the table and if during INSERTS ...
- 907
0
votes
1
answer
585
views
upgrading postgres 11 to 13 fails in consistency check
when i try to upgrade Postgres from version 11 to version 13 using this command:
/usr/lib/postgresql/13/bin//pg_upgrade
-b /postgresql/11/bin/
-B /postgresql/13/bin/
-d /data/service/postgres.1061/...
3
votes
1
answer
4k
views
Postgres - how to add a partition to a table that has a DEFAULT partition, without holding AccessExclusiveLock?
Specifically, this section of the postgres doc doc says I should be able to avoid a scan of the default partition:
Before running the ATTACH PARTITION command, it is recommended to create a CHECK ...
- 3,077
0
votes
0
answers
142
views
How to pass pg-promise transaction/task between ExpressJS middlewares?
ExpressJS can have a chain of middlewares for a single route, which at the very least allows to split input validation logic from the actual handling of the inputs.
The usual pattern looks like this:
...
- 320
0
votes
1
answer
820
views
Cannot drop schema because schema does not exist but it exists in pg_namespace
I am having a problem with dropping schema to create it again.
When I run:
drop schema 'schema_name' cascade
I get the error message saying "schema does not exist".
But when I search ...
- 3
0
votes
0
answers
210
views
Connection to server at port 5436 failed running on docker
I am trying to install pgAdmin4 in my windows machine. I am getting error message of connection to server failed.
I found a similar kind of issue here but the solution doesn't work. I have been ...
- 364
2
votes
2
answers
982
views
PostgreSQL: increment column value in select statement based on previous values
I am using Postgresql 13 and I have a table that looks something like this:
event_id
timestamp
1
2022-11-28 00:00:00
1
2022-11-28 00:00:10
2
2022-11-28 00:00:20
2
2022-11-28 00:00:30
2
2022-11-28 00:...
- 33
3
votes
2
answers
420
views
postgresql psql command \d does not list indexes
I created a table in PGSQL (version 13) using the following command:
db1=# create table temp2(
foo int PRIMARY KEY,
bar varchar(20) UNIQUE NOT NULL
);
CREATE TABLE
The \d or d+ command does not list ...
- 3,702
0
votes
2
answers
335
views
How to change Odoo Logo on POS - Odoo 16 installed on Docker?
I try to find the location of the Odoo logo on POS, but I could not find it any where in my VPS.
Search the answer online and found out that I could change it in location, but I have not found the ...
- 11
0
votes
1
answer
43
views
name list in PostgreSQL 13.0
list the name autors with the first name chris by alfabetic order
Tabela ESTRELAS
Nome da coluna Tipo Descrição
id INTEGER id do gênero
nome_estrela VARCHAR Nome da estrela de cinema (atores e ...
- 3
1
vote
0
answers
801
views
Encountering failure during restore of TimeScale (Postgres) database
I am trying to restore a sizeable dump file (100GB) from a TimeScaleDB (Postgres) database, but the restore appears to catastrophically fail after a few hours with the following error:
pg_restore: ...
- 31