CODEWITHSUNDEEP
sqlpostgresql
vinoth kumar
vinoth kumar

Reputation: 51

Postgresql to trunc the text column

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

Upvotes: 1

Views: 97

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521429

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.

Upvotes: 1

Related Questions