Questions tagged [postgresql-9.6]
PostgreSQL version 9.6
513 questions
0
votes
2
answers
295
views
unable to extract json column data (posgres 9.6)
I can run below query to get the data in given format as:
SELECT to_timestamp(unnest(ARRAY[[1725042600, 1725043500], [1725041700, 1725042600], [1725043500, 1725044400], [1725040800, 1725041700]]));
...
1
vote
1
answer
78
views
Why does ORDER BY significantly slow down my query with a computed score of trigram similarity and null field?
I'm working on optimizing a query in PostgreSQL, and I've encountered a performance issue when using the ORDER BY clause. The query is intended to search profiles based on a similarity match to a name ...
0
votes
1
answer
145
views
UNIQUE CONSTRAINT on two columns, but duplicates appeared: how to track down and fix? [duplicate]
We have table for users:
CREATE UNIQUE INDEX uk_users_login_socnet ON public.users USING btree (login, socnet)
We had maintenance on our server, during which it was backed up and replicated to ...
- 129
0
votes
1
answer
247
views
Better way to return list of FK "parent" tables?
Here's what I currently do in Postgres 9.6. (I know it's EOL; that's not my choice to make.)
I don't want the definitions, FK names, or any of the other clutter you get with \d <table>. Just ...
- 694
1
vote
2
answers
5k
views
Performance difference using CTE and subquery
I have run the following queries.
CTE
WITH temp AS (SELECT id, timestamp FROM table group by timestamp, id)
SELECT COUNT(*) FROM temp WHERE timestamp = '2023-01-01 08:28:45'
Subquery
SELECT COUNT(*) ...
- 141
0
votes
1
answer
839
views
Fix invalid checksum in database that causes pg_dump to fail
I have a Postgres 9.6 database that is corrupted. The hardware it was running on went bad, and there is at least one corrupted page in the DB. When running pg_dump I get the following error:
pg_dump: ...
- 117
4
votes
1
answer
8k
views
(PostgreSQL CREATE TRIGGER) Is there an equivalent to IF NOT EXISTS?
On PostgreSQL 9.6, how can I do the equivalent of CREATE TRIGGER IF NOT EXISTS? I noticed that in PostgreSQL 14 you can do CREATE OR REPLACE TRIGGER but I'm using 9.6 which doesn't have that.
0
votes
1
answer
654
views
How can I recursively query a table containing LTREE to get the tree like structure when some of the parent paths are missing?
Lets say I have this table:
CREATE TABLE nodes (node_path ltree);
INSERT INTO nodes VALUES ('Top.Science');
INSERT INTO nodes VALUES ('Top.Science.Astronomy.Astrophysics');
INSERT INTO nodes VALUES ('...
1
vote
1
answer
277
views
How to interpret the contents of a replication slot state file in the data directory
We know that there is a state file for slot in the data/pg_replslot/<slot_name> directory.
It is needed when server restarts after a crash.
Questions:
How is the data stored inside this state ...
- 137
0
votes
0
answers
679
views
Postgresql alternative for trigger in multiple-row insert
I have a table where the values of different variables are stored. Basically, it is a table of the form data_table (timestamp, variable, value). One of the variables represents a state value.
Certain ...
- 123
2
votes
1
answer
375
views
Vacuum limited to 1 worker
I'm currently working with a relatively big postgres 9.6 server with multiple databases and observing the following behavior:
The server has 5 max parallel workers configured but currently, only one ...
- 23
1
vote
1
answer
417
views
How to merge 2 postgres databases using WAL files
We have two different postgres databases one for STAGING and one for PROD disconnected from each other but they have same schema, same tables with same structures and same data. The requirement is ...
- 113
0
votes
1
answer
85
views
Different authentication behaviour when starting postgres from pg_ctl and when starting from systemd
I am running postgres 9.6 cluster on ubuntu 20.
All installation and starting the database is fine, however, I am experiencing a weird behavior.
In the pg_hba.conf file, I am trusting any connection ...
- 153
0
votes
0
answers
53
views
Postgres execute requests isolatedly
Users submit schema and query. I need to execute them and return the result.
But if multiple requests have the same table in the schema mentioned, then I am getting conflicts (which is as expected). I ...
- 101
2
votes
1
answer
4k
views
Postgres "unnesting" multiple json arrays simultaneouesly with different depths
I am trying to explode/unnest a row of jsonb arrays into individual rows and keep any rows that would result in nulls.
Fiddle: https://www.db-fiddle.com/f/pgd6E5yKf8NzPtgboeoC6c/7
Input table: (I am ...
- 123