我的 Postgres 列值之一是 TEXT，我希望通過 postgres sql 截斷第一行和最后五行，我嘗試使用 substr 函式，但我無法確定列長度，因為它可能因每一行而異。
列日志：文本
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 應該只包含，
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
uj5u.com熱心網友回復：
我們可以在REGEXP_REPLACE這里使用該功能。請注意，語法有點復雜，因為我們有一個復雜的正則運算式模式，它也使用 Postgres 擴展字串常量來表示換行符。
SELECT REGEXP_REPLACE(log,
e'^(.*?\n.*?\n.*?\n.*?\n.*?)\n.*(.*?\n.*?\n.*?\n.*?\n.*?\n.*?)$',
'\1\2') AS output
FROM hot_backup;
演示
請注意，在上面的演示中，換行符是使用擴展字串常量引入的，例如e'Here is line 1\nHere is line 2'. 常規字串常量在這里不能正常作業。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/494082.html
標籤：sql PostgreSQL
下一篇：沒有子查詢有沒有更好的方法？