PostgreSQL 14时间戳微秒末尾零截断的解决方法咨询

我使用PostgreSQL 14创建了如下表：

CREATE TABLE IF NOT EXISTS F_WALLET
(
    id                      serial PRIMARY KEY,
    -- columns
    -- more columns
    inserttime              timestamp
);

通过Python代码执行插入操作，示例语句如下：

INSERT INTO F_WALLET
    (
    -- some more columns
    inserttime
    )
    VALUES (
          11.05,      137.66,        2.20,
         137.66,        0.00,       21.81,
           0.00,       21.81,      115.85,
         137.66,        0.00,      115.85,
           true, '2022-09-15 20:21:57.768330+02:00' 
    )

但发现inserttime字段的微秒部分末尾为零时会被截断。执行SELECT id, inserttime FROM F_WALLET查询后得到如下结果：

897  | 2022-09-15 20:22:05.400541 
 896  | 2022-09-15 20:22:03.477851 
 895  | 2022-09-15 20:22:01.9936   
 894  | 2022-09-15 20:22:00.591512 
 893  | 2022-09-15 20:21:57.76833  
 892  | 2022-09-15 20:21:54.860594 

请问如何强制表存储时补零至指定位数？若无法实现，如何在查询时补零使微秒部分达到指定位数？

一、存储层面：无法强制补零存储 #

PostgreSQL的timestamp类型（包括timestamp without time zone和timestamp with time zone）在存储时仅保留实际有效精度，末尾无意义的零属于冗余信息，不会被存储。这是数据库底层用整数存储时间戳的逻辑决定的，无法通过修改表结构或存储规则强制补零。

二、查询层面：格式化输出补全6位微秒 #

如果需要让查询结果的微秒部分固定显示为6位（补全末尾的零），可以使用to_char()函数对时间字段进行格式化：

示例SQL： #

SELECT 
    id, 
    to_char(inserttime, 'YYYY-MM-DD HH24:MI:SS.US') AS inserttime
FROM F_WALLET;

参数说明： #

• YYYY-MM-DD：固定日期格式
• HH24:MI:SS：24小时制的时间格式
• .US：指定微秒部分强制显示6位，自动补全末尾的零

执行后查询结果会变成：

897  | 2022-09-15 20:22:05.400541 
 896  | 2022-09-15 20:22:03.477851 
 895  | 2022-09-15 20:22:01.993600 
 894  | 2022-09-15 20:22:00.591512 
 893  | 2022-09-15 20:21:57.768330 
 892  | 2022-09-15 20:21:54.860594 

注意事项： #

• 使用to_char()后，字段类型会转为字符串（text），若需后续进行时间运算，需用to_timestamp()转换回时间类型。
• 若inserttime是带时区的timestamptz类型，可在格式串中添加时区标识，例如'YYYY-MM-DD HH24:MI:SS.US TZ'。

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