Lin Hong's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

Postgresql CONSTRAINT Study 009 Tips

2020-09-01

Postgresql CONSTRAINT Study 009 Tips

Postgres 非空约束

非空约束就是指字段不能为NULL值

CREATE TABLE products_null (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric
);

示例: product_no 和 name 是非空字段,插入数据时候指定null时报错

postgres=# \d products_null
             Table "public.products_null"
   Column   |  Type   | Collation | Nullable | Default
------------+---------+-----------+----------+---------
 product_no | integer |           | not null |
 name       | text    |           | not null |
 price      | numeric |           |          |

postgres=#
postgres=# insert into products_null values(null,'aaa',100);
ERROR:  null value in column "product_no" violates not-null constraint
DETAIL:  Failing row contains (null, aaa, 100).
postgres=# insert into products_null values(1,null,100);
ERROR:  null value in column "name" violates not-null constraint
DETAIL:  Failing row contains (1, null, 100).
postgres=#

写法: NOT NULL 等同于 CHECK (column_name IS NOT NULL)

Postgres 唯一约束

唯一约束保证一个字段或者多个字段组合在表中是唯一的

写法:

CREATE TABLE products_unique (
    product_no integer UNIQUE,
    name text,
    price numeric
);

或者

CREATE TABLE products_unique_01 (
    product_no integer,
    name text,
    price numeric,
    UNIQUE (product_no)
);

示例:

postgres=# \d products_unique
            Table "public.products_unique"
   Column   |  Type   | Collation | Nullable | Default
------------+---------+-----------+----------+---------
 product_no | integer |           |          |
 name       | text    |           |          |
 price      | numeric |           |          |
Indexes:
    "products_unique_product_no_key" UNIQUE CONSTRAINT, btree (product_no)

postgres=#
postgres=# \d products_unique_01
           Table "public.products_unique_01"
   Column   |  Type   | Collation | Nullable | Default
------------+---------+-----------+----------+---------
 product_no | integer |           |          |
 name       | text    |           |          |
 price      | numeric |           |          |
Indexes:
    "products_unique_01_product_no_key" UNIQUE CONSTRAINT, btree (product_no)

postgres=#

如果要指定多列为唯一字段,参考如下:

CREATE TABLE products_unique_mul (
    product_no integer,
    name text,
    price numeric,
    UNIQUE (product_no,name)
);

示例: 多列唯一是允许一个列的值是可以相同的,只是保证组合成的值在整表中是唯一的

postgres=# \d products_unique_mul
          Table "public.products_unique_mul"
   Column   |  Type   | Collation | Nullable | Default
------------+---------+-----------+----------+---------
 product_no | integer |           |          |
 name       | text    |           |          |
 price      | numeric |           |          |
Indexes:
    "products_unique_mul_product_no_name_key" UNIQUE CONSTRAINT, btree (product_no, name)

postgres=#

创建表的时候可以指定唯一性约束,通过create unique index也可以创建唯一性约束。

另外每一个unique约束默认都会创建一个唯一索引

postgres=# select * from pg_indexes where tablename like 'products%';;
 schemaname |      tablename      |                indexname                | tablespace |                                                         indexdef
------------+---------------------+-----------------------------------------+------------+--------------------------------------------------------------------------------------------------------------------------
 public     | products_unique     | products_unique_product_no_key          |            | CREATE UNIQUE INDEX products_unique_product_no_key ON public.products_unique USING btree (product_no)
 public     | products_unique_01  | products_unique_01_product_no_key       |            | CREATE UNIQUE INDEX products_unique_01_product_no_key ON public.products_unique_01 USING btree (product_no)
 public     | products_unique_mul | products_unique_mul_product_no_name_key |            | CREATE UNIQUE INDEX products_unique_mul_product_no_name_key ON public.products_unique_mul USING btree (product_no, name)
(3 rows)

postgres=#

Postgres用户执行docker权限问题

错误描述:

[postgres@centos7_pg ~]$ docker exec -it pgtest /bin/bash
Got permission denied while trying to connect to the Docker daemon socket at unix:///var/run/docker.sock: Get http://%2Fvar%2Frun%2Fdocker.sock/v1.40/containers/pgtest/json: dial unix /var/run/docker.sock: connect: permission denied
[postgres@centos7_pg ~]$

原因:

Manage Docker as a non-root user

The docker daemon binds to a Unix socket instead of a TCP port. By default that Unix socket is owned by the user root and other users can only access it using sudo. The docker daemon always runs as the root user.

