postgresql – How can I use SQL window functions to determine the lead of the last row in a partition?

Considering the following simple example:

select lead(c) over (partition by a order by a)
from (values 
      (1,'1','date1'),
      (1,'2','date1'),
      (2,null,'date2')
) t(a,b,c)

The first two lines are records with the same ID (column a) and "date1" (column c). ID duplication is due to an improper operation in an array column that may have a different size or number of elements. The most unnecessary operation leads to column b.

Now I want to get the lead date of the next ID "date2". The query I wrote prints "date 1" for the first record, but I really want to get "date 2". I'm not interested in the static solution to this problem, which is a guide (c, 2) (order through a), but how to get the guidance of a window of dynamic size.

Expected issue:

date2
date2
NULL

postgresql – Regex character class: print: in postgres

I'm trying to replicate an Oracle test condition that prevents characters from being printed in Postgres, and I'm at a loss. I think Postgres can handle: print: and: ascii: as synonymys?!?


select REGEXP_REPLACE('bla', '(^(:print:))', '(X)','g') ;
 regexp_replace 
----------------
 bla
(1 row)

select REGEXP_REPLACE('bla'||chr(10)||'bla', '(^(:print:))', '(X)','g') ;
 regexp_replace 
----------------
 bla(X)bla
(1 row)

select REGEXP_REPLACE('Ҕ', '(^(:print:))', '(X)','g') ;
 regexp_replace 
----------------
 (X)
(1 row)

select REGEXP_REPLACE('ñino', '(^(:print:))', '(X)','g') ;
 regexp_replace 
----------------
 (X)ino
(1 row)

Why should Ҕ and ñ be trapped in it?

Every guide is appreciated.

postgresql – Can a "select" subquery be returned under some conditions?

Is it possible in Postgres to return a column (result of a subquery) only if a condition is met?

Query:

SELECT
  (SELECT NOW()) AS FIRST,

  (SELECT NOW()) AS SECOND

outputs:

| first      | second      |
|:-----------|------------:|
| timestamp  | timestamp   |

I want something in that direction:

SELECT
  (SELECT NOW()) AS FIRST,
  CASE WHEN 'potato' <> 'potato' THEN (SELECT NOW()) END AS SECOND
| first      |
|:-----------|
| timestamp  |

However, this always returns SECOND as NULL. Is there a way not to render the column?

PostgreSQL 10 logical replication does not work logically !!! Slots that block calls and do not send whale events back to the client

Logical postgress replication is not working

We created the logical replication slot on page 10 with the following command

select * from pg_create_logical_replication_slot (# slot, # 39; wal2json);

The above request creates a place in the database

Select * from pg_replication_slots;

Enter image description here

strong text

PG replication does not return stream events with the slot listen command.

The size of the slots automatically increases to a high value.

Below are global configurations at the postgress level

archive_mode = off
archive_command = (disabled)
ssl = off
wal_level = logical
shared_preload_libraries = decodebufs, wal2json
wal_keep_segments = 100
max_replication_slots = 100
max_wal_senders = 30
wal_sender_timeout = 0
max_standby_streaming_delay = -1

Question:
1 What is the best configuration for logical pg replication to fix this problem?
2 Which is the best plugin for logical replication of our following options:
wal2json
test decoding
protobuf

Please enter your input to fix our problem.

postgresql – How do I handle `.partial` WAL segments generated with the Postgres command` pg_receivewal`?

pg_receivewal create one .partial File for incomplete WAL segment. My question is how to handle this file during recovery. If I leave it as it is, the last transactions will not be restored. If I rename the removing file .partial After the fix, the recovery procedure crashes with the error:

FATAL:  archive file "000000010000000C00000080" has wrong size: 152 instead of 16777216

The only way I have found is to remove this .partial Segment after the previous crash recovery and start the process again. In this way, db seems to be fully restored.

What is the right way to deal with them? .partial Segments?

My DB version is 11.5.

postgresql Subquery Results for Postgres Submission to Function Returning TABLE

I have a function that accepts arrays as parameters and returns a table. I can get the arguments correctly, but return rows of type record. When I try to extend the record with FROM I get syntax errors

SELECT
  zdb.filters(
      sub.idx
    , sub.labels
    , variadic sub.filters
    ) 
    FROM (
      SELECT
        'conversations_zdx' as idx
        , ARRAY_AGG(id)::TEXT() as labels
        , ARRAY_AGG(filters) as filters
        FROM conversation_views 
        group by organization_id
) as sub;
+------------------------------------------+
| filters                                  |
|------------------------------------------|
| (32a430c6-eb33-4ac1-8ce7-9fb64e5b465c,1) |
| (a9177e44-95bf-4b1b-8724-b48efc97e3cf,4) |
+------------------------------------------+

The function signature is as follows

FUNCTION zdb.filters(
    index regclass,
    labels text(),
    filters JSON()) 
RETURNS TABLE (
    label text,
    doc_count bigint)

Linux – It takes a long time to start PostgreSQL

I use Postgre 10.9 version:

$ psql --version                                                         
psql (PostgreSQL) 10.9 (Ubuntu 10.9-0ubuntu0.18.04.1)

Recently i just stopped my postgresql:

$ sudo /etc/init.d/postgresql stop                                             
( ok ) Stopping postgresql (via systemctl): postgresql.service.

and then I try it again:

$ sudo /etc/init.d/postgresql start
(....) Starting postgresql (via systemctl): postgresql.service

But it takes me a long time to get started, even though I've waited more than 10 minutes.

If I try CTRL + C that, and check the status, it still inactive:

$ sudo /etc/init.d/postgresql status 
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: inactive (dead) since Sun 2019-08-18 21:33:49 WIB; 30min ago
 Main PID: 2452 (code=exited, status=0/SUCCESS)

Agu 18 21:33:49 billpresta systemd(1): Stopped PostgreSQL RDBMS.
Agu 18 21:33:49 billpresta systemd(1): Stopping PostgreSQL RDBMS...
Warning: Journal has been rotated since unit was started. Log output is incomplete or unavailable.

I also try to use the restart command:

$ sudo /etc/init.d/postgresql restart 
(....) Restarting postgresql (via systemctl): postgresql.service

I also tried this command:

sudo service postgresql start

I also tried it systemctl:

sudo systemctl start postgresql

But still the same, which takes a long time.

So, what's wrong with that? Please help me 🙂

postgresql – how to return unique priced results?

I have a database of the results of the races. Each race has the competitor's name and the time they got in that rank. How can I proceed if I want to return the top 10 competitors for time? I can already return the top 50 times / races, but I'm not sure how to return the top 50 drivers, so the time displayed is guaranteed to be the best time.

PostgreSQL self-join query condition to return no repeated results

I have this simplified query:

SELECT a.p_key, b.p_key
FROM test AS a, test AS b       
WHERE 
a.p_key < b.p_key -- symmetry breaker

Following is the returned record, the b.p_keys marked with * have already been retrieved:

a.p_key p.b_key
1   2
1   3

2   3* (already linked to a.p_key = 1)
2   4

3   4* (already linked to a.p_key = 2)
3   5

I did a lot of research and experimenting, but apparently I can not get the result I'm looking for:

I want to exclude b.p_keys that were previously retrieved, so my actual result set should look like this:

a.p_key p.b_key
1   2
1   3

2   4

3   5

postgresql – How to speed up the PG query, even if indexes are used

I have a PG table with about 30 million records. I notice that when I query the table with IN Clause that retrieves many rows, the query takes a long time to respond (~ 3 minutes) question How can I speed up the query even though my query uses indexes?

mydb=> explain analyze select count(scores), sum(scores) from mytable where classes in ('1st','2nd','3rd') and school_year in ('2018');
                                                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1150864.84..1150864.85 rows=1 width=40) (actual time=185454.773..185454.773 rows=1 loops=1)
   ->  Bitmap Heap Scan on mytable  (cost=16998.63..1146247.22 rows=923525 width=6) (actual time=934.546..184704.246 rows=967663 loops=1)
         Recheck Cond: ((school_year)::text = ANY ('{1st,2nd,3rd}'::text()))
         Filter: (school_year = 2018)
         Heap Blocks: exact=448386
         ->  Bitmap Index Scan on mytable_classes_idx  (cost=0.00..16767.75 rows=923525 width=0) (actual time=736.690..736.690 rows=967663 loops=1)
               Index Cond: ((school_year)::text = ANY ('{1st,2nd,3rd}'::text()))
 Planning time: 1.252 ms
 Execution time: 185463.153 ms
(9 rows)