602 questions
1
vote
1
answer
757
views
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;
...
- 119
0
votes
0
answers
1k
views
Postgres 9.6 to 12.7 pg_upgrade error - terminating connection due to administrator command
I am currently upgrading a Postgres cluster running on a Centos machine from version 9.6.22 to 12.7. Post the install of 12.7 binaries, I am running the pg_upgrade command:
/usr/pgsql-12/bin/...
- 75
11
votes
3
answers
11k
views
How to list all indexes of a table with their corresponding size in PostgreSQL?
I can view the total size of all indexes in a table with
SELECT pg_size_pretty (pg_indexes_size('table_name'));
and the size of a specific index with:
select pg_size_pretty(pg_relation_size('...
- 389
0
votes
0
answers
88
views
Updating sequences for a single schema not working (Postgres 12)
Postgresql12
I am trying to fix a schema's sequences, but for some reason this yields attributes from other tables in some cases. I assume I am leaving out something in the where clause that isn't ...
- 634
3
votes
2
answers
2k
views
How to remove first row in every grouped result ordered on date column in postgresql
I have a sql data (table) which consists of n number of columns, but 3 are of particular interest
claim_id
patient_id
admission_date
1
p_1
2001-01-01
2
p_2
2001-02-01
3
p_1
2001-01-01
4
p_3
2001-02-02
...
- 26.3k
2
votes
3
answers
2k
views
How to compress postgres database backup using barman
We are backing up 1TB+ of postgres database using barman. Barman is backing up full database daily. Every day we are dumping 1TB+ database. So it's consuming huge space.
We have following ...
user9891377
1
vote
1
answer
402
views
What is the alternative of PL/Java for PostgreSQL 11 and 12?
Understand from: https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/user-guide/11/EDB_Postgres_Advanced_Server_Guide.1.80.html
that PL/Java is deprecated in Advanced ...
- 11
0
votes
1
answer
417
views
Rails & Postgres selecting sub keys of a JSONB column
I have a column of a database table that is of type JSONB and I'm wanting to get some data from that column. For the most part the column is a flat list of key \ value pairs.
Ex:
{ s_key: 'value', ...
- 1,288
0
votes
1
answer
76
views
Postgresql: handle all queries to change user's password?
I have to log all such queries to my database on Amazon RDS
The best way, of course, create a trigger on pg_catalog.pg_authid, but I have no privileges to operate system catalog.
Any advice?
- 316
1
vote
1
answer
2k
views
PostgreSQL "duplicate key violation" with SEQUENCE
[Issue resolved. See Answer below.]
I have just encountered a series of “duplicate key value violates unique constraint” errors with a system that has been working well for months. And I cannot ...
- 303
1
vote
1
answer
276
views
How to query for a zero-byte char?
According to the documentation, pg_attribute.attgenerated is typed as char and has a value of "a zero byte" if the column is not generated, and there is at least one other possible value, ...
- 85k
0
votes
1
answer
421
views
How to distinguish between real NULL and formal NULL in Postgres ROLLUP?
I am trying to determine what "level" of a ROLLUP report each given row belongs to. When initial data contains no NULLs, then it is possible just count nulls in every row (amount of null ...
- 7,895
4
votes
1
answer
3k
views
How to set PostgreSQL script parameters in DBeaver or PgAdmin?
PostgreSQL v12.6 DBEaver 7.1.0
How to set SQL script parameters in DBeaver or PgAdmin?
(any working option is welcome)
This script works perfectly when I run it as a script on DB initialization
(i.e. ...
- 7,895
0
votes
1
answer
116
views
Update records by joining local and foreign db using db link
t - current db table
table1 - other db
database - current db table where the "other db" credentials are stored
SELECT
t.*
FROM "database" d, t,
dblink_exec(
...
- 147
0
votes
1
answer
39
views
SQL query Postgres 12
I'm doing an inner join on a table like this:
SELECT *
FROM patient p
INNER JOIN
vaccine v
ON
p.vaccine_id = v.id
The condition f.vac_1 = mv.id might not been satisfied in the case ...
- 2,145
2
votes
1
answer
2k
views
Can you logically replicate a physical Postgres replica?
The title is pretty much self-explanatory. To illustrate more, here's a picture:
[Live Database]-->(physical replication)-->[Physical Replica]-->(logical replication)-->[Logical Replica]
...
- 111
3
votes
2
answers
702
views
How to convert PostgreSQL 12 generated column to a normal column?
I have a generated column in PostgreSQL 12 defined as
create table people (
id bigserial primary key,
a varchar,
b boolean generated always as (a is not null) stored
);
but now i want column ...
- 6,587
1
vote
1
answer
2k
views
Import .sql file to a specific schema (not in public schema) PostgreSQL 12
sudo psql -f sample-schema.sql -U postgres -p 5432 -h my ipaddress -d dbname
How to specify into what schema the data go?
- 105
5
votes
2
answers
3k
views
Are hash indexes the most optimal for exact text match queries as of Postgres 13?
Example (fake) case: indexing a column containing e-mails (text type) when the column will exclusively be queried for an exact string match, ex.
SELECT * FROM mytable WHERE email = '[email protected]'
Do ...
- 389
1
vote
2
answers
225
views
POSTGRES PROCEDURE with wildcard
I am trying to create a procedure with a wildcard with postgresql unfortunately I do not have any success. It looks like the postgresql versions have different ways of creating procedures, however, I ...
- 622
1
vote
1
answer
357
views
Why my newly created postgres RDS has constant write IOPS?
Why my newly created postgres RDS has constant read/write IOPS plus CPU utilization? I didn't use the rds in any of my app, nor does it open to public network.
And I am just using the default param ...
- 380
1
vote
1
answer
2k
views
docker and bitnami/phppgadmin: How to connect to the remote postgresql database
I am trying to connect to a remote postgresql database using the bitnami/phppgadmin docker
How to mention the host name
phppgadmin:
image: "bitnami/phppgadmin:7.13.0"
ports:
-...
- 12k
0
votes
1
answer
556
views
ERROR: current transaction is aborted, commands ignored until end of transaction block when trying to import a raster image to postgis
I am getting the following error when trying to import a raster image to a PostGIS DB using raster2pgsql.
ERROR: current transaction is aborted, commands ignored until end of transaction block
I have ...
0
votes
2
answers
2k
views
Query Postgres Timestamp with Timezone wrong result
I have a table with a time column defined as
time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
my database and the server have the timezone UTC
Tue Apr 20 18:22:50 UTC 2021
When I ran the following query
...
1
vote
3
answers
283
views
How can I define a plpgsql function that accepts a parameter of a type that is not schema qualified and is not yet created
My database is structured with a schema per application user. In each schema there is an identical table named "entries" with the exact same DDL. I also have a common schema that hosts some ...
- 6,264