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