PostgreSQL的隐藏功能

select pg_size_pretty(200 * 1024)

可以使用以下命令复制数据库:

createdb -T old_db new_db

文档说明:

这不是一个通用的 "COPY DATABASE" 工具

但对我来说效果很好，速度比以下方式更快:

createdb new_db

pg_dump old_db | psql new_db

pgcrypto：比许多编程语言的加密模块提供更多的加密功能，所有这些功能都可以直接从数据库中访问。它使得加密工作变得非常容易而又正确。

1.) 当你需要在查询中添加注释时，可以使用嵌套的注释。

SELECT /* my comments, that I would to see in PostgreSQL log */
       a, b, c
   FROM mytab;

2.) 从数据库中所有的text和varchar字段中删除尾随空格。

do $$
declare
    selectrow record;
begin
for selectrow in
select 
       'UPDATE '||c.table_name||' SET '||c.COLUMN_NAME||'=TRIM('||c.COLUMN_NAME||')  WHERE '||c.COLUMN_NAME||' ILIKE ''% '' ' as script
from (
       select 
          table_name,COLUMN_NAME
       from 
          INFORMATION_SCHEMA.COLUMNS 
       where 
          table_name LIKE 'tbl%'  and (data_type='text' or data_type='character varying' )
     ) c
loop
execute selectrow.script;
end loop;
end;
$$;

3.) 我们可以使用窗口函数非常有效地删除重复行：

DELETE FROM tab 
  WHERE id IN (SELECT id 
                  FROM (SELECT row_number() OVER (PARTITION BY column_with_duplicate_values), id 
                           FROM tab) x 
                 WHERE x.row_number > 1);

DELETE FROM tab 
  WHERE ctid = ANY(ARRAY(SELECT ctid 
                  FROM (SELECT row_number() OVER (PARTITION BY column_with_duplicate_values), ctid 
                           FROM tab) x 
                 WHERE x.row_number > 1));

4.) 当我们需要识别服务器状态时，可以使用一个函数：

SELECT pg_is_in_recovery();

5.) 获取函数的DDL命令。

select pg_get_functiondef((select oid from pg_proc where proname = 'f1'));

6.) PostgreSQL中安全地更改列数据类型

create table test(id varchar );
insert into test values('1');
insert into test values('11');
insert into test values('12');

select * from test
--Result--
id
character varying
--------------------------
1
11
12

从上表可以看出，我在“id”列中使用了数据类型“character varying”。但这是个错误，因为我一直使用整数作为id。因此，在这里使用varchar是一个不好的做法。所以让我们尝试将列类型更改为整数。

ALTER TABLE test ALTER COLUMN id TYPE integer;

错误：列“id”无法自动转换为整数类型SQL状态：42804提示：指定一个USING表达式来执行转换

ALTER TABLE test ALTER COLUMN id  TYPE integer USING (id ::integer);

它可以工作。

7.) 知道谁连接到数据库
这是一个监控命令。要知道哪个用户连接到哪个数据库，包括他们的IP和端口，请使用以下SQL：

SELECT datname,usename,client_addr,client_port FROM pg_stat_activity ;

8.) 在不重启服务器的情况下重新加载PostgreSQL配置文件

PostgreSQL的配置参数位于像postgresql.conf和pg_hba.conf这样的特殊文件中。通常，您可能需要更改这些参数。但是，对于某些参数要生效，我们通常需要重新加载配置文件。当然，重新启动服务器可以做到这一点。但在生产环境中，为了设置某些参数而仅仅因为这个原因就重启正在被数千人使用的数据库是不可取的。在这种情况下，我们可以使用以下函数在不重启服务器的情况下重新加载配置文件：

select pg_reload_conf();

请记住，这并不适用于所有参数，有些参数更改需要完全重新启动服务器才能生效。

9.) 获取当前数据库集群的数据目录路径

