Use this tag to indicate that your question is about PostgreSQL version 13. Questions concerning database administration should go to https://dba.stackexchange.com/
Questions tagged [postgresql-13]
368 questions
0
votes
2 answers
Ansible "apt cache update failed" during postgresql repo install
I'm trying to install postgresql on my remote host using Ansible.
I have 2 solutions, but both work only half as I expect them to work/turn out.
1. Solution one (using Ansible apt_repository module);
- name: Get Postgres latest repository release
…
Collega
- 424
- 6
- 12
0
votes
0 answers
Improve postgres v9.4 SQL return table function for postgres v13
We are in the process of migrating from PostgreSQL v9.4 to v13. In doing so we have to migrate all our functions too, The following FUNCTION issue a 42704 error stating that
record_type cannot be found in either prod or public. This function worked…
user9491577
- 1
- 5
0
votes
1 answer
PostgreSQL Distinct Sort For Huge Amount of Data
Here my query is:
explain(buffers, analyze) SELECT DISTINCT e.eventid, e.objectid, e.clock, e.ns, e.name, e.severity
FROM EVENTS e, functions f, items i, hosts_groups hg
WHERE e.source='0' AND e.object='0' AND NOT EXISTS
(SELECT NULL FROM…
Umut TEKİN
- 856
- 1
- 9
- 19
0
votes
0 answers
REGEXP_REPLACE for alpha to NULL
While converting Oracle to Postgresql I came to know the following Oracle query need to be converted in Postgres.
Oracle Query: Find pattern and replace with null
select regexp_replace('1', '[^0-9]', null) from dual;
select regexp_replace('a',…
MAK
- 6,824
- 25
- 74
- 131
0
votes
1 answer
PostgreSQL inefficient index is selected in sub-query
I've a query that for each row from campaigns gets the most recent row from effects:
SELECT
id,
(
SELECT
created
FROM
effects
WHERE
effects.campaignid = campaigns.id
ORDER…
Alechko
- 1,406
- 1
- 13
- 27
0
votes
1 answer
PostgreSQL : Difference between ItemIdData & CTID
According to postgres documentation :
ItemIdData is an Array of item identifiers pointing to the actual items. Each entry is an (offset,length) pair.
&
CTID is The physical location of the row. and this is also pair.
Please help to understand that…
Siddheshwar Soni
- 182
- 1
- 7
0
votes
1 answer
Get session number from namespace USERENV in postgresql
While migrating oracle to postgresql I came up with the query:
Oracle:
select SYS_CONTEXT('USERENV','SID') from dual;
What will be the equivalent in PostgreSQL?
MAK
- 6,824
- 25
- 74
- 131
0
votes
1 answer
psql: error: could not connect to server: Connection refused
For some reason I cannot connect to a postgreSQL server on my LAN. I can ssh into the box and connect using psql just fine. The server is running. But...
psql: error: could not connect to server: Connection refused
Is the server running on…
Rich_F
- 1,830
- 3
- 24
- 45
0
votes
1 answer
Postgresql 13 AlmaLinux Permissions Issue
I'm trying to get postgresql 13.x running on AlmaLinux. The package manage shows v13.x is available and I install postgresql-server:
sudo yum install -y postgresql-server postgresql-contrib
I then try to initialize the first db:
sudo…
Rich_F
- 1,830
- 3
- 24
- 45
0
votes
1 answer
How to set Postgres pg_wal as symbolic link
Using Postgres 13 on Ubuntu 21.10. I'm trying to move my pg_wal contents to a new disk, as the current one is full. I've moved the contents to a new disk, then created a symbolic link in the main postgres data directory. It shows up when listing the…
m.nicolau
- 47
- 5
0
votes
1 answer
Postgres 13.6 is using Sequential scan and Postgres 10.18 is using Index Only Scan
We have upgraded our db from version 10.18 to 13.6 and for simple count select I have noticed a different behaviour.
Postgres 13.6:
explain select count(id) from jobs;
QUERY…
Thomas
- 503
- 1
- 12
- 18
0
votes
1 answer
postgres 13.6 jsonb index not working on boolean type
I am using a boolean inside a jsonb field. What I find out is that when my boolean is a string "true" the index is being used but when i used a bool it is not. I am currently using postgres 13.6 (cloud SQL).
Here is how i am creating the NONE…
Jonathan Chevalier
- 993
- 1
- 9
- 18
0
votes
1 answer
Syntax to return results for a given time interval?
We just switched from MySQL to Postgres 13.6.
I am trying to return results for unpaid invoices that are between 30 and 59 days from invoice create date, so anything that has gone 30 and 59 days unpaid since being created.
In MySQL this was done…
gothxbrooks
- 1
- 1
0
votes
1 answer
PostgreSQL pg_upgrade error on pg_restore. "role '29648' does not exist"
When using pg_upgrade to upgrade PostgreSQL from 11 to 13 I receive the below error in step "Restoring database schemas in the new cluster":
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3801; 0 0 ACL FUNCTION…
TheEnglishMan_
- 33
- 10
0
votes
0 answers
How to update jarray in jsonb field?
I have a jsonb field that contained the something like below:
How to update is_read properties in extras node to true where the users_pid = 1 and is_read=false?
I have tried below:
UPDATE chats
SET attributes = jsonb_set(
…
Dev
- 107
- 6