PostgreSQL 脚本收集

http://rongjih.blog.163.com/blog/static/33574461201110300454392/

Postgresql 脚本收集

2011-12-19 19:19:50|分类：数据库|标签：postgresql|举报|字号订阅

管理端登录：>psql postgres

1) 数据库的命令行管理脚本

1-1) 创建数据库

=> CREATE DATABASE数据库名 WITH ENCODING '数据库编码'OWNER 拥有者 TEMPLATE 模板名 TABLESPACE 表空间名;

其中 WITH ENCODING、 OWNER、 TEMPLATE和 TABLESPACE 都是可选参数，不设置就使用数据库的默认值。

如 => CREATE DATABASE testdb WITH ENCODING 'utf8' OWNER test;

另外默认情况下是根据模板template1创建的数据库（表空间为pg_default），如果要创建一个真正空白的数据库，可以改用模板 template0，因为 template1在数据库安装后是允许维护修改的，而 template0则在数据库安装初始化后一直保持不变的。特别是在使用pg_dump 命令备份的数据库进行恢复时，使用模板 template0创建一个空白的数据库，然后再恢复备份信息到这个数据库，就可以避免不必要的对象冲突。

查看已经创建的数据库的列表信息：=> \l 或 =>select datname from pg_database;

1-2) 删除数据库

=> DROP DATABASE数据库名;

如果当前数据库存在会话，会提示你而无法删除。

1-3) 用户权限管理

Postgresql的角色包括我们常说的数据库用户或者用户组。

创建角色：=> create role 角色名;

删除角色：=> drop role 角色名; ----如果角色是某个数据库的属主，删除时会有提示而无法删除

查看已存在的角色：=> \du 或 select rolname from pg_roles;

创建用户：=> create user 用户名; 或 create role 用户名 login;

注：只有创建的角色包含login属性，才能使用该角色连接数据库。

如要指定用户的登录密码，使用=> create role 用户名 login password '登录密码';

如要修改用户的密码，使用=>alter role 用户名with password '登录密码';

创建组(常用于某些用户的权限管理)：=>CREATE ROLE group_role NOINHERIT;

向组中添加用户：=> GRANT group_role TO role1,... ;

删除组中的用户：=> REVOKE group_role FROM role1,... ;

将表的select权限分配给用户：=>GRANT SELECT ON [表名] TO [用户名];

将表的select权限从用户中剔除：=> REVOKE SELECT ON [表名] FROM[用户名];

将数据库的select权限分配给用户：=> GRANT SELECT ON ALL TABLES IN SCHEMA public TO [ 用户名];

将数据库的select权限从用户中剔除：=>REVOKESELECT ON ALL TABLES IN SCHEMApublicFROM [用户名];

有以下几种权限：SELECT（读）， INSERT（追加）， UPDATE（写），DELETE， RULE（规则），REFERENCES(外键)， TRIGGER， CREATE，TEMPORARY，EXECUTE， USAGE，和 ALL PRIVILEGES。

alter default privileges的使用

a) 授予默认权限：alter default privileges for user 【用户1】 in schema public grant select on tables to 【用户2】;

使用这个语句后，【用户1】再建新表时，【用户2】就会自动有对这些新表的select权限。

注意执行这个语句后，在这个语句之前【用户1】的老表，【用户2】仍然是没有select权限的。

这时需要手工把旧表的select权赋给scott用户，然后再使用alter default privileges就实现了只读用户scott。

b) 收回授予的默认权限：alter default privileges for user 【用户1】in schema public revoke select on tables from 【用户2】;

执行这个语句之前，alter default privileges .... grant语句之后，【用户1】建的表，【用户2】已经有select权限的，这个语句不会收回这些select权限。

c) 查看alter default privileges权限的方法是使用 # /ddp;

总结：alter default privileges并不会改变已有用户的权限，只有在有对象创建时，才会根据alter default privileges定义的情况给指定的用户加权限，这个语句有点象是在建对象时加了一个权限触发器。

1-4) 关于表空间

数据库默认存在pg_global、pg_default两个表空间， pg_default是 template0和 template1使用的也是新建数据库的默认表空间。

创建一个新的表空间：=>CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';

注：所指定的表空间路径必须存在、为空及为Postgresql系统帐号的拥有者。

查看现有的表空间信息：=> \db 或 => select spcname from pg_tablespace;

设置默认的表空间：=> SET default_tablespace = space1;

将表创建到指定的表空间：=> CREATE TABLE foo(i int) TABLESPACE space1;

更多：

1-5) 查看数据库的版本信息：=> select version();

1-5) 查看数据库的命令帮助：=> \h 或 \?

\h查看的是sql命令，如create table等；\?查看的是psql的元数据命令，如\db等。

1-6) 备份数据库为sql 文件：

>pg_dump -U bcsystem > bcsystem.sql -- 用户bcsystem是数据库bcsystem的属主

1-7) 恢复备份的sql 文件：

>psql -U bcsystem -f bcsystem.sql -- 用户bcsystem是数据库bcsystem的属主

>psql -h192.168.0.222 -U bcsystem -W -f bcsystem.sql -- 用户bcsystem是数据库bcsystem的属主

=> \i 文件名.sql ---- 如果要输出详细的执行信息，=> set echo to all;

2) 数据表维护脚本

2-1) 添加列：ALTER TABLE bs_case_advice ADD COLUMNis_invalid boolean DEFAULTfalse;

2-2) 删除列：ALTER TABLE bs_case_advice DROP COLUMNis_invalid;

2-3) 修改列名：ALTER TABLE bs_case_advice RENAMEis_invalid TOis_invalid1;

2-4) 修改列类型：ALTER TABLE bs_case_advice ALTER COLUMN is_invalid TYPEboolean;

2-5) 修改列默认值：ALTER TABLE bs_case_advice ALTER COLUMN is_invalid SET DEFAULTfalse;

=>CREATE DATABASE数据库名WITH ENCODING'数据库编码'OWNER 拥有者 TEMPLATE 模板名 TABLESPACE 表空间名;

) sql注意事项

2-1) 使用 select distinct ... order by aa时，order by后的字段必须在select的字段中包含，否则报错

2-2) 查询语句中使用别名时，统一使用as关键字可以避免不必要的错误

如 t.name name、t.type type会报错，改为t.name as name、t.type as type就不会报错了

2-3) 使用hibernate时，如果domain字段的类型为boolean，数据库字段也必须用boolean，不能用integer代替，如

"INNER_ boolean not null default false,"

2-4) 官方文档中有说使用integer字段类型，速度是最快的，但integer类型不支持限定长度，如integer(2)的语法是错误的，如果要控制长度则只能使用numeric(2)来代替

) 其它

启动：$sudo /etc/init.d/postgresql-9.1start

停止：$ sudo /etc/init.d/postgresql-9.1stop

-)设置客户端编码：

=>SET CLIENT_ENCODING TO 'utf8';

或 => \encoding 'utf8';

-)查看客户端编码：=> \encoding

-)替换bytea字段内的部分值：

update act_ge_bytearray set bytes_ = overlay(bytes_ placing E'2013-04-25'::bytea from position(E'2013-03-25'::bytea in bytes_))

where id_ in ('398416','398420');

-)转换数据类型：select cast(id as varchar(255)),... from ... (id原为int类型，这里将其转换为varchar类型)

-)数字格式化：to_char(16.5,'FM99D0') -> 16.5,to_char(16,'FM999D0') -> 16.0

-)构建1-12的连续数据：select * from generate_series(1,12) order by 1 或使用递归

WITH RECURSIVE T(N) AS (

VALUES (1)

UNION ALL

SELECT N+1 FROM T WHERE N < 12

) SELECT N FROM T;

-)数组转为行：SELECT UNNEST(ARRAY['a','b','c','d']) AS names

-) 触发器函数里的几个特殊变量： http://www.php100.com/manual/PostgreSQL8/plpgsql-trigger.html

参考：

http://wiki.ubuntu.org.cn/PostgreSQL

Postgres 9.1 DEB 安装

字符集支持

一些PostgreSQL的操作

PostgreSQL9.0新特性介绍： alter default privileges，解决只读用户的问题

postgresql9.1 sql-revoke

postgresql9.1 sql-grant

postgres 创建只读用户，并且取消权限

中文环境下PostgreSQL的使用

Disqus 评论设计(一个递归例子)

PostgreSQL函数如何返回数据集

PostgreSQL的行转列应用 http://www.postgresql.org/docs/9.1/static/tablefunc.html http://blog.163.com/digoal@126/blog/static/163877040201151253211186/

SQL UPDATE FROM 的使用方法

Errors and Messages

sql 函数参考:

1）获取行号: SELECT row_number() over(),* FROM t;

2）返回第一个非空值：selectCOALESCE(value [,...])

PostgreSQL 脚本收集

Postgresql 脚本收集

相关文章