PostgreSQL 基础
一白.
菜鸟中的菜鸟
展开
-
PostgreSQL大表快速添加包含not null和default的字段
和扣款一、前言 在需求的不断迭代中,表字段也会增加,有时候在大表增加的字段中,存在包含not null和default字段,这时候添加表字段就执行相当慢,因为PostgreSQL把表数据全部重写,参考:https://my.oschina.net/Kenyon/blog/99757; &n...原创 2019-06-30 00:55:46 · 9418 阅读 · 1 评论 -
PostgreSQL 使用psql插入参数到sql脚本文件中
1. 数据postgres=# select * from tb1; id | name -----+------ 2 | aa 3 | aa 4 | aa 7 | aa 8 | aa 9 | aa 10 | aa 1 | cc 5 | cc 6 | cc 100 | vv(11 rows)2. 准备sql脚本文件vi test.s原创 2015-05-11 18:25:54 · 7350 阅读 · 1 评论 -
PostgreSQL 判断字符串包含的几种方法
判断字符串包含的几种方法: 1. position(substring in string):postgres=# select position('aa' in 'abcd'); position ---------- 0(1 row)postgres=# select position('ab' in 'abcd'); position ----------原创 2015-05-03 21:32:14 · 78343 阅读 · 1 评论 -
PostgreSQL实用日常维护SQL
转载自: http://my.oschina.net/Kenyon/blog/853951. 查看数据库select * from pg_database;2. 查看表空间select * from pg_tablespace;3. 查看语言select * from pg_language;4. 查看角色用户select * from pg_user;select * from pg_shado转载 2015-05-08 08:18:34 · 2348 阅读 · 0 评论 -
PostgreSQL 使用RETURNING返回值
PostgreSQL 使用RETURNING返回值原创 2015-04-21 08:55:20 · 19052 阅读 · 1 评论 -
PostgreSQL explain返回行的评估
PostgreSQL explain返回行的评估原创 2015-03-05 12:48:52 · 3083 阅读 · 0 评论 -
PostgreSQL default_statistics_target
default_statistics_target(integer): PostgreSQL进行analyze的时候,参考的生成的列的柱状图的大小,可以理解为采样颗粒度。 官方解释:Sets the default statistics target for table columns without a column-specific target set via ALTER TABLE SE原创 2015-04-09 10:56:01 · 3109 阅读 · 1 评论 -
PostgreSQL unique constraint allow mutiple nulls
1. 建表postgres=# create table tb8(id integer,name character varying);CREATE TABLE2. 添加唯一约束postgres=# alter table tb8 add CONSTRAINT check_name_unique unique(name);ALTER TABLEpostgres=# \d tb8原创 2015-04-02 12:15:18 · 806 阅读 · 0 评论 -
PostgreSQL联结关键字 ON、USING、NATURAL
PostgreSQL联结关键字 ON USING NATURAL原创 2015-04-07 08:32:54 · 4863 阅读 · 2 评论 -
PostgreSQL key words of LATERAL
文档解释:FROM子句中出现的子查询可以放在关键字LATERAL之前,这样就允许它们引用通过前置FROM条目提供的字段。(如果没有LATERAL,那么每个子查询都被认为是独立的并且不能交叉引用任何其他的FROM条目。)这是PostgreSQL9.3的新增特性,第一次看到这解释,估计看不懂,看下面解释。1. 准备好的数据。postgres=# select * from tb原创 2015-04-07 16:04:20 · 3080 阅读 · 1 评论 -
PostgreSQL UNION[ALL],INTERSECT [ALL],EXCEPT [ALL]
PostgreSQL UNION[ALL],INTERSECT [ALL],EXCEPT [ALL]原创 2015-03-25 10:34:13 · 3749 阅读 · 1 评论 -
PostgreSQL SEQUENCE
PostgreSQL有数据类型 smallserial,serial,bigserial,它们都不是真正的类型,是依赖与一个序列的自动增长的且不为null的类型。 create table tb10( id serial ); 等价于下面的语句: create sequence tb10_id_seq; create table tb10( id integer原创 2015-04-08 11:22:40 · 2376 阅读 · 1 评论 -
索引扫描
索引扫描:1. 索引唯一扫描2. 索引范围扫描3.原创 2014-10-15 08:39:11 · 710 阅读 · 0 评论 -
全表扫描
影响全表扫描的因素:1. 返回的数据量的原创 2014-10-13 16:04:36 · 1842 阅读 · 0 评论 -
PostgreSQL RULE
RULE语法:postgres=# \h create ruleCommand: CREATE RULEDescription: define a new rewrite ruleSyntax:CREATE [ OR REPLACE ] RULE name AS ON event TO table_name [ WHERE condition ] DO [原创 2015-04-06 17:18:12 · 4374 阅读 · 0 评论 -
PostgreSQL DISTINCT ON
用法: DISTINCT ON ( expression [, …] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. […]。 Note that the “first row” of each set is unpredictable unless OR原创 2015-04-17 08:38:28 · 16266 阅读 · 1 评论 -
PostgreSQL 锁浅析
转载自:http://francs3.blog.163.com/blog/static/40576727201082134343604/ 一、概述 此文档主要对Postgresql 锁机制进行分析,在讲解的过程中结合实验,理解Postgresql的锁机制。二、表级锁类型 表级锁类型分为八种,以下对各种表级锁类型进行简单介绍下, 锁的冲突模式可以参考3.1的图一:表级锁冲突模式。2.1 ACCES转载 2015-05-04 09:16:39 · 14883 阅读 · 0 评论 -
PostgreSQL function返回多行
1. 建表postgres=# create table tb1(id integer,name character varying);CREATE TABLEpostgres=# postgres=# insert into tb1 select generate_series(1,5),'aa';INSERT 0 5 2. 返回单字段的多行(returns setof datatype原创 2015-05-04 22:20:43 · 11374 阅读 · 2 评论 -
PostgreSQL ROW_NUMBER() OVER()
语法: ROW_NUMBER() OVER( [ PRITITION BY col1] ORDER BY col2[ DESC ] ) 解释: ROW_NUMBER()为返回的记录定义个行编号, PRITITION BY col1 是根据col1分组,ORDER BY col2[ DESC ]是根据col2进行排序。 举例:postgres=# create table student(i原创 2015-04-16 18:07:28 · 51322 阅读 · 1 评论 -
PostgreSQL行列变换
使用 extenstion tablefunc地址: http://yq.aliyun.com/articles/269?spm=5176.group29.0.0.IeCcA9原创 2015-12-23 22:30:31 · 1586 阅读 · 0 评论 -
行政地址代码
INSERT INTO address_code (id, code, name, level, parentcode) VALUES (811, '230811', '郊区', 2, '230800');INSERT INTO address_code (id, code, name, level, parentcode) VALUES (1965, '421321', '随县', 2, '42原创 2015-12-02 16:49:01 · 3180 阅读 · 0 评论 -
PostgreSQL 多行变一行
实例表:postgres=# \d book2 Table "public.book2" Column | Type | Modifiers --------+-------------------+------------原创 2015-03-19 15:26:38 · 15637 阅读 · 1 评论 -
postgreSQL copy与\copy的区别
区别是:copy必须使用能够超级用户使用;copy .. to file ,copy file to ..中的文件都是数据库服务器所在的服务器上的文件。\copy 一般用户即可执行\copy 保存或者读取的文件是在客户端所在的服务器 比如当使用192.168.17.53连上192.168.17.52的数据库,使用copy tb1 to ‘/home/postgres/aa.txt’,该文原创 2015-07-07 15:28:55 · 11699 阅读 · 0 评论 -
PostgreSQL ALTER TABLE中改变数据类型时USING的用法
在修改表字段类型的时候使用Using来进行显示的转换类型。原文说明:SET DATA TYPE This form changes the type of a column of a table. Indexes and simple table constraints involving the column willbe automatically converted to use原创 2015-06-06 12:15:51 · 23614 阅读 · 3 评论 -
PostgreSQL 使用citd删除重复行
1. ctid的简单介绍: ctid是PostgreSQL表中的系统字段,表示数据行在它所在表内的物理位置。ctid的字段类型是oid。但是VACUUM FULL操作之后,经过回收数据块内的空闲空间,数据行在块内的物理位置会发生移动,即ctid会发生变化。2. 使用ctid删除重复行数据postgres=# select ctid,* from tb20; ctid | id ----原创 2015-06-04 08:56:17 · 2732 阅读 · 1 评论 -
PostgreSQL 客户端下查看表,视图,function,切换数据库等
\l: 查看所有数据库 \c dataname: 切换数据库 \dt: 查看所有自己创建的表 \dt+: 查看所有自己创建的表,显示表的相关内容占的磁盘大小 \dt(+) tablename: 参看指定表 \dv: 查看所有自己创建的视图 \dv+: 查看所有自己创建的视图,显示大小 \dv(+): 查看制定的视图 \df: 查看所有自己创建的function \df+: 查看所有原创 2015-04-13 09:45:33 · 13821 阅读 · 1 评论 -
PostgreSQL function里面调用function
PostgreSQL function里面调用function原创 2015-05-31 09:50:37 · 13601 阅读 · 1 评论 -
PostgreSQL sql放入文件批量执行
PostgreSQL sql放入文件批量执行原创 2015-03-18 11:00:05 · 19933 阅读 · 1 评论 -
PostgreSQL file_fdw的使用
1. 简介 file_fdw模块提供了外部数据封装器file_fdw,可以用来在服务器的文件系统中访问数据文件。数据文件必须是COPY FROM 可读的格式;参阅COPY获取细节。访问这样的数据文件当前只是可读的。 简单的说,就是创建一个表,表的数据来源于数据库之外的文件,而这个文件是通过copy命令导出去的。 使用这个封装器创建的外部表可以有下列选项: 参数 说明 file原创 2015-05-12 18:22:41 · 3671 阅读 · 0 评论 -
PostgreSQL lag,lead获取记录前后的数据
PostgreSQL lad,lead获取记录前后的数据原创 2015-05-04 22:27:30 · 15992 阅读 · 1 评论 -
PostgreSQL SELECT INTO和INSERT INTO SELECT 两种表复制语句
SELECT INTO和INSERT INTO SELECT两种表复制语句都可以用来复制表与表之间的数据,但是它们之间也有区别。1. INSERT INTO FROM语句 语句形式为:Insert into Table2(field1,field2,…) select value1,value2,… from Table1 要求目标表Table2必须存在,由于目标表Table2已经存在,所以我们除原创 2015-05-12 22:53:36 · 51258 阅读 · 1 评论 -
PostgreSQL 事件触发器
创建触发器的步骤分两步,a. 创建触发器函数,b. 编写触发语句;创建事件触发器的步骤也是一样,分为两步。1. 简介: 事件触发器为全局触发器,影响范围为指定的某个库,并且可以捕获 DDL 事件,而传统的触发器是基于表级别,并且只能捕获 DML 事件;事件触发器是PostgreSQL 9.3新增的功能。2. 语法:postgres=# \h create event triggerComma原创 2015-05-12 08:27:09 · 11039 阅读 · 1 评论 -
PostgreSQL的外部表使用
原文链接:http://my.oschina.net/Kenyon/blog/165432 PostgreSQL从9.1开始增加了外部表访问的功能,这个功能就是数据库直接读取数据库以外的文件,比如csv或者text等类型的文件,暂时不支持DML。PostgreSQL有各种插件能直连各种异构DB,如oracle_fdw,mysql_fdw,file_fdw等,对数据的迁移是很方便的,这是PG的扩展转载 2015-04-22 17:12:49 · 17989 阅读 · 0 评论 -
PostgreSQL 添加各种约束语法
1. 添加主键alter table goods add primary key(sid);2. 添加外键alter table orders add foreign key(goods_id) references goods(sid) on update cascadeon delete cascade;on update cascade: 被引用行更新时,引用行自动更原创 2015-04-04 22:23:31 · 36981 阅读 · 4 评论 -
PostgreSQL 临时表
转载自: http://blog.163.com/digoal@126/blog/static/1638770402012101575032326/SQL标准中临时表是一次创建, 以后使用的时候无须再次创建的. 并且每个会话保持各自的数据.但是在PostgreSQL中, 临时表的使用有所改变.1. 临时表在会话结束后会自动删除(或者在事务结束后删除on commit drop)转载 2015-03-02 11:11:01 · 15413 阅读 · 0 评论 -
PostgreSQL do language plpgsql $$ ...
PostgreSQL支持无需创建出来即可执行的function,关键字是do language plpgsql $$declarebegin......end $$;示例如下:postgres=# do language plpgsql $$postgres$# declarepostgres$# beginpostgres$# raise notice 'hello原创 2015-02-27 08:30:14 · 5031 阅读 · 1 评论 -
PostgreSQL 枚举类型的使用
1. 定义enum: (男、女)postgres=# create type sex as enum('male','female');CREATE TYPE2. 创建表,字段以该枚举类型为类型postgres=# create table person(id serial,name character varying,p_sex sex);CREATE TABLEpostgr原创 2015-02-28 15:03:16 · 6533 阅读 · 0 评论 -
PostgreSQL调出每一次sql执行所花的时间
使用命令: \timingpostgres=# \timingTiming is on.postgres=#postgres=# select * from tb4 where name is not null;id | name----+------10 | john 9 | john 8 | john 7 | john 6 | joh原创 2015-02-13 09:12:04 · 10656 阅读 · 1 评论 -
PostgreSQL 逻辑删除的表的唯一约束的控制
很多时候,当我们的数据很重要,删除采用的是逻辑删除,仅仅是在记录上指定一个删除标记;这个时候,我们又需要该表的某一字段唯一(未删除的保持唯一,已经有删除标记的行不管),仅仅是采用数据库本身的check unique就不行了。原创 2015-02-09 14:08:33 · 8657 阅读 · 3 评论 -
PostgreSQL PITR示例 (物理备份+WAL日志)
参考地址:http://blog.sciencenet.cn/home.php?mod=space&uid=419883&do=blog&id=537939我将步骤整理如下:PITR:时间点恢复(Point In Time Recovery)一、备份1. 创建表,插入100W测试数据: create table foo(id integer);insert into foo原创 2015-02-09 13:48:33 · 3191 阅读 · 0 评论