postgresql
o0wufan0o
这个作者很懒,什么都没留下…
展开
-
postgresql insert
INSERT INTO sfa_t_cir_ordercode ( ordercodeid, orderdate, ordercode ) VALUES ( cast(sfa_f_new_uuid(CAST(random() * 100000 AS INTEGER)) AS uuid), to_char(now(),'yyyy-mm-dd'),原创 2013-07-02 09:12:33 · 747 阅读 · 0 评论 -
postgresql常用攻略
1.不支持top 1 可以使用limit order by2.select语句给变量赋值的方式--第一种写法select into tempstorename, tempstorerankname storename, storerankname from tbasstoretest where cast(storeid as varchar) = storeidp;原创 2013-07-02 09:10:34 · 988 阅读 · 0 评论 -
postgresql日期格式转换
--上月天数select EXTRACT(DAY from date_trunc('month', CURRENT_DATE)- interval '0 month'- interval '1 day'); --当前时间select now();--当前年月日select current_date当前年当前月select extract(year fro原创 2013-07-04 08:20:06 · 81842 阅读 · 0 评论 -
postgresql行转列并拼接字符串
有这样一张表:select * from liutest order by 1;id | kw ----+-------- 1 | big 1 | hello 2 | oracle 2 | small 2 | apple 3 | shit(6 rows)我想按id把kw拼接成一个字符串,在网上查到一种方法:select id,原创 2013-08-26 18:05:41 · 9440 阅读 · 0 评论 -
postgresql截取指定字符左边的所有字符
"M.101.5.2""M.101.5.3""M.101.22.4""M.101.11.15"原创 2014-10-23 11:36:27 · 12018 阅读 · 0 评论 -
postgresql isnumeric
WITH test(x) AS ( VALUES (''), ('.'), ('.0'), ('0.'), ('0'), ('1'), ('123'), ('123.456'), ('abc'), ('1..2'), ('1.2.3.4'))SELECT x , x ~ '^[0-9]*.?[0-9]*$' AS isnumericFROM test;原创 2014-10-14 16:41:24 · 1705 阅读 · 0 评论 -
PG_全角转半角函数
CREATE OR REPLACE FUNCTION convertstring(text) RETURNS text AS$$ DECLARE len INTEGER; retval text; tmp text; asciival INT4; difference INT4 := ascii('A')-ascii('A'); fu原创 2014-11-18 17:16:09 · 1496 阅读 · 0 评论