If you don’t want to use sudo when you use the docker command, create a Unix group called docker and add users to it. When the docker daemon starts, it makes the ownership of the Unix socket read/writable by the docker group.

docker进程使用Unix Socket而不是TCP端口。而默认情况下,Unix socket属于root用户,需要root权限才能访问。

可以通过下面方法:

  • sudo 权限解决
root@centos7_pg ~]# visudo

root@centos7_pg ~]# su - postgres
Last login: Mon Apr 13 21:34:10 CST 2020 on pts/1
[postgres@centos7_pg ~]$ sudo docker ps -a

We trust you have received the usual lecture from the local System
Administrator. It usually boils down to these three things:

    #1) Respect the privacy of others.
    #2) Think before you type.
    #3) With great power comes great responsibility.

[sudo] password for postgres:
CONTAINER ID        IMAGE                        COMMAND                  CREATED             STATUS              PORTS                    NAMES
0b35109c39c9        postgres_10_centos7.7.1908   "docker-entrypoint.s…"   3 days ago          Up About a minute   0.0.0.0:5431->5432/tcp   pgtest01
[postgres@centos7_pg ~]$
  • 创建docker用户组,并将当前用户加入到docker用户组中,那么当前用户就有权限访问Unix socket了,进而也就可以执行docker相关命令 (docker守护进程启动的时候,会默认赋予名字为docker的用户组读写Unix socket的权限)
root用户
groupadd docker     #添加docker用户组
gpasswd -a $USER docker     #将登陆用户加入到docker用户组中
newgrp docker     #更新用户组
docker ps    #测试docker命令是否可以使用sudo正常使用
之后重启docker服务
systemctl restart docker
[root@centos7_pg ~]# whoami
root
[root@centos7_pg ~]# groupadd docker
[root@centos7_pg ~]# gpasswd -a postgres docker
Adding user postgres to group docker
[root@centos7_pg ~]# newgrp docker
[root@centos7_pg ~]# su - postgres
Last login: Mon Apr 13 21:19:21 CST 2020 on pts/1
[postgres@centos7_pg ~]$ docker ps -a
Got permission denied while trying to connect to the Docker daemon socket at unix:///var/run/docker.sock: Get http://%2Fvar%2Frun%2Fdocker.sock/v1.40/containers/json?all=1: dial unix /var/run/docker.sock: connect: permission denied
[postgres@centos7_pg ~]$
[root@centos7_pg ~]# grep docker /etc/group
dockerroot:x:995:
docker:x:1003:postgres
[root@centos7_pg ~]# systemctl restart docker
[root@centos7_pg ~]# ps -ef | grep docker
root      2580     1  1 21:37 ?        00:00:00 /usr/bin/dockerd --log-level=error
root      2585  2580  0 21:37 ?        00:00:00 containerd --config /var/run/docker/containerd/containerd.toml --log-level error
root      2721  2200  0 21:37 pts/1    00:00:00 grep --color=auto docker
[root@centos7_pg ~]# docker ps -a
CONTAINER ID        IMAGE                        COMMAND                  CREATED             STATUS                        PORTS               NAMES
0b35109c39c9        postgres_10_centos7.7.1908   "docker-entrypoint.s…"   3 days ago          Exited (137) 15 seconds ago                       pgtest01
[root@centos7_pg ~]# su - postgres
Last login: Mon Apr 13 21:34:59 CST 2020 on pts/1
[postgres@centos7_pg ~]$ docker ps -a
CONTAINER ID        IMAGE                        COMMAND                  CREATED             STATUS                        PORTS               NAMES
0b35109c39c9        postgres_10_centos7.7.1908   "docker-entrypoint.s…"   3 days ago          Exited (137) 20 seconds ago                       pgtest01
[postgres@centos7_pg ~]$ docker start 0b35109c39c9
0b35109c39c9
[postgres@centos7_pg ~]$ docker ps -a
CONTAINER ID        IMAGE                        COMMAND                  CREATED             STATUS              PORTS                    NAMES
0b35109c39c9        postgres_10_centos7.7.1908   "docker-entrypoint.s…"   3 days ago          Up 1 second         0.0.0.0:5431->5432/tcp   pgtest01
[postgres@centos7_pg ~]$ docker exec -it pgtest01 ifconfig
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 172.17.0.2  netmask 255.255.0.0  broadcast 172.17.255.255
        ether 02:42:ac:11:00:02  txqueuelen 0  (Ethernet)
        RX packets 8  bytes 656 (656.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 3  bytes 205 (205.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 3  bytes 205 (205.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[postgres@centos7_pg ~]$

Have a good work&life! 2020/09 via LinHong


Similar Posts

Comments