注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

浅谈 PostgreSQL 类型转换  

2011-05-17 10:24:53|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

    

        类似Oracle ,PostgreSQL也有强大的类型转换函数, 下面仅举两个类型转换例子。
     
--1 例子
postgres=# select 1/4;
 ?column?
----------
        0
(1 row)

        在PG里如果想做除法并想保留小数,用上面的方法却行不通,因为"/" 运算结果为取整,并
且会截掉小数部分。
 

--2 类型转换
postgres=# select round(1::numeric/4::numeric,2);
 round
-------
  0.25
(1 row)

  备注:类型转换后,就能保留小数部分了。

francs=> select oid,relname,reltuples from pg_class where oid='test_1'::regclass;

  oid  | relname | reltuples 

-------+---------+-----------

 16416 | test_1  |         6

备注: 'test_1'::regclass 这里将表名转换成表的 oid,其它用法参考本文属的附二。


--3 也可以通过 cast 函数进行转换
postgres=# select round( cast ( 1 as numeric )/ cast( 4 as numeric),2);
 round
-------
  0.25
(1 row)


--4 关于 cast 函数的用法
postgres=# SELECT substr(CAST (1234 AS text), 3,1);
 substr
--------
 3
(1 row)


--5 附一: PostgreSQL 类型转换函数

Function Return Type Description Example


to_char
(timestamp, text

)


text
convert time stamp to string
to_char(current_timestamp, 'HH12:MI:SS')

to_char
(
interval, text
)

text
convert interval to string
to_char(interval '15h 2m 12s', 'HH24:MI:SS')

to_char
(
int, text
)

text
convert integer to string
to_char(125, '999')


to_char
(
double

precision
, text
)


text
convert real/double precision to string
to_char(125.8::real, '999D9')

to_char
(
numeric, text
)

text
convert numeric to string
to_char(-125.8, '999D99S')

to_date
(text, text
)

date
convert string to date
to_date('05 Dec 2000', 'DD Mon YYYY')

to_number
(
text, text
)

numeric
convert string to numeric
to_number('12,454.8-', '99G999D9S')

to_timestamp
(
text, text
)

timestamp with time zone
convert string to time stamp
to_timestamp('05 Dec 2000', 'DD Mon YYYY')

to_timestamp
(
double precision
)

timestamp with time zone
convert Unix epoch to time stamp
to_timestamp(1284352323)


附二 Table 8-23. Object Identifier Types

NameReferencesDescriptionValue Example
oidanynumeric object identifier564182
regprocpg_procfunction namesum
regprocedurepg_procfunction with argument typessum(int4)
regoperpg_operatoroperator name+
regoperatorpg_operatoroperator with argument types*(integer,integer) or -(NONE,integer)
regclasspg_classrelation namepg_type
regtypepg_typedata type nameinteger
regconfigpg_ts_configtext search configurationenglish
regdictionarypg_ts_dicttext search dictionarysimple
  评论这张
 
阅读(45728)| 评论(1)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

{list a as x} {if !!x}
88 n.f40" class=c/4057672t="p://blog.163.com/${x.visitorName}/"> {if x.visitorName==visitor.userName} c/4057672t="on.)" onpan title="pe}" onerro88 1;">
loca40" class="cwd bd88 name="jst" id=span " tar, a as x} {if !!x}
088888 classc/4057672t="mag萈jnt6r;">f">蚮an>

< y.c/4057672B} div class}/?veFrmebor两竧t>sp;ayer fc06"> f">蚮an> n #--引用记录-- pan

0icn0fi|&nb记录"Postgrespan ti<{/isingle:="nclepan tinJEVi9dWk1KTzJlMTU288 lix.visito"hotarea apan title=anRecommendCount">0
:{}a40" c unt"> single:" i7 span '1', ${x. erB} d(1}n. x. erB} malin;">f">
:{r unt"> single:" i7 span '1', ${x. er Pag}" . x. er "_bl;">f">

'lix.visito40" clag.t" tar, single:" id=span '1', ://inpin.blog.163.co x.get="_bla/${x.om= class}/?c/4057672B} reAndD${x. reAn|defazta:"";">f">f">蚮an> n #--随机'lix.visito40" clag.t" tar, single:" id=span '1', ://inpin.blog.163.co x.get="_bla/${x.om= class}/?om=='wapR reAndD${x. reAn|defazta:"";">f">f">蚮an> n #--首页_spa-- pan

'lix.visito40" clag.t" tar, single:" id=span " tar, 05${x.cwd d(1|defazta:"";">f">f">f">蚮an> n #-- clearfix'> -- pan
8888 lix.visito40" civ> pan titl8888 name="jstspan " tar, dIcon">  x.get="_bla/${x.om= class|defazta:""om/ reAndD${x. reAn|defazta:"";">f">f">蚮an> n #--被_spa日志-- pan
'lix.visito40" clag.t" tar, single:" id=span '1', eitm()}${x.om= class}/ . e}ass reAn,26r;">f">蚮an> n 88 #--上一篇,下一篇-- pan
unt">040" clag.tsingle:span dIcon"> <="125" selfRecomBlo${cwd Detail.lt;Bwd div classw-f4${cwd Detail.lt;Bwd TreAn;">f"> /span>
u" name40" clag.tsingle:span '1', ="125" selfRecomBlo${cwd Detail.nosdBwd div classw-f4${cwd Detail.nosdBwd TreAn;">f"> n #-- ;hei -- pan
'1;">
/di> n }