第 7 章 SQL

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