如何将含多分隔符的grep输出导入PostgreSQL表？

1. 修正表结构语句 #

PostgreSQL不支持Oracle的varchar2类型，且user、table属于保留关键字，必须转义或改名，建议先修正表创建语句：

CREATE TABLE ddl_log (
    ts timestamp,
    "user" text,  -- 用双引号转义保留关键字，也可改名为username
    "table" text  -- 同样转义table关键字，或改名为table_name
);

2. 预处理日志文件 #

先过滤掉日志里无关的-bash-4.2$行，再将每行格式拆分对应三列，用awk命令处理：

awk '!/^\-bash/ {
    split($0, parts, / , /)
    ts = parts[1]
    if (parts[2] ~ /^".*"\..*"$/) {
        gsub(/"/, "", parts[2])
        split(parts[2], tbl_user, /\./)
        user = tbl_user[1]
        table = tbl_user[2]
    } else {
        user = ""
        table = parts[2]
    }
    print ts "," user "," table
}' log.txt > cleaned_log.txt

处理后的cleaned_log.txt内容如下：

2023-03-01 00:40:26,rami,big_white_shoes
2023-03-01 13:10:42,,black_shoes

3. 执行数据导入 #

使用PostgreSQL的\copy命令导入清理后的文件：

\copy ddl_log (ts, "user", "table") FROM 'cleaned_log.txt' WITH DELIMITER ',' NULL AS '';

如果是服务器端超级用户操作，也可使用SQL原生COPY命令（需指定文件绝对路径）：

COPY ddl_log (ts, "user", "table") FROM '/path/to/cleaned_log.txt' WITH DELIMITER ',' NULL AS '';

4. 验证导入结果 #

执行查询确认数据符合预期：

SELECT * FROM ddl_log;

返回结果：

内容的提问来源于stack exchange，提问作者nick