在系统中可能设置了多个PostgreSQL实例(集群)，通常在不同的端口或其他位置。在这种情况下，找到哪个实例使用哪个目录(物理存储目录)是一项繁琐的任务。在这种情况下，我们可以在我们感兴趣的集群中的任何数据库中使用以下命令来获取目录路径:

SHOW data_directory;

相同的函数可以用于更改群集的数据目录，但需要重新启动服务器：

SET data_directory to new_directory_path;

10.) 查找一个CHAR类型的数据是否为日期类型

create or replace function is_date(s varchar) returns boolean as $$
begin
  perform s::date;
  return true;
exception when others then
  return false;
end;
$$ language plpgsql;

用法：以下内容将返回True

select is_date('12-12-2014')
select is_date('12/12/2014')
select is_date('20141212')
select is_date('2014.12.12')
select is_date('2014,12,12')

11.) 在PostgreSQL中更改所有者

REASSIGN OWNED BY sa  TO postgres;

12.) PGADMIN PLPGSQL DEBUGGER

这里解释得很清楚

这是我收藏的一些不太为人知的功能列表。

事务性DDL

在Postgres中，几乎每个SQL语句都是事务性的。如果关闭自动提交，则可以实现以下操作：

drop table customer_orders;
rollback;
select *
from customer_orders;

范围类型和排除约束

据我所知，Postgres 是唯一一个允许您创建检查两个范围是否重叠的约束条件的关系型数据库管理系统。例如，一个包含产品价格及其“有效起止日期”的表：

create table product_price
(
   price_id      serial        not null primary key,
   product_id    integer       not null references products,
   price         numeric(16,4) not null,
   valid_during  daterange not null
);

NoSQL特性

hstore扩展提供了一个灵活且非常快速的键/值存储，可用于在数据库的某些部分需要“无模式”时使用。JSON是另一种选项，可用于以无模式方式存储数据。

insert into product_price 
  (product_id, price, valid_during)
values 
  (1, 100.0, '[2013-01-01,2014-01-01)'),
  (1,  90.0, '[2014-01-01,)');


-- querying is simply and can use an index on the valid_during column
select price
from product_price
where product_id = 42
  and valid_during @> date '2014-10-17';

在一个拥有700,000行的表上执行上述操作的执行计划：

Index Scan using check_price_range on public.product_price  (cost=0.29..3.29 rows=1 width=6) (actual time=0.605..0.728 rows=1 loops=1)
  Output: price
  Index Cond: ((product_price.valid_during @> '2014-10-17'::date) AND (product_price.product_id = 42))
  Buffers: shared hit=17
Total runtime: 0.772 ms

alter table product_price
  add constraint check_price_range 
  exclude using gist (product_id with =, valid_during with &&)

无限

Postgres不需要一个“真实”的远期日期，可以将日期与无穷大进行比较。例如，在不使用日期范围时，您可以执行以下操作：

insert into product_price 
  (product_id, price, valid_from, valid_until)
values 
  (1,  90.0, date '2014-01-01', date 'infinity');

可写公共表达式

您可以在单个语句中执行删除、插入和选择操作：

with old_orders as (
   delete from orders
   where order_date < current_date - interval '10' year
   returning *
), archived_rows as (
   insert into archived_orders 
   select * 
   from old_orders
   returning *
)
select *
from archived_rows;

内存存储用于一次性数据/全局变量

您可以创建一个位于RAM中的表空间，并在该表空间中创建表（可能是未记录的，在9.1中），以存储您想要跨会话共享的一次性数据/全局变量。

http://magazine.redhat.com/2007/12/12/tip-from-an-rhce-memory-storage-on-postgresql/

咨询锁定

这些在手册的一个不常见区域中有记录：

http://www.postgresql.org/docs/9.0/interactive/functions-admin.html

它们有时比获取大量行级锁更快，可以用于解决FOR UPDATE未实现的情况（例如递归CTE查询）。

重命名旧数据库比mysql自己做方便。只需使用以下命令：

ALTER DATABASE name RENAME TO new_name