postgresql 数据类型转换

1、postgresql 数据类型转换,日期操作函数各种数据类型(日期/时间、integer、floating point和numeric)转换成格式化的字符串以及反过来从格式化的字符串转换成指定的数据类型。下面列出了这些函数，它们都遵循一个公共的调用习 惯：第一个参数是待格式化的值，而第二个是定义输出或输出格式的模板。函数返回类型描述例子to_char(timestamp, text)text把时间戳转换成字串to_char(current_timestamp, HH12:MI:SS)to_char(interval, text)text把时间间隔转为字串to_char(interval 15h

2、 2m 12s, HH24:MI:SS)to_char(int, text)text把整数转换成字串to_char(125, 999)to_char(double precision, text)text把实数/双精度数转换成字串to_char(125.8:real, 999D9)to_char(numeric, text)text把numeric转换成字串to_char(-125.8, 999D99S)to_date(text, text)date把字串转换成日期to_date(05 Dec 2000, DD Mon YYYY)to_timestamp(text, text)timestam

3、p把字串转换成时间戳to_timestamp(05 Dec 2000, DD Mon YYYY)to_timestamp(double)timestamp把UNIX纪元转换成时间戳to_timestamp(200120400)to_number(text, text)numeric把字串转换成numericto_number(12,454.8-, 99G999D9S)用于日期/时间格式化的模式：模式描述HH一天的小时数(01-12)HH12一天的小时数(01-12)HH24一天的小时数(00-23)MI分钟(00-59)SS秒(00-59)MS毫秒(000-999)US微秒(000000-99

4、9999)AM正午标识(大写)Y,YYY带逗号的年(4和更多位)YYYY年(4和更多位)YYY年的后三位YY年的后两位Y年的最后一位MONTH全长大写月份名(空白填充为9字符)Month全长混合大小写月份名(空白填充为9字符)month全长小写月份名(空白填充为9字符)MON大写缩写月份名(3字符)Mon缩写混合大小写月份名(3字符)mon小写缩写月份名(3字符)MM月份号(01-12)DAY全长大写日期名(空白填充为9字符)Day全长混合大小写日期名(空白填充为9字符)day全长小写日期名(空白填充为9字符)DY缩写大写日期名(3字符)Dy缩写混合大小写日期名(3字符)dy缩写小写日期名(3

5、字符)DDD一年里的日子(001-366)DD一个月里的日子(01-31)D一周里的日子(1-7；周日是1)W一个月里的周数(1-5)(第一周从该月第一天开始)WW一年里的周数(1-53)(第一周从该年的第一天开始)下面是PostgreSQL中支持的时间/日期操作符的列表：操作符例子结果+date 2001-09-28 + integer 7date 2001-10-05+date 2001-09-28 + interval 1 hourtimestamp 2001-09-28 01:00+date 2001-09-28 + time 03:00timestamp 2001-09-28 03:

6、00+interval 1 day + interval 1 hourinterval 1 day 01:00+timestamp 2001-09-28 01:00 + interval 23 hourstimestamp 2001-09-29 00:00+time 01:00 + interval 3 hourstime 04:00- interval 23 hoursinterval -23:00-date 2001-10-01 - date 2001-09-28integer 3-date 2001-10-01 - integer 7date 2001-09-24-date 2001-0

7、9-28 - interval 1 hourtimestamp 2001-09-27 23:00-time 05:00 - time 03:00interval 02:00-time 05:00 - interval 2 hourstime 03:00-timestamp 2001-09-28 23:00 - interval 23 hourstimestamp 2001-09-28 00:00-interval 1 day - interval 1 hourinterval 23:00-timestamp 2001-09-29 03:00 - timestamp 2001-09-27 12:

8、00interval 1 day 15:00*interval 1 hour * double precision 3.5interval 03:30/interval 1 hour / double precision 1.5interval 00:40postgresql 字符串转整数 int、integerpostgresql 字符串转整数 int、integercode1234567-把1234转成整数select cast(1234 as integer ) ;-用substring截取字符串，从第8个字符开始截取2个字符：结果是12select cast(substring(123

9、4abc12,8,2) as integer)-使用to_number函数来转换成整数-to_number(text, text) 返回的类型 numeric 把字串转换成numeric to_number(12,454.8-, 99G999D9S)select to_number(12121,999999999)用于数值格式化的模板模式：模式 描述9 带有指定数值位数的值0 带前导零的值.(句点) 小数点,(逗号) 分组(千)分隔符PR 尖括号内负值S 带符号的数值L 货币符号D 小数点G 分组分隔符MI 在指明的位置的负号(如果数字 0)SG 在指明的位置的正/负号postgresql 获

10、取当前时间 ,操作当前时间current_date今天的日期例子：sql：select current_dateresult：2012-12-17current_time现在的时间例子：sql：select current_timeresult：18:16:09.984+08current_timestamp日期和时间例子：sql：select current_timestampresult：2012-12-17 18:17:03.015+08postgresql windows下修改帐号密码 (图文)重新设置postgres用户的密码方法： 1、关闭数据库服务 2、进入数据库的工作空间目录

11、(如果是建库是没有另外指定，应该就是postgresql安装目录下的 data 目录) 3、编辑修改文件 pg_hba.conf， 把连接权限设置的 md5 加密方式 改成 trust 以我的为例，原本设置是# IPv4 local connections:host all all 127.0.0.1/32 md5修改为# IPv4 local connections:host all all 127.0.0.1/32trust 4、重新新启动postgresql数据库服务 5、可以不用密码就能用postgres用户登陆，然后执行修改用户密码的操作 alter user postgres wi

12、th password foobar; 6、修改 pg_hba.conf 文件，改回到 md5 方式，再重启数据库服务设置完成。postgresql 日期计算，时间加减 方法在PostgreSQL中可以直接对时间进行加减运算：、SELECT now():timestamp + 1 year; -当前时间加1年SELECT now():timestamp + 1 month; -当前时间加一个月SELECT now():timestamp + 1 day; -当前时间加一天SELECT now():timestamp + 1 hour; -当前时间加一个小时SELECT now():timest

13、amp + 1 min; -当前时间加一分钟SELECT now():timestamp + 1 sec; -加一秒钟select now():timestamp + 1 year 1 month 1 day 1 hour 1 min 1 sec; -加1年1月1天1时1分1秒SELECT now():timestamp + (col | day):interval FROM table -把col字段转换成天 然后相加PostgreSQL 替换字符串方法及字符串操作函数下面是PostgreSQL中提供的字符串操作符列表：替换字符的例子：update ab set a=replace(a,aa

14、a,0)把a字段里面的aaa字符串替换成0函数返回类型描述例子结果string | stringtext字串连接Post | greSQLPostgreSQLbit_length(string)int字串里二进制位的个数bit_length(jose)32char_length(string)int字串中的字符个数char_length(jose)4convert(string using conversion_name)text使用指定的转换名字改变编码。convert(PostgreSQL using iso_8859_1_to_utf8)PostgreSQLlower(string)te

15、xt把字串转化为小写lower(TOM)tomoctet_length(string)int字串中的字节数octet_length(jose)4overlay(string placing string from int for int)text替换子字串overlay(Txxxxas placing hom from 2 for 4)Thomasposition(substring in string)int指定的子字串的位置position(om in Thomas)3substring(string from int for int)text抽取子字串substring(Thomas f

16、rom 2 for 3)homsubstring(string from pattern)text抽取匹配 POSIX 正则表达式的子字串substring(Thomas from .$)massubstring(string from pattern for escape)text抽取匹配SQL正则表达式的子字串substring(Thomas from %#o_a#_ for #)omatrim(leading | trailing | both characters from string)text从字串string的开头/结尾/两边/ 删除只包含characters(缺省是一个空白)的

17、最长的字串trim(both x from xTomxx)Tomupper(string)text把字串转化为大写。upper(tom)TOMascii(text)int参数第一个字符的ASCII码ascii(x)120btrim(string text , characters text)text从string开头和结尾删除只包含在characters里(缺省是空白)的字符的最长字串btrim(xyxtrimyyx,xy)trimchr(int)text给出ASCII码的字符chr(65)Aconvert(string text, src_encoding name, dest_encodi

18、ng name)text把字串转换为dest_encodingconvert( text_in_utf8, UTF8, LATIN1)以ISO 8859-1编码表示的text_in_utf8initcap(text)text把每个单词的第一个子母转为大写，其它的保留小写。单词是一系列字母数字组成的字符，用非字母数字分隔。initcap(hi thomas)Hi Thomaslength(string text)intstring中字符的数目length(jose)4lpad(string text, length int , fill text)text通过填充字符fill(缺省时为空白)，把

19、string填充为长度length。 如果string已经比length长则将其截断(在右边)。lpad(hi, 5, xy)xyxhiltrim(string text , characters text)text从字串string的开头删除只包含characters(缺省是一个空白)的最长的字串。ltrim(zzzytrim,xyz)trimmd5(string text)text计算给出string的MD5散列，以十六进制返回结果。md5(abc)repeat(string text, number int)text重复string number次。repeat(Pg, 4)PgPgPg

20、Pgreplace(string text, from text, to text)text把字串string里出现地所有子字串from替换成子字串to。replace(abcdefabcdef, cd, XX)abXXefabXXefrpad(string text, length int , fill text)text通过填充字符fill(缺省时为空白)，把string填充为长度length。如果string已经比length长则将其截断。rpad(hi, 5, xy)hixyxrtrim(string text , character text)text从字串string的结尾删除只包含character(缺省是个空白)的最长的字rtrim(trimxxxx,x)trimsplit_part(string text, delimiter text, field int)text根据delimiter分隔string返回生成的第field个子字串(1 Base)。split_part(abcdefghi, , 2)defstrpos(string, substring)text声明的子字串的位置。strpos(high,ig)2substr(string, from , count)text抽取子字串。substr(alphabet, 3, 2)phto_ascii(tex