PostgreSQL

[uniquejava]

PostgreSQL Versions

Version	Current minor	Supported	First Release	Final Release
13	13.0	Yes	September 24 2020	~
12	12.2	Yes	October 3, 2019	November 14, 2024
11	11.7	Yes	October 18, 2018	November 9, 2023
10	10.12	Yes	October 5, 2017	November 10, 2022
9.6	9.6.17	Yes	September 29, 2016	November 11, 2021

Intellij IDEA 2020.1 对应的psql client为12/13

docker compose (2020.11更新）

参考: Getting Started with PostgreSQL using Docker-Compose

.env文件

# redis
REDIS_DIR=./redis

# postgresql
POSTGRES_ROOT_PASSWORD=happyHalloween
INIT_SQL_DIR=./xxxxxx/src/main/resources/sql/postgres

docker-compose.yaml文件

version: "3"
# 管理的服务
services:
  postgres:
    image: postgres:13.0
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: ${POSTGRES_ROOT_PASSWORD}
      POSTGRES_DB: test_db
    volumes:
      # Named volumes
      - pgdata:/var/lib/postgresql/data
      # Bind mounts
      - ${INIT_SQL_DIR}:/docker-entrypoint-initdb.d

# named volumes (最佳实践)
# Named volumes are volumes which you create manually with docker volume create VOLUME_NAM
# see https://stackoverflow.com/a/47152658/2497876
# see https://medium.com/analytics-vidhya/getting-started-with-postgresql-using-docker-compose-34d6b808c47c
# 使用named volumes能保证包数据永不丢失
# docker volume ls 查看
# docker volume remove pgdata 移除
volumes:
  pgdata:

docker （以前写的太麻烦， 备份）

https://hub.docker.com/_/postgres/

默认创建的database name, user, password都是postgres

# 下载PQ11
docker pull postgres:13

# 注意如果要完全重建DB， 首先要rm -rf $HOME/docker/volumes/postgres
docker run --rm  --name pg-demo -e POSTGRES_PASSWORD=postgres -d -p 5432:5432 -v $HOME/docker/volumes/postgres:/var/lib/postgresql/data  postgres:11-alpine

# PSQL CLI
psql -h localhost -U postgres -d postgres

# or
docker exec -it <container_id> psql -U <user> -d <database>

We have provided several options to the docker run command:

— rm: Automatically remove the container and it’s associated file system upon exit. In general, if we are running lots of short term containers, it is good practice to to pass rm flag to the docker run command for automatic cleanup and avoid disk space issues. We can always use the v option (described below) to persist data beyond the lifecycle of a container
— name: An identifying name for the container. We can choose any name we want. Note that two existing (even if they are stopped) containers cannot have the same name. In order to re-use a name, you would either need pass the rm flag to the docker run command or explicitly remove the container by using the command docker rm [container name].
-e: Expose environment variable of name POSTGRES_PASSWORD with value docker to the container. This environment variable sets the superuser password for PostgreSQL. We can set POSTGRES_PASSWORD to anything we like. I just choose it to be docker for demonstration. There are additional environment variables you can set. These include POSTGRES_USER and POSTGRES_DB. POSTGRES_USER sets the superuser name. If not provided, the superuser name defaults to postgres. POSTGRES_DB sets the name of the default database to setup. If not provided, it defaults to the value of POSTGRES_USER.
-d: Launches the container in detached mode or in other words, in the background.
-p: Bind port 5432 on localhost to port 5432 within the container. This option enables applications running out side of the container to be able to connect to the Postgres server running inside the container.
-v: Mount $HOME/docker/volumes/postgres on the host machine to the container side volume path /var/lib/postgresql/data created inside the container. This ensures that postgres data persists even after the container is removed.

see: Don’t install Postgres. Docker pull Postgres

[uniquejava]

Cyper 实战

Spring Boot Integration

pom.xml

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

application.yml

 spring: 
  datasource:
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://localhost:5432/postgres
    username: postgres
    password: postgres

PostgreSQL Triggers

参考： Creating a Trigger in PostgreSQL

-- postgres triggers
-- see https://www.postgresqltutorial.com/creating-first-trigger-postgresql/
drop trigger if exists grade_changes on customer;

create or replace function create_event()
    returns trigger as
$BODY$
begin
    if new.grade <> old.grade then

        insert into event(event_title, event_description, start_date, end_date, start_time, end_time,
                          is_full_day_event,
                          is_recurring, created_by,
                          created_date, parent_event_id)
        values ('customer grade changed!', concat('from ', old.grade, ' to ', new.grade), now(), null,
                null, null, 'N', 'N', 'cyper', now(), null);
    end if;

    return new;
end;

$BODY$
    LANGUAGE plpgsql VOLATILE;

create trigger grade_changes
    after update
    on customer
    for each row
execute procedure create_event();

another trigger

-- postgres triggers
-- see https://www.postgresqltutorial.com/creating-first-trigger-postgresql/

create or replace function create_event2()
    returns trigger
    language plpgsql
as
$$
    declare v_post post%rowtype;
begin

    select * into v_post from post where id = new.post_id;

    if v_post.user_id = 2 then
        insert into event(event_title, event_description, start_date, end_date, start_time, end_time,
                          is_full_day_event,
                          is_recurring, created_by,
                          created_date, parent_event_id)
        values (v_post.title, concat('刚有人回复了你的帖子:',new.body), now(), null,
                null, null, 'N', 'N', 'cyper', now(), null);
    end if;

    return new;
end;
$$;

drop trigger if exists comment_count_changes on comment;
create trigger comment_count_changes
    after insert
    on comment
    for each row
execute procedure create_event2();

[uniquejava]

PostgreSQL DDL

create table customer
(
    id    integer      not null
        constraint customer_pkey
            primary key,
    name  varchar(100) not null,
    grade char         not null
);

alter table customer
    owner to postgres;

create table event
(
    id                serial      not null
        constraint event_pkey
            primary key,
    event_title       varchar(50) not null,
    event_description varchar(500),
    start_date        date        not null,
    end_date          date,
    start_time        time,
    end_time          time,
    is_full_day_event char        not null,
    is_recurring      char        not null,
    created_by        varchar(10),
    created_date      timestamp default CURRENT_TIMESTAMP,
    parent_event_id   integer
);

alter table event
    owner to postgres;

[uniquejava]

Note: Triggers 只能加载 updatable view 之上

Updatable view

Is a view in the database updatable?

Yes, they are updatable but not always. Views can be updated under followings:

• If the view consists of the primary key of the table based on which the view has been created.

• If the view is defined based on one and only one table.

• If the view has not been defined using groups and aggregate functions.

• If the view does not have any distinct clause in its definition.

• If the view that is supposed to be updated is based on another view, the later should be updatable.

• If the definition of the view does not have any sub queries.

[uniquejava]

修改seq的起始值

方法一

SELECT setval('payments_id_seq', 21, true);  # next value will be 22
SELECT setval('payments_id_seq', select max(id) from xxx, true);  # next value will be max(id) + 1

true 表示在第二个参数21的基础上 +1， 是默认值。

方法二

ALTER SEQUENCE payments_id_seq RESTART WITH 22;