create type/cast/ENUM

最新推荐文章于 2023-11-22 21:38:33 发布

bielidefeng

最新推荐文章于 2023-11-22 21:38:33 发布

阅读量896

点赞数

CC 4.0 BY-SA版权

分类专栏： postgresql

本文链接：https://blog.youkuaiyun.com/bielidefeng/article/details/27650031

postgresql 专栏收录该内容

33 篇文章

订阅专栏

本文介绍如何在PostgreSQL中使用枚举类型，并演示了创建枚举类型、使用枚举类型字段、定义枚举类型的默认值及范围查询等操作。此外，还详细解释了类型转换的过程，包括显式与隐式转换的实现方式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

create type： http://www.postgresql.org/docs/9.3/static/sql-createtype.html

enum: http://www.postgresql.org/docs/9.3/static/datatype-enum.html， http://www.postgresql.org/docs/9.3/static/functions-enum.html


digoal=# CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TYPE
digoal=# CREATE TABLE person (
digoal(#     name text,
digoal(#     current_mood mood
digoal(# );
CREATE TABLE
digoal=# INSERT INTO person VALUES ('Moe', 'happy');
INSERT 0 1
digoal=# SELECT * FROM person;
 name | current_mood 
------+--------------
 Moe  | happy
(1 row)
digoal=# select enum_first(null::mood);   
 enum_first 
------------
 sad
(1 row)
digoal=# select enum_range('ok'::mood,null);   
 enum_range 
------------
 {ok,happy}
(1 row)

create cast: http://www.postgresql.org/docs/9.3/static/sql-createcast.html， http://www.php100.com/manual/PostgreSQL8/sql-createcast.html

drop cast: http://www.postgresql.org/docs/9.3/static/sql-dropcast.html


CREATE CAST (source_type AS target_type)
    WITH FUNCTION function_name (argument_type [, ...])
    [ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CAST (source_type AS target_type)
    WITHOUT FUNCTION
    [ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CAST (source_type AS target_type)
    WITH INOUT
    [ AS ASSIGNMENT | AS IMPLICIT ]
WITHOUT FUNCTION：
表示源数据类型和目标数据类型是二进制兼容的， 所以不需要什么函数来执行转换。
AS ASSIGNMENT：
表示转换可以在赋值环境里隐含调用。
AS IMPLICIT：
表示这个转换可以在任何环境里隐含调用。


digoal=# select cast(24 as text);
 text 
------
 24
(1 row)
digoal=# CREATE OR REPLACE FUNCTION increment(i integer) RETURNS text AS $$
digoal$#         BEGIN
digoal$#                 RETURN 'aaaaa'||i;
digoal$#         END;
digoal$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
digoal=# CREATE CAST (int AS text) WITH FUNCTION increment(int) AS ASSIGNMENT;                    
CREATE CAST
digoal=# select cast(24 as text);
  text   
---------
 aaaaa24
(1 row)
digoal=# drop cast (int as text);
DROP CAST
digoal=# select cast(24 as text);
 text 
------
 24
(1 row)
digoal=# select 2>=cast(24 as text)||''; 
ERROR:  operator does not exist: integer >= text
LINE 1: select 2>=cast(24 as text)||'';
                ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
digoal=# CREATE CAST (int AS text) WITH FUNCTION increment(int) AS IMPLICIT;
CREATE CAST
digoal=# select 2>=cast(24 as text)||'';                                    
 ?column? 
----------
 false
(1 row)