Home | 簡體中文 | 繁體中文 | 雜文 | 打賞(Donations) | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎專欄 | Search | 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=#