Stack Overflow
Stack Overflow

Questions tagged [postgres-12]

32 questions
13
votes
3 answers

Get table size of partitioned table (Postgres 10+)

I came across this query on Postgres weekly which shows tables, their sizes, toast sizes and index sizes in bytes: SELECT relname AS table_name, pg_size_pretty(pg_total_relation_size(relid)) AS total, pg_size_pretty(pg_relation_size(relid)) AS…
Damir Ciganović-Janković
  • 821
  • 2
  • 12
  • 30
9
votes
2 answers

psycopg2.errors.ActiveSqlTransaction: CREATE TABLESPACE cannot run inside a transaction block

I am quite new to Python, we have an app container & a DB container. App container collects values like DB_HOST,PORT etc from CLI & try to create Tablespace on containerized Postgres DB running on same docker host. While execution the query we are…
Learner
  • 97
  • 1
  • 4
4
votes
1 answer

Postgresql wrong casting from float8 to decimal(24,8)

using Postgres 12.4 I found some inconsistencies rounding between float8 to decimal(24,8) By doing select 29314.630053404966::float8::decimal(24,8) as num1, 29314.630053404966::decimal(24,8) as num2 the return is: num1: 29314.63005341 ->…
Yaroslav Kolodiy
  • 121
  • 1
  • 2
  • 8
3
votes
1 answer

Postgres upgrade from 10 to 12: pg_upgrade fail for presence of required library

$ /usr/pgsql-12/bin/pg_upgrade \ > -b /usr/pgsql-1 pgsql-10/ pgsql-12/ > -b /usr/pgsql-10/bin/ \ > -B /usr/pgsql-12/bin/ \ > -d /var/lib/pgsql/1 10/ 12/ > -d /var/lib/pgsql/10/data/ \ > -D /var/lib/pgsql/12/data/ \ > --check Performing Consistency…
Shubham Singh
  • 141
  • 2
  • 7
3
votes
2 answers

B-tree index does not seem to be used?

I'm new to Postgres and I'm trying to understand indexes a bit more. I'm using version 12.5 and here is my code: CREATE TABLE textfun(content TEXT); CREATE UNIQUE INDEX text_b ON textfun(content); INSERT INTO textfun (content) SELECT (CASE WHEN…
Israel Phiri
  • 109
  • 1
  • 11
2
votes
1 answer

InterfaceError: cursor already closed

We have a Django 4.0.4 site running. Since upgrading from Python 3.10->3.11 and Psycopg2 from 2.8.6->2.9.3/5 and gunicorn 20.0.4->20.1.0 we've been getting random InterfaceError: cursor already closed errors on random parts of our codebase. Rarely…
Kushboy
  • 43
  • 1
  • 5
2
votes
2 answers

could not translate host name "db" to address: Temporary failure in name resolution

I am trying to build postgres with django from official docker documentation samples link nevertheless for unknown reason i am getting following error which i tried to solve without success. Please of help. This is output with error i got: ╰─…
Arie
  • 3,041
  • 7
  • 32
  • 63
2
votes
1 answer

When doing a database engine upgrade to PostgreSQL 12, should the Gemfile be updated for the 'pg' gem as well? How are they related?

Gemfile specifies gem 'pg', '~> 0.18' and the database engine is currently PostgreSQL 9.6 - which needs to be upgraded to PostgreSQL 12. Gemfile.lock includes pg (0.21.0) and pg (0.18.0). I don't have a good understanding of how these two are…
Mooqin
  • 29
  • 3
2
votes
1 answer

Postgres - query json with nested arrray and objects inside array

I have data stored in a Postgres 12 table as jsonb, the structure of the jsonb has an array inside an array. How do i get the values from the nested array? I can get the values from the first level array but not the second level array. This is a…
thehill
  • 93
  • 11
1
vote
0 answers

Postgres Copy command multiple delimiter

I am trying to use the copy command in postgres to upload the CSV file into the database using the below command psql -h 127.0.0.1 -d target -U postgres -c "\copy test FROM 'E:\pg_test\test.csv' delimiter ';'" Whereas when using the multiple…
Karthick88it
  • 601
  • 2
  • 12
  • 28
1
vote
1 answer

UPDATE with multiple JOIN on CASE

I am writing an UPDATE sql query within Postgres 12. The query is very complex and it needs to contain JOIN and CASE in order to work. I can not get my head around it so far. I need to update an approval_status column on the comment_response…
jabepa
  • 61
  • 5
1
vote
2 answers

Postgres Inheritance based partition scanning all the partitions

I wanted to implement partitioning via inheritance in Postgres. I implemented the below steps by referring to the Postgres article :- Created a master table called "test_table" CREATE TABLE kirana_customer.test_table ( col1 bigint NOT NULL…
animo3991
  • 181
  • 2
  • 9
1
vote
1 answer

pg_stats_activity.datname is null on one record

i'am using this query to determite hit/read value for my databases SELECT datname, CASE WHEN blks_read = 0 THEN 0 ELSE blks_hit / blks_read END AS ratio FROM pg_stat_database; select * from pg_stat_database; i have…
Drilla
  • 109
  • 8
1
vote
2 answers

Bad estimates in Postgres when joining tables

Currently I'm facing a problem where Postgres's query planer makes bad decisions based on (what I think) seems to be bad estimates, i.e. in a larger query the query planer chooses to do the (hash) join from this post as it's first/inner-most part…
Oliver Saggau
  • 5
  • 4
0
votes
0 answers

Logical replication fails at failover because of sequence

I have two servers A and B, server A is the publisher for table with primary key sequence. Before i switch to server B, I update the sequence in server B to be same as server A. Then I do the failover and make server B the publisher and server A the…
Yassin Shanwany
  • 56
  • 5
1
2 3