PostgreSQL新手入门http://www.ruanyifeng.com/blog/2013/12/getting_started_with_postgresql.html
windows 2003安装问题:
1. problem running post install setup
安装路径权限问题,配置Users的权限为完全控制,就可以了.
自动备份
Windows http://www.oschina.net/question/100267_71299
PostgreSQL自动备份的批处理文件 http://blog.csdn.net/adeng1919/article/details/13022859
@ECHO OFF
echo 正在初始化环境变量. . .
echo.
:: 对于路径中有空格的路径字符串,需要加上英文双引号包裹!否则就会出现命令错误!
set POSTGRESQL_DIR="C:\PostgreSQL\9.3\bin"
set DB_NAME=rhcpm_dev4
set USER="postgres"
:: 数据库管理系统名
set DBMS_NAME=PostgreSQL
set SV_IP="192.168.0.167"
set SV_PORT="5432"
set DBBAK_DIR="C:\PostgreSQL\pg_db_bak"
set CMD_STR="-b -c -C -Ft -U"
:: PostgreSQL的pg_dump命令行工具没有带密码的参数,但是可以设置一个PGPASSWORD的环境变量来提供密码
set PGPASSWORD=1
echo 正在备份数据库. . .
echo.
:: 以下是获得当前系统时间的命令,e.g. 20120503101305
:: 年
set myyy=%date:~0,4%
:: 月
set mymm=%date:~5,2%
:: 日
set mydd=%date:~8,2%
::echo %myyy% %mymm% %mydd%
set /a TODAY=%date:~0,4%%date:~5,2%%date:~8,2%
set _TIME=%time:~0,8%
::echo %_TIME%
set CURRENTTIME=%_time::=%
set CURRENTTIME=%CURRENTTIME: =0%
::set MYDATETIME=%TODAY%-%CURRENTTIME%
::set MYDATETIME=%TODAY%-%_TIME%
::set MYDATETIME=%myyy%-%mymm%-%mydd%_%CURRENTTIME%
set MYDATETIME=%TODAY%_%CURRENTTIME%
D:
cd %POSTGRESQL_DIR%
:: 使用PostgreSQL提供的pg_dump命令将具体数据库导出为.sql文件
%POSTGRESQL_DIR%\pg_dump.exe -h %SV_IP% -p %SV_PORT% -b -c -C -Ft -U %USER% %DB_NAME%>%DBBAK_DIR%\%DB_NAME%_%MYDATETIME%.backup
echo 备份结束
echo
exit
PostgreSQL自动备份(backup)与恢复(restore)数据库(图解)https://xly3000.wordpress.com/2012/03/07/postgresql%E8%87%AA%E5%8A%A8%E5%A4%87%E4%BB%BDbackup%E4%B8%8E%E6%81%A2%E5%A4%8Drestore%E6%95%B0%E6%8D%AE%E5%BA%93%E5%9B%BE%E8%A7%A3/
Linux http://mlm.iyunv.com/blog/1129709
序列
postgresql 创建序列方法_postgresql create sequence http://www.jsjtt.com/shujuku/postgresql/32.html
PostgreSQL 序列(SEQUENCE) http://www.cnblogs.com/mchina/archive/2013/04/10/3012493.html
PostgreSQL 8.1 序列操作函数 http://www.php100.com/manual/PostgreSQL8/functions-sequence.html
-- DROP SEQUENCE seq_user_id;
CREATE SEQUENCE seq_user_id INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1;
ALTER TABLE seq_route_id OWNER TO postgres; 查找所有数据库
SELECT datname FROM pg_database; 查找所有用户表
SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%' ORDER BY tablename;
查找表的所有字段
SELECT a.attnum,a.attname AS field,t.typname AS type,a.attlen AS length,a.atttypmod AS lengthvar,a.attnotnull AS notnull from pg_class c,pg_attribute a,pg_type t where c.relname='rh_dimension' and a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid
postgres查询表中所有字段、主键、唯一、外键、是否为空
select 'true' as list,'true' as edit,'false' as search,a.attname as column_name,format_type(a.atttypid,a.atttypmod) as data_type,
(case
when atttypmod-4>0 then atttypmod-4
else 0
end)data_length,
(case
when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='p')>0 then 'Y'
else 'N'
end) as P,
(case
when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='u')>0 then 'Y'
else 'N'
end) as U,
(case
when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='f')>0 then 'Y'
else 'N'
end) as R,
(case
when a.attnotnull=true then 'Y'
else 'N'
end) as nullable,
col_description(a.attrelid,a.attnum) as comment,'XEditText' as control
from pg_attribute a
where attstattarget=-1 and attrelid = (select oid from pg_class where relname ='userinfo')