目录
- 7.1. "::"数据转换
- 7.2. ORDER BY 排序
- 7.3. 递归查询
- 7.4. returning
- 7.5. Function
-
- 7.5.1. generate_series
- 7.5.2. 日期/时间
-
- 7.5.2.1. Date/Time Operators
- 7.5.2.2. 当前日期/时间
- 7.5.2.3. 时间计算
- 7.5.2.4. to_char() / to_date()
- 7.5.2.5. EXTRACT, date_part
- 7.5.2.6. date_trunc
- 7.5.2.7. 延迟执行
- 7.5.2.8. 时区
- 7.5.3. uuid
- 7.5.4. tablefunc
7.1. "::"数据转换
3.3 “::”数据转换 PostgreSQL 数据之间的转换可以使用“::”操作符。 3.3.1 text to varchar vperson 表gender字段为布尔型(boolean)在视图中要显示为true显示为“先生”,false显示为“女士”CASE WHEN 表达式应该是: CASE WHEN p.gender = true THEN '先生' ELSE '女士' END as gender, 直接使用'先生', '女士' PostgreSQL认为' '中间的字符为text类型,请看下面: postgres=# CREATE OR REPLACE VIEW vperson AS postgres-# SELECT p.uid,p.name, postgres-# CASE WHEN p.gender = true THEN '先生' ELSE '女士' END as gender, postgres-# p.nickname,p.mobile,p.tel,p.fax,p.email,p.province,p.city,p.addre ss,p.postalcode postgres-# FROM "person" p postgres-# Order By p.uid; CREATE VIEW postgres=# \dv vperson List of relations Schema | Name | Type | Owner --------+---------+------+---------- public | vperson | view | postgres (1 row) postgres=# \d person Table "public.person" Column | Type | Modifiers -------------+------------------------+---------------------- uid | integer | not null default 0 name | character varying(20) | not null gender | boolean | not null default 'F' nickname | character varying(20) | mobile | character varying(13) | tel | character varying(20) | not null fax | character varying(20) | email | character varying(60) | province | character varying(10) | not null city | character varying(10) | not null address | character varying(255) | not null postalcode | character varying(6) | not null rate | character varying(20) | default '0' bank | character varying(20) | not null default '' bankaccount | character varying(20) | not null default '' Indexes: person_pkey primary key btree (uid) Check constraints: "person_rate" ((((((rate = '0'::character varying) OR (rate = '1'::character varying)) OR (rate = '2'::character varying)) OR (rate = '3'::character varying)) OR (rate = '4'::character varying)) OR (rate = '5'::character varying)) postgres=# postgres=# \d vperson View "public.vperson" Column | Type | Modifiers ------------+------------------------+----------- uid | integer | name | character varying(20) | gender | text | nickname | character varying(20) | mobile | character varying(13) | tel | character varying(20) | fax | character varying(20) | email | character varying(60) | province | character varying(10) | city | character varying(10) | address | character varying(255) | postalcode | character varying(6) | View definition: SELECT p.uid, p.name, CASE WHEN (p.gender = true) THEN '先生':: text ELSE '女士'::text END AS gender, p.nickname, p.mobile, p.tel, p.fax, p.emai l, p.province, p.city, p.address, p.postalcode FROM person p ORDER BY p.uid; 使用“::”将test 转为varchar: CASE WHEN p.gender = true THEN '先生'::varchar(2) ELSE '女士'::varchar(2) END as gender, 例: CREATE OR REPLACE VIEW vperson AS SELECT p.uid,p.name, CASE WHEN p.gender = true THEN '先生'::varchar(2) ELSE '女士'::varchar(2) END as gender, p.nickname,p.mobile,p.tel,p.fax,p.email,p.province,p.city,p.address,p.postalcode FROM "person" p Order By p.uid; postgres=# drop view vperson ; DROP VIEW postgres=# CREATE OR REPLACE VIEW vperson AS postgres-# SELECT p.uid,p.name, postgres-# CASE WHEN p.gender = true THEN '先生'::varchar(2) ELSE '女士'::varchar(2) END as gender, postgres-# p.nickname,p.mobile,p.tel,p.fax,p.email,p.province,p.city,p.address,p.postalcode postgres-# FROM "person" p postgres-# Order By p.uid; CREATE VIEW postgres=# \d vperson View "public.vperson" Column | Type | Modifiers ------------+------------------------+----------- uid | integer | name | character varying(20) | gender | character varying(2) | nickname | character varying(20) | mobile | character varying(13) | tel | character varying(20) | fax | character varying(20) | email | character varying(60) | province | character varying(10) | city | character varying(10) | address | character varying(255) | postalcode | character varying(6) | View definition: SELECT p.uid, p.name, CASE WHEN (p.gender = true) THEN ('先生'::character varying)::character varying(2) ELSE ('女士'::character varying)::character varying(2) END AS gender, p.nickname, p.mobile, p.tel, p.fax, p.email, p.province, p.city, p.address, p.postalcode FROM person p ORDER BY p.uid; postgres=#
原文出处:Netkiller 系列 手札
本文作者:陈景峯
转载请与作者联系,同时请务必标明文章原始出处和作者信息及本声明。