在Ubuntu上安装使用PostgreSQL数据库

1. 安装

apt update

apt upgrade

apt show postgresql

sudo apt install postgresql postgresql-contrib

service postgresql status

cd /home

mkdir postgres

chown postgres prostgres

cd postgres

su postgres

psql -c "SELECT version();"

psql

> ALTER USER postgres WITH PASSWORD 'my_password';

> CREATE USER idealand WITH PASSWORD 'my_password';

> \du

> ALTER USER my_user WITH SUPERUSER ;

> DROP USER my_user ;

> \q

psql -U postgres
psql -U my_user -d my_db

service postgresql restart

2. 允许远程访问

修改pg_hba.conf文件，添加下面一行

host all all 0 . 0 . 0 . 0 / 0 md5

修改postgresql.conf文件，添加下面一行

listen_addresses = '*'

service postgresql restart

3. 创建数据库

CREATE DATABASE idealand;

\l

\c idealand

4. 创建数据表

CREATE TABLE Person
(
id serial PRIMARY KEY NOT NULL UNIQUE,
name VARCHAR(300) NOT NULL, --姓名
pw VARCHAR(300) NOT NULL, --密码
remain NUMERIC(22,6) NOT NULL, --余额
contri NUMERIC(22,6) NOT NULL, --股份
nickname VARCHAR(300) NOT NULL, --昵称
express VARCHAR(600) NOT NULL, --自我表达
introduction VARCHAR(1200) NOT NULL, --自我介绍
services INT[] NOT NULL, --技能
picture VARCHAR(600) NOT NULL, --头像
email VARCHAR(300) NOT NULL,
phone VARCHAR(300) NOT NULL,
location VARCHAR(600) NOT NULL, --当前所在位置
post_address VARCHAR(600) NOT NULL, --邮寄地址
score INT NOT NULL, --信用分
birth timestamp NOT NULL, --生日
no VARCHAR(300) NOT NULL, --身份证号码
last_login timestamp NOT NULL, --最近登录时间
update_time timestamp NOT NULL, --更新时间
create_time timestamp NOT NULL, --更新时间
status INT NOT NULL --状态
);

5. 查询数据表和字段信息

SELECT * FROM pg_tables WHERE tablename NOT LIKE 'pg_%' AND tablename NOT LIKE 'sql_%' ORDER BY tablename;

SELECT
A.attnum,
A.attname AS field,
T.typname AS TYPE,
A.attlen AS LENGTH,
A.atttypmod AS lengthvar,
A.attnotnull AS NOTNULL,
b.description AS COMMENT
FROM
pg_class C,
pg_attribute A
LEFT OUTER JOIN pg_description b ON A.attrelid = b.objoid
AND A.attnum = b.objsubid,
pg_type T
WHERE
C.relname = 'tablename'
AND A.attnum > 0
AND A.attrelid = C.oid
AND A.atttypid = T.oid
ORDER BY
A.attnum;

参考资料

PostgreSQL 数组

https://www.w3cschool.cn/postgresql13_1/postgresql13_1-kc2n3jas.html

数据类型

https://www.yiibai.com/postgresql/postgresql-datatypes.html

https://www.runoob.com/postgresql/postgresql-create-database.html

如何在 Ubuntu 上安装和配置 PostgreSQL

https://linux.cn/article-11480-1.html

在Ubuntu上安装与简单使用PostgreSQL数据库

https://zhuanlan.zhihu.com/p/467644334