📄 cast.out
字号:
1 row inserted/updated/deletedij> -- tab1 type -> its tab1 typeselect cast(i as integer) from tab1;1 -----------1 NULL ij> select cast(s as smallint) from tab1;1 ------1 NULL ij> select cast(l as bigint) from tab1;1 --------------------1 NULL ij> select cast(c as char(10)) from tab1;1 ----------char NULL ij> select cast(v as char varying(10)) from tab1;1 ----------varchar NULL ij> select cast(d as double precision) from tab1;1 ----------------------1.1 NULL ij> select cast(r as float) from tab1;1 ----------------------1.100000023841858 NULL ij> select cast(dt as date) from tab1;1 ----------1990-10-10NULL ij> select cast(t as time) from tab1;1 --------11:11:11NULL ij> select cast(ts as timestamp) from tab1;1 --------------------------xxxxxxFILTERED-TIMESTAMPxxxxxNULL ij> select cast(dc as dec) from tab1;1 ------1 NULL ij> -- try a few others where we try all conversionsselect cast(i as integer) from tab1;1 -----------1 NULL ij> select cast(i as smallint) from tab1;1 ------1 NULL ij> select cast(i as bigint) from tab1;1 --------------------1 NULL ij> select cast(i as char(10)) from tab1;1 ----------1 NULL ij> select cast(i as char varying(10)) from tab1;ERROR 42846: Cannot convert types 'INTEGER' to 'VARCHAR'.ij> select cast(i as double precision) from tab1;1 ----------------------1.0 NULL ij> select cast(i as float) from tab1;1 ----------------------1.0 NULL ij> select cast(i as date) from tab1;ERROR 42846: Cannot convert types 'INTEGER' to 'DATE'.ij> select cast(i as time) from tab1;ERROR 42846: Cannot convert types 'INTEGER' to 'TIME'.ij> select cast(i as timestamp) from tab1;ERROR 42846: Cannot convert types 'INTEGER' to 'TIMESTAMP'.ij> select cast(i as dec) from tab1;1 ------1 NULL ij> -- try a few othersselect cast(c as integer) from tab1;1 -----------ERROR 22018: Invalid character string format for type INTEGER.ij> select cast(c as smallint) from tab1;1 ------ERROR 22018: Invalid character string format for type SMALLINT.ij> select cast(c as bigint) from tab1;1 --------------------ERROR 22018: Invalid character string format for type BIGINT.ij> select cast(c as char(10)) from tab1;1 ----------char NULL ij> select cast(c as char varying(10)) from tab1;1 ----------char NULL ij> select cast(c as double precision) from tab1;ERROR 42846: Cannot convert types 'CHAR' to 'DOUBLE'.ij> select cast(c as float) from tab1;ERROR 42846: Cannot convert types 'CHAR' to 'DOUBLE'.ij> select cast(c as date) from tab1;1 ----------ERROR 22007: The syntax of the string representation of a datetime value is incorrect.ij> select cast(c as time) from tab1;1 --------ERROR 22007: The syntax of the string representation of a datetime value is incorrect.ij> select cast(c as timestamp) from tab1;1 --------------------------ERROR 22007: The syntax of the string representation of a datetime value is incorrect.ij> select cast(c as dec) from tab1;1 ------ERROR 22018: Invalid character string format for type DECIMAL.ij> select cast(t as integer) from tab1;ERROR 42846: Cannot convert types 'TIME' to 'INTEGER'.ij> select cast(t as smallint) from tab1;ERROR 42846: Cannot convert types 'TIME' to 'SMALLINT'.ij> select cast(t as bigint) from tab1;ERROR 42846: Cannot convert types 'TIME' to 'BIGINT'.ij> select cast(t as char(10)) from tab1;1 ----------11:11:11 NULL ij> select cast(t as char varying(10)) from tab1;1 ----------11:11:11 NULL ij> select cast(t as double precision) from tab1;ERROR 42846: Cannot convert types 'TIME' to 'DOUBLE'.ij> select cast(t as float) from tab1;ERROR 42846: Cannot convert types 'TIME' to 'DOUBLE'.ij> select cast(t as date) from tab1;ERROR 42846: Cannot convert types 'TIME' to 'DATE'.ij> select cast(t as time) from tab1;1 --------11:11:11NULL ij> select cast(t as timestamp) from tab1;ERROR 42846: Cannot convert types 'TIME' to 'TIMESTAMP'.ij> select cast(t as dec) from tab1;ERROR 42846: Cannot convert types 'TIME' to 'DECIMAL'.ij> drop table tab1;0 rows inserted/updated/deletedij> ----------------------------------------------------------------- Other Tests---------------------------------------------------------------autocommit off;ij> -- create tablescreate table t1 (bt char(1) for bit data, btv varchar(1) for bit data, c char(30), d double precision, i int, r real, s smallint, dc decimal(18), num numeric(18), dt date, t time, ts timestamp, v varchar(30), lvc long varchar);0 rows inserted/updated/deletedij> create table strings(c30 char(30));0 rows inserted/updated/deletedij> -- we need a 1 row table with date/time columns because of problems-- with single quotes in using 'values DATE('')'create table temporal_values (dt date, t time, ts timestamp);0 rows inserted/updated/deletedij> insert into temporal_values values(DATE('9876-5-4'), TIME('1:02:34'), TIMESTAMP('9876-5-4 1:02:34'));1 row inserted/updated/deletedij> -- negative-- pass wrong type for parameterprepare a1 as 'values cast(? as smallint)';ij> execute a1 using 'values 1';1 ------1 ij> -- uninitialized parametervalues cast(? as int);ERROR 07000: At least one parameter to the current statement is uninitialized.ij> -- positive-- test casting null to all builtin typesinsert into t1 (bt) values cast(null as char(1) for bit data);1 row inserted/updated/deletedij> insert into t1 (btv) values cast(null as varchar(1) for bit data);1 row inserted/updated/deletedij> insert into t1 (c) values cast(null as char(30));1 row inserted/updated/deletedij> insert into t1 (d) values cast(null as double precision);1 row inserted/updated/deletedij> insert into t1 (i) values cast(null as int);1 row inserted/updated/deletedij> insert into t1 (r) values cast(null as real);1 row inserted/updated/deletedij> insert into t1 (s) values cast(null as smallint);1 row inserted/updated/deletedij> insert into t1 (dc) values cast(null as decimal);1 row inserted/updated/deletedij> insert into t1 (num) values cast(null as numeric);1 row inserted/updated/deletedij> insert into t1 (dt) values cast(null as date);1 row inserted/updated/deletedij> insert into t1 (t) values cast(null as time);1 row inserted/updated/deletedij> insert into t1 (ts) values cast(null as timestamp);1 row inserted/updated/deletedij> insert into t1 (v) values cast(null as varchar(30));1 row inserted/updated/deletedij> insert into t1 (lvc) values cast(null as long varchar);1 row inserted/updated/deletedij> -- expect 10 rows of nullsselect * from t1;BT |BTV |C |D |I |R |S |DC |NUM |DT |T |TS |V |LVC -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL ij> -- make sure casting works correctly on nullsselect cast (bt as char(1) for bit data) from t1;1 ----NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLij> select cast (btv as varchar(1) for bit data) from t1;1 ----NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLij> select cast (c as char(30)) from t1;1 ------------------------------NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> select cast (d as double precision) from t1;1 ----------------------NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> select cast (r as real) from t1;1 -------------NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> select cast (s as smallint) from t1;1 ------NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> select cast (num as numeric) from t1;1 ------NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> select cast (dc as decimal) from t1;1 ------NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> select cast (dt as date) from t1;1 ----------NULL NULL NULL ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -