All Questions
Tagged with postgres-9.6 or postgresql-9.6
934 questions
0
votes
0
answers
52
views
AWS DMS Keeps silently failing with resource temporarily unavailable (apr status = 11)
I'm using AWS DMS to migrate from PostgreSQL 9.6 to PostgreSQL 17.5 and the DMS task keeps failing on a single table with this error
atfo_read: status__ == APR_SUCCESS failed (at_file.c:1000)
...
- 361
1
vote
1
answer
166
views
Query that checks if an item name contains a substring, and if true, perform a second query that grabs an image with a specific name
I have this simple query that makes an image appear in a report based on its name
SELECT image_data
FROM image
WHERE ((image_name='logo'));
I was able to modify this query so that if the name of ...
- 21
0
votes
1
answer
280
views
LENGTH of ARRAY [42883]: ERROR: function array_length(text[]) does not exist
ON PostgreSQL 9.6.10 (Red Hat 4.8.5-28), 64-bit
select string_to_array('file.name.pdf', '.')
{file,nome,pdf}
select array_length(string_to_array('file.nome.pdf', '.'))
[42883]: ERROR: function ...
- 1
2
votes
1
answer
78
views
Multiply all values of a json object with postgres 9.6
I have a column type text, with json object in it, in my table like :
{{"name":"MyName","age":10.0, "attribut":{"attack":"20","defence&...
- 66
1
vote
0
answers
33
views
Postgres 9.6 Find distinct time periods and count of records within a certain time range
I have a table that looks like below:
CREATE TABLE "time_records" (
startdate timestamp,
starttime timestamp,
endtime timestamp,
timesheetid int,
servicestate int,
recordcount ...
- 33
1
vote
1
answer
37
views
PostgreSQL 9.6 nested INSERT/RETURN statements have unacceptably poor CTE performance
I have a large table (> 50M rows) that I'm trying to convert into a new table format. As part of this, the row ids returned from the first insert need to be used in a second insert into a second ...
- 9,501
0
votes
2
answers
289
views
Filtering by JSON type field with Postgres and SQLAlchemy
I have a table test_table:
Column | Type |
------------+------------------------+
id | integer |
attributes | json |
With content:
...
- 277
0
votes
1
answer
460
views
Spring boot Flyway Gradle dependency version auto inclusion
I am using Spring boot 2.7.5 and included dependency Flyway core 7.15.0 to execute migrations script on Psotgresql-9.6 but The IntelliJ-Idea external dependencies show Flyway core dependency 7.15.0 ...
- 617
0
votes
1
answer
95
views
Integer array column having more than one value
This column is an integer array type:
log_session
-----------------------
{105683,105694}
{111833}
{120285}
{108592}
{84659,84663}
I want to know how many log_session have just 1 value (3 in this ...
- 4,103
0
votes
0
answers
68
views
Merge rows if they contain a duplicate value in one of many columns
SQL Fiddle
I'd like a query that will merge rows that have if they have the same value in one of 3 columns.
If one of of the following columns matches the rows should merge.
customerid, externalid, ...
- 132
1
vote
0
answers
347
views
Does deleting a lot of a rows in a transaction hurt with postgresql?
Say there are 3 tasks:
Insert rows to table_a.
Select rows in table_a, and select scope may cover the entire table.
Delete rows from table_a:
delete from table_a where timestamp < certain_time
...
- 10.1k
0
votes
1
answer
169
views
What happens for "FOR SHARE" with nested select?
Say, I have a nested select:
WITH candidate AS (
select * table_a where flag = 'a'
)
select * from candidate where other_flag = 'b' for share
This SQL indirectly select from table_a with for share....
- 10.1k
0
votes
1
answer
453
views
Will "select for share" take no effective without transaction?
To understand "select for share", I just read https://shiroyasha.io/selecting-for-share-and-update-in-postgresql.html.
I just want to confirm that, if I do "select for share" ...
- 10.1k
1
vote
1
answer
64
views
Postgres Regular Expression Positive Lookbehind with Repetition
Working for the first time with Postgres flavor regular expressions and stumped on it's behavior here.
Input: 'xxxaaabc'
Expected: 'xxxaaazzzbc'
Logic: Match M a's, capture group, and add 'zzz' ...
- 3,899
0
votes
0
answers
405
views
How to perform the same query on multiple tables?
I'd like to audit specific user activity across our application's database. Most of the important tables have these metadata fields:
id
date_last_edited
last_edited_by
I've got a prototype working:
...
- 3,499
1
vote
1
answer
132
views
Will index speed up finding max TIMESTAMPTZ?
I have a table conversation_views with columns: user_id | conversation_id | viewed_at
And a INDEX on conversation_views (conversation_id, user_id)
I would like query:
select
max(viewed_at)
from ...
- 3,692
2
votes
1
answer
6k
views
How to restore postgres with base backup and wal files
I am using rsync and pg_basebackup to copy wal file and base.
My rsync runs every minute and copy WAL files from pg_xlog and every end of the day i run pg_basebackup to get the base folder.
Now i need ...
- 21
0
votes
1
answer
386
views
pg_dump toc.dat missing public schema line
I'm trying to parse the toc.dat file and I see different result when dumping same database from different pg_dump versions. On 9.6 I've got line with SCHEMA public postgres but I don't have this line ...
- 2,383
2
votes
1
answer
2k
views
How to store a unique query RESULT into DBeaver variable?
i'm trying to store some data in Dbeaver binding variables like these:
--test
@set test = good
--testQ
@set testQ = select date from table where id = 1;
I need to store the query result, NOT the ...
0
votes
2
answers
350
views
postgreSQL function select from list
I'm new to PostgreSQL and any other databases.
I was trying to create a function (we are on version 9.6) that can select a temporary hashed password from a list and update the table and return the non ...
- 11
1
vote
0
answers
106
views
Postgresql uses Index Scan but Seq Scan is faster
I have the following partitioned table
Column | Type | Modifiers | Storage | Stats target | Description
---------------+-----------------------------+------...
- 543
0
votes
1
answer
120
views
Is there a way to grant SELECT on a table but prevent users creating VIEWs from it?
I have a table that gets regularly and automatically dropped and recreated. I would like users to be able to select from it but not be able to create VIEWs that would prevent this automatic dropping ...
- 2,879
0
votes
1
answer
506
views
Get conditional count and conditional DISTINCT count in a single SELECT
I have 3 tables:
TABLE: session_log
user_id | device | logged_on
---------|--------|---------------------
1 | web | 2022-01-01 12:43:25
1 | web | 2022-01-01 13:33:32
2 | ...
- 386
2
votes
1
answer
619
views
Is pg_stat_user_indexes replicated?
We are running Postgres 9.6.23 on Google Cloud SQL, with the Primary Instance being used for the (transactional) application and a Read Replica being used for analytical purposes.
Querying ...
- 4,618
0
votes
1
answer
825
views
Role inheritance by granting one role to another does not work
DROP OWNED BY user_a;
DROP ROLE IF EXISTS user_a;
CREATE ROLE user_a WITH
NOSUPERUSER
INHERIT
NOCREATEDB
NOCREATEROLE
NOREPLICATION
PASSWORD '***'
;
GRANT USAGE ON SCHEMA ...
- 2,855