PostgreSQL使用过程中遇到的一些问题分析和注意事项笔记

Last updated on Jan 23, 2024 1 min read 数据库

关于数据类型

smallserial、serial和bigserial

pgsql中如果需要实现自增长ID字段，可以采用serial类型

时间、日期相关字段

根据PostgreSQL中文社区的文档，PostgreSQL支持的日期和时间类型包括如下：

对于日期而言，支持的可能输入包括下图所示输入：

对于时间而言，支持的可能输入包括下图所示：

可以看到，PostgreSQL的日期和时间类型对于是否有时区做了精细的分类；对于带时区(with timezone)的类型，查询时数据库会自动将数据转为客户端时区再显示；对于不带时区（without timezone）的类型，数据库对数据的存储和展现完全不受客户端时区影响。可以通过show timezone命令查看数据库会话时区。

实践中遇到的一些细节问题

更新日期/时间型字段的报错

如果要变更的新类型与旧类型之间不可进行强制类型转换，则需要删除字段再重建，如：

approval_date原本是time类型字段，其不可自动强制转换date类型，于是通过sql修改字段类型需要删除并重建字段。

ALTER TABLE company DROP COLUMN approval_date;
ALTER TABLE company ADD COLUMN  approval_date date;

ALTER TABLE gb_industry DROP COLUMN updated_at;
ALTER TABLE gb_industry ADD COLUMN  updated_at timestamptz;

Text类型

text类型字段是否浪费空间，对于不确定长度的字段应该用character varing并指定一个很大的最大长度好还是直接使用text类型好？

先说背景，在进行一些数据入库操作时，经常发现预定义的vary charecter不足以存储完整数据，多次手动扩容还是遇到数据超长问题，直接使用text可以避免数据超长/截断问题，但是这会造成空间浪费吗？text类型字段在pgsql内部机制是动态扩容还是默认超大固定空间？

根据一篇pgsql教程网站的博客:PostgreSQL Character Types: CHAR, VARCHAR, And TEXT

TEXT和不指定最大长度的CHARECTER VARYING类型是一样的，文章建议：一般来说除非需要检查（或希望数据库明确指出）数据是否超出一定长度，都应该使用TEXT。

对于text是否会浪费更多空间，查到了相关问题，但尚未发现提供了足够的技术细节论证的答案

空间模型字段

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

SELECT  mailing_address_point, ST_AsEWKB(company.registered_address_point) AS company_registered_address_point
FROM company
WHERE company.mailing_address_point IS NULL AND company.mailing_address IS NOT NULL ;

ORM操作空间模型字段的问题

应该使用geometry类型还是更精确的point、linestring、polygon类型？
如何从point类型改为geometry类型？

point类型无法通过强制类型转换为geometry类型，简单的办法是删除字段再重建。

如何查询几何对象并以WKT格式输出？

select st_astext(geo) from poi_address_coordinate_log;

基于sqlalchemy ORM框架如何写geometry对象？

基于geoalchemy2库的函数可以构建空间要素类型，兼容sqlalchemy。以点要素为例，通过ST_MakePoint方法构建的点要素类型可直接赋给postgresql中数据表的空间要素字段.
```
ST_MakePoint(114,22)
```

Core Tutorial — GeoAlchemy2 0.14.4.dev1+g4e4f45d documentation

……

关于触发器

为了记录数据变更时间，往往会有一个字段在数据发生更新时自动记录当前时间戳，对于MySQL而言可以很方便地通过设置下列额外值实现这个需求，但对于PgSQL而言却是另一种做法。

on update CURRENT_TIMESTAMP

PgSQL中需要使用触发器实现这个功能：

创建触发器函数

CREATE  FUNCTION update_updated_on_company()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

创建触发器

CREATE TRIGGER update_company_updated_on
    BEFORE UPDATE
    ON
        company
    FOR EACH ROW
EXECUTE PROCEDURE update_updated_on_company_task();

关于数据迁移

PostgreSQL PostGIS