Home | 简体中文 | 繁体中文 | 杂文 | Search | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 作品与服务 | Email

第 6 章 SQL

目录

6.1. "::"数据转换
6.2. ORDER BY 排序
6.3. 递归查询
6.4. returning
6.5. Function
6.5.1. generate_series
6.5.2. 日期/时间
6.5.2.1. Date/Time Operators
6.5.2.2. 当前日期/时间
6.5.2.3. 时间计算
6.5.2.4. to_char() / to_date()
6.5.2.5. EXTRACT, date_part
6.5.2.6. date_trunc
6.5.2.7. 延迟执行
6.5.2.8. 时区
6.5.3. uuid
6.5.4. tablefunc

6.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=#

    	
    	
comments powered by Disqus