Postgresql to trunc the text column-CodePudding

One of my Postgres column value is TEXT and i'm looking to trunc the first and the last five lines through postgres sql i tries using the substr function but i cannot determine the column length as it may vary for each row.

Column log : Text

pg_basebackup: initiating base backup, waiting for checkpoint to complete
 pg_basebackup: checkpoint completed
 pg_basebackup: write-ahead log start point: 4294/761C8F0 on timeline 1
 pg_basebackup: starting background WAL receiver
 pg_basebackup: created temporary replication slot "pg_basebackup_78757"
 0/92649519 kB (0%), 0/1 tablespace (...p/5432/2022-05-20_08_08/base.tar)
 46451/92649519 kB (0%), 0/1 tablespace (...p/5432/2022-05-0_08_08/base.tar)
 359630/92649519 kB (0%), 0/1 tablespace (...p/5432/2022-050_08_08/base.tar)
 721390/92649519 kB (0%), 0/1 tablespace (...p/5432/2022-05-_08_08/base.tar)
 <<<TRUNCATED>>>>        
    91974979/92649519 kB (99%), 0/1 tablespace (...p/5432/2022-05-20_08_08/base.tar)
    92136675/92649519 kB (99%), 0/1 tablespace (...p/5432/2022-05-20_08_08/base.tar)
    92393347/92649519 kB (99%), 0/1 tablespace (...p/5432/2022-05-20_08_08/base.tar)
    92701040/92701040 kB (100%), 0/1 tablespace (...p/5432/2022-05-20_08_08/base.tar)
    92702617/92702617 kB (100%), 0/1 tablespace (...p/5432/2022-05-20_08_08/base.tar)
    92702617/92702617 kB (100%), 1/1 tablespace                                         
    pg_basebackup: write-ahead log end point: 4294/F0CAB50
    pg_basebackup: waiting for background process to finish streaming ...
    pg_basebackup: syncing data to disk ...
    pg_basebackup: renaming backup_manifest.tmp to backup_manifest
    pg_basebackup: base backup completed

SELECT
    substring(log ,1,300)|| ' ' ||substring(log ,500,600)
    from hot_backup where id=1825`

O/p should just contain,

pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_103482"
pg_basebackup: write-ahead log end point: 0/2000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

CodePudding user response：

We can use the REGEXP_REPLACE function here. Note that the syntax gets a bit intricate, because we have a complex regex pattern which also uses Postgres extended string constants to express the newline character.

SELECT REGEXP_REPLACE(log,
           e'^(.*?\n.*?\n.*?\n.*?\n.*?)\n.*(.*?\n.*?\n.*?\n.*?\n.*?\n.*?)$',
           '\1\2') AS output
FROM hot_backup;

Demo

Note carefully in the above demo that newlines are introduced using extended string constants e.g. e'Here is line 1\nHere is line 2'. Regular string constants won't work properly here.