Postgresql

发表于 阅读次数：

基本用法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# 1 创建数据库
CREATE DATABASE testdb OWNER dbuser;

# 2 列出所有数据库
\l

# 3 连接数据库
\c testdb

# 4 创建表和查看表
CREATE TABLE student(id int, name varchar(20), birth_date date);
\d # 查看所有表
\d student # 查看当前表结构
\d+ student # 显示更多的信息，如：与表列关联的注释

# 5 插入数据
INSERT INTO student(id, name, birth_date) values(1, 'a', '2010-10-10');
INSERT INTO student(id, name, birth_date) values(2, 'b', '2010-10-12');

# 6 查询表
SELECT * FROM student;

# 7 修改
UPDATE student SET name='aa' WHERE id = 1;
UPDATE student SET name='bb' WHERE id = 2;

# 8 删除
DELETE FROM student where id = 1;

# 9 删除表
DROP TABLE student;

安装

1
2
sudo apt-get install postgresql-client
sudo apt-get install postgresql
1
2
3
4
5
6
7
8
9
\h：查看SQL命令的解释，比如\h select。
\?：查看psql命令列表。
\l：列出所有数据库。
\c [database_name]：连接其他数据库。
\d：列出当前数据库的所有表格。
\d [table_name]：列出某一张表格的结构。
\du：列出所有用户。
\e：打开文本编辑器。
\conninfo：列出当前数据库和连接的信息。

注意

  • 要用;来结束语句；
  • 字符串要用单引号'来包裹；

enable remote connection

sudo service postgresql restart

参考资料

Permission denied for relation

1
2
3
4
5
6
7
-- login in admin:
psql -U postgres -d exampledb -h 127.0.0.1 -p 5432;
-- grant
GRANT ALL PRIVILEGES ON ALL TABLES user_tbl TO dbuser;

-- login in dbuser
psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432;

命令

https://stackoverflow.com/questions/26277356/how-to-get-current-database-and-user-name-using-a-select-in-postgresql/26277430

查看当前用户；

1
select user;

查看当前数据库

1
select current_database();

pgcli

https://www.pgcli.com/config

注意

在修改某个字段类型之前，最好删除这个字段的约束，
修改完后再把合适的约束添加上去。《PostgreSQL 修炼之道》p166

插入或更新

1
INSERT INTO contacts VALUES (1,'n1', '{15200000000, 15200000001}', 'shenzhen') on conflict (id) do UPDATE set phone = excluded.phone;

插入数组

1
2
3
4
5
6
7
create TABLE contacts(
        id int primary key,
        name varchar(40),
        phone varchar(32)[],
        address text);

 INSERT INTO contacts VALUES (1,'n1', '{15200000000, 15200000001}', 'shenzhen')

https://stackoverflow.com/questions/33335338/inserting-array-values

批量插入数据

1
2
3
4
5
6
7
create table student(student_no int, student_name varchar(20), age int, class_no int);

INSERT INTO student select generate_series(1,23), concat('s',generate_series(1,23)),18,  1
on conflict (student_no) do update
set student_name=excluded.student_name,
    age=excluded.age,
    class_no=excluded.class_no;

修改默认的schema

https://stackoverflow.com/questions/2875610/permanently-set-postgresql-schema-path

1
ALTER DATABASE lou SET search_path TO lou, osdba,postgres,public