postgres01

pg常用命令

\password:设置当前登录用户的密码
\h:查看SQL命令的解释,比如\h select。
\?:查看psql命令列表。
\l:列出所有数据库。
\c [database_name]:连接其他数据库。
\d:列出当前数据库的所有表格。
\d [table_name]:列出某一张表格的结构。
\du:列出所有用户。
\e:打开文本编辑器。
\conninfo:列出当前数据库和连接的信息。
\password [user]: 修改用户密码
\q:退出


# Connect to PostgreSQL, using a superuser named 'postgres'
psql -U postgres -h localhost

pg 测试数据生成

select id from generate_series(1,10) t(id);  

#随机数
select (random()*100)::int from generate_series(1,10);  

#生成随机字符串
select md5(random()::text) from generate_series(1,10); 


#随机汉字
create or replace function gen_hanzi(int) returns text as $$    
declare    
	res text;    
begin    
	if $1 >=1 then    
		select string_agg(chr(19968+(random()*20901)::int), '') into res from generate_series(1,$1);    
		return res;    
	end if;    
	return null;    
end;    
$$ language plpgsql strict;   

select gen_hanzi(10) from generate_series

#随机数组
create or replace function gen_rand_arr(int,int) returns int[] as $$    
	select array_agg((random()*$1)::int) from generate_series(1,$2);    
$$ language sql strict;    



# 随机身份证
create or replace function gen_id(    
	a date,    
	b date    
)     
returns text as $$    
select lpad((random()*99)::int::text, 2, '0') ||     
			 lpad((random()*99)::int::text, 2, '0') ||     
			 lpad((random()*99)::int::text, 2, '0') ||     
			 to_char(a + (random()*(b-a))::int, 'yyyymmdd') ||     
			 lpad((random()*99)::int::text, 2, '0') ||     
			 random()::int ||     
			 (case when random()*10 >9 then 'X' else (random()*9)::int::text end ) ;    
$$ language sql strict;    

select gen_id('1900-01-01', '2017-10-16') from generate_series(1,10);  

gin 倒排索引

CREATE INDEX "name " ON "tablename" USING gin(to_tsvector('jiebacfg', "columnname"));

CREATE INDEX rumidx ON rum_test USING rum (c1 rum_tsvector_ops);

pg 查询

4#全文检索
select * from tbl where tsvector_col @@ 'postgres & china | digoal:A' order by ts_rank(tsvector_col, 'postgres & china | digoal:A')
 limit xx;


5正则查询。(可以使用pg_trgm和gin索引)

select * from tbl where col ~ '^a[0-9]{1,5}\ +digoal$'; 


6、相似查询。(可以使用pg_trgm和gin索引)

select * from tbl order by similarity(col, 'postgre') desc limit 10;  



7ADHOC查询,任意字段组合查询。(通过bloom index, multi-index bitmap scan, gin-index bitmap scan 等索引都可以实现)

select * from tbl where a=? and b=? or c=? and d=? or e between ? and ? and f in (?); 



5、搜索操作符为@@

select * from tbl where $tsvector_col @@ $tsquery;  



5、搜索操作符为@@

select * from tbl where $tsvector_col @@ $tsquery;  


2、支持文档结构,语法如下

As in basic tsquery input, weight(s) can be attached to each lexeme to restrict it to match only tsvector lexemes of 
those weight(s). For example:

SELECT to_tsquery('english', 'Fat | Rats:AB');  
    to_tsquery      
------------------  
 'fat' | 'rat':AB  
 
 
 
 SELECT title, ts_rank_cd(textsearch, query) AS rank  
FROM apod, to_tsquery('neutrino|(dark & matter)') query  
WHERE query @@ textsearch  
ORDER BY rank DESC  
LIMIT 10;  
                     title                     |   rank  
-----------------------------------------------+----------  
 Neutrinos in the Sun                          |      3.1  
 The Sudbury Neutrino Detector                 |      2.4  
 A MACHO View of Galactic Dark Matter          |  2.01317  
 Hot Gas and Dark Matter                       |  1.91171  
 The Virgo Cluster: Hot Plasma and Dark Matter |  1.90953  
 Rafting for Solar Neutrinos                   |      1.9  
 NGC 4650A: Strange Galaxy and Dark Matter     |  1.85774  
 Hot Gas and Dark Matter                       |   1.6123  
 Ice Fishing for Cosmic Neutrinos              |      1.6  
 Weak Lensing Distorts the Universe            | 0.818218