PostgreSQL入门

SQL PostgreSQL
SQL
发布日期:   2025-07-15
文章字数:   931
阅读时长:   4 分
阅读次数:  

PostgreSQL入门

简介

  • 数据结构丰富，JSON/JSONB，数组、hStore，几何/地理空间，网络地址，全文检索
  • 原生支持流复制，非常稳定可靠。逻辑复制允许更灵活的数据同步。

mysql postgresql常见命令差异

mysql:
TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
FLOAT, DOUBLE
DATE, TIME, DATETIME, TIMESTAMP（自动时区转换），YEAR
INT AUTO_INCREMENT
BIGINT AUTO_INCREMENT
postgre:
SMALLINT（2字节）, INTEGER（4字节）, BIGINT（8字节）
REAL（4字节浮点数）, DOUBLE PRECISION（8字节浮点数）
DATE, TIME, TIMESTAMP, INTERVAL
SERIAL
BIGSERIAL
mysql:
SELECT CURDATE();当前日期
SELECT IF(1 > 0, 'true', 'false');
SELECT IFNULL(NULL, 'default');
postgre:
SELECT CURRENT_DATE;当前日期
SELECT CASE WHEN 1 > 0 THEN 'true' ELSE 'false' END;
SELECT COALESCE(NULL, 'default');

原理

  • 存储结构：使用堆heap存储表数据，数据无序，每个表由n个堆文件组成，每个堆默认8KB,可配置
  • 索引数据结构：索引和表数据分开，使用B-tree，叶子节点存储TID，查到后通过其直接访问物理地址，速度很快
  • 表数据：update后，会追加一条新数据，然后修改索引叶子节点的TID指向，旧数据依然存在，等到没有活跃事务用到它时，视为可回收
  • VACUUM：回收机制，常规VACUUM不会停用户线程，识别到可回收的旧数据(死元组)，空间标记为可复用，不会直接清理空间(空间不还给操作系统，postgre能继续复用空间)
  • VACUUM FULL：停止用户线程，彻底重组表，空间归还给操作系统，建议永不VACUUM FULL，可考虑pg_repack
  • MVCC读已提交：在一个事务中，一条查询只能看到在查询开始之前已经提交的数据，多次执行同一个查询可能会看到不同的数据
  • MVCC可重复读：快照隔离：事务开始时会获取一个快照，这个快照记录了当前所有活动事务的状态。在事务的整个过程中，所有查询都基于这个快照。序列化快照隔离(SSL)：通过跟踪事务之间的读写依赖关系来检测可能破坏序列化的事务冲突，并在检测到冲突时回滚其中一个事务，避免幻读，其中SSL是乐观锁
  • 隔离级别最小精度到一个事务
  • 持久化：使用预写式日志（Write-Ahead Logging, WAL），所有数据修改先写入WAL日志，只有WAL写入成功后才确认事务提交，数据页在后台异步刷写到数据文件
  • 崩溃恢复：从最近的检查点开始，重放检查点之后的WAL日志，应用所有已提交的事务，回滚所有未提交的事务，更新磁盘达到一致性状态后再启动

命令

CREATE USER zzz WITH SUPERUSER PASSWORD 'mypassword';

springboot mysql -> postgresql

  • maven
            <!-- Mysql驱动包 -->
<!--        <dependency>-->
<!--            <groupId>mysql</groupId>-->
<!--            <artifactId>mysql-connector-java</artifactId>-->
<!--        </dependency>-->

        <!-- Postgresql驱动包 -->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>
  • 配置文件
    # 数据源配置
spring:
    datasource:
        type: com.alibaba.druid.pool.DruidDataSource
        # driverClassName: com.mysql.cj.jdbc.Driver
        driverClassName: org.postgresql.Driver
        druid:
            master:
                # url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
                url: jdbc:postgresql://localhost:5432/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=Asia/Shanghai
                username: zzz
                password: zzzzzz
            # 配置检测连接是否有效
            # validationQuery: SELECT 1 FROM DUAL
            validationQuery: SELECT version()
    pagehelper:
  # helperDialect: mysql
  helperDialect: postgresql
  reasonable: true
  supportMethodsArguments: true
  params: count=countSql
  • edit SQL
    sysdate() -> now()
find_in_set() -> ANY()
ifnull() -> COALESCE()
`query` -> query
SQL PostgreSQL
  目录