📄 cast.out
字号:
cast (cast ('-32768' as DECIMAL(24,1)) as SMALLINT) );1 |2 -----------------------------------32768.0 |-32768ij> values ( cast ('-32767' as DECIMAL(24,1)), cast (cast ('-32767' as DECIMAL(24,1)) as SMALLINT) );1 |2 -----------------------------------32767.0 |-32767ij> -- only this should failvalues ( cast ('-32769' as DECIMAL(24,1)), cast (cast ('-32769' as DECIMAL(24,1)) as SMALLINT) );1 |2 ----------------------------------ERROR 22003: The resulting value is outside the range for the data type SMALLINT.ij> values ( cast ('-32768.9' as DECIMAL(24,1)), cast (cast ('-32768.9' as DECIMAL(24,1)) as SMALLINT) );1 |2 -----------------------------------32768.9 |-32768ij> values ( cast ('2147483647' as DECIMAL(24,1)), cast (cast ('2147483647' as DECIMAL(24,1)) as INTEGER) );1 |2 ---------------------------------------2147483647.0 |2147483647 ij> values ( cast ('2147483646' as DECIMAL(24,1)), cast (cast ('2147483646' as DECIMAL(24,1)) as INTEGER) );1 |2 ---------------------------------------2147483646.0 |2147483646 ij> -- only this should failvalues ( cast ('2147483648' as DECIMAL(24,1)), cast (cast ('2147483648' as DECIMAL(24,1)) as INTEGER) );1 |2 ---------------------------------------ERROR 22003: The resulting value is outside the range for the data type INTEGER.ij> values ( cast ('2147483647.9' as DECIMAL(24,1)), cast (cast ('2147483647.9' as DECIMAL(24,1)) as INTEGER) );1 |2 ---------------------------------------2147483647.9 |2147483647 ij> values ( cast ('-2147483647' as DECIMAL(24,1)), cast (cast ('-2147483647' as DECIMAL(24,1)) as INTEGER) );1 |2 ----------------------------------------2147483647.0 |-2147483647ij> values ( cast ('-2147483646' as DECIMAL(24,1)), cast (cast ('-2147483646' as DECIMAL(24,1)) as INTEGER) );1 |2 ----------------------------------------2147483646.0 |-2147483646ij> -- only this should failvalues ( cast ('-2147483649' as DECIMAL(24,1)), cast (cast ('-2147483649' as DECIMAL(24,1)) as INTEGER) );1 |2 ---------------------------------------ERROR 22003: The resulting value is outside the range for the data type INTEGER.ij> values ( cast ('-2147483648.9' as DECIMAL(24,1)), cast (cast ('-2147483648.9' as DECIMAL(24,1)) as INTEGER) );1 |2 ----------------------------------------2147483648.9 |-2147483648ij> --numbers to charvalues (cast (1.1 as char(10)));1 ----------1.1 ij> values (cast (1.1 as varchar(10)));ERROR 42846: Cannot convert types 'DECIMAL' to 'VARCHAR'.ij> values (cast (1e1 as varchar(10)));ERROR 42846: Cannot convert types 'DOUBLE' to 'VARCHAR'.ij> values (cast (1e1 as char(10)));ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'.ij> values (cast (1 as char(10)));1 ----------1 ij> values (cast (1 as varchar(10)));ERROR 42846: Cannot convert types 'INTEGER' to 'VARCHAR'.ij> values (cast (1e200 as char(10)));ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'.ij> values (cast (1e200 as varchar(10)));ERROR 42846: Cannot convert types 'DOUBLE' to 'VARCHAR'.ij> values (cast (1 as long varchar));ERROR 42846: Cannot convert types 'INTEGER' to 'LONG VARCHAR'.ij> values (cast (1.1 as long varchar));ERROR 42846: Cannot convert types 'DECIMAL' to 'LONG VARCHAR'.ij> values (cast (1e1 as long varchar));ERROR 42846: Cannot convert types 'DOUBLE' to 'LONG VARCHAR'.ij> --char to numbersvalues (cast ('123' as smallint));1 ------123 ij> values (cast ('123' as int));1 -----------123 ij> values (cast ('123' as bigint));1 --------------------123 ij> values (cast ('123' as double precision));ERROR 42846: Cannot convert types 'CHAR' to 'DOUBLE'.ij> values (cast ('123' as float));ERROR 42846: Cannot convert types 'CHAR' to 'DOUBLE'.ij> values (cast ('123' as real));ERROR 42846: Cannot convert types 'CHAR' to 'REAL'.ij> values (cast ('123' as numeric(3,0)));1 ----123 ij> values (cast ('123' as decimal(3,0)));1 ----123 ij> -- char (with decimal) to numbers (truncates where needed Track #3756)-- bug 5568values (cast ('123.45' as smallint));1 ------123 ij> values (cast ('123.45' as int));1 -----------123 ij> values (cast ('123.45' as bigint));1 --------------------123 ij> values (cast ('123.45' as double precision));ERROR 42846: Cannot convert types 'CHAR' to 'DOUBLE'.ij> values (cast ('123.45' as float));ERROR 42846: Cannot convert types 'CHAR' to 'DOUBLE'.ij> values (cast ('123.45' as real));ERROR 42846: Cannot convert types 'CHAR' to 'REAL'.ij> values (cast ('123.45' as numeric(5,1)));1 --------123.4 ij> values (cast ('123.45' as decimal(5,1)));1 --------123.4 ij> values (cast ('123.99' as smallint));1 ------123 ij> values (cast ('123.99' as int));1 -----------123 ij> values (cast ('123.99' as bigint));1 --------------------123 ij> values (cast ('123.99' as double precision));ERROR 42846: Cannot convert types 'CHAR' to 'DOUBLE'.ij> values (cast ('123.99' as float));ERROR 42846: Cannot convert types 'CHAR' to 'DOUBLE'.ij> values (cast ('123.99' as real));ERROR 42846: Cannot convert types 'CHAR' to 'REAL'.ij> values (cast ('123.99' as numeric(5,1)));1 --------123.9 ij> values (cast ('123.99' as decimal(5,1)));1 --------123.9 ij> --badvalues (cast (1 as char(2) for bit data));ERROR 42846: Cannot convert types 'INTEGER' to 'CHAR () FOR BIT DATA'.ij> values (cast (1 as date));ERROR 42846: Cannot convert types 'INTEGER' to 'DATE'.ij> values (cast (1 as time));ERROR 42846: Cannot convert types 'INTEGER' to 'TIME'.ij> values (cast (1 as timestamp));ERROR 42846: Cannot convert types 'INTEGER' to 'TIMESTAMP'.ij> ---------------------char -> date/time-------------------values (cast ('TIME''11:11:11''' as time));ERROR 22007: The syntax of the string representation of a datetime value is incorrect.ij> values (cast ('11:11:11' as time));1 --------11:11:11ij> values (cast ('DATE''1999-09-09''' as date));ERROR 22007: The syntax of the string representation of a datetime value is incorrect.ij> values (cast ('1999-09-09' as date));1 ----------1999-09-09ij> values (cast ('TIMESTAMP''xxxxxxFILTERED-TIMESTAMPxxxxx'' as timestamp));ERROR 22007: The syntax of the string representation of a datetime value is incorrect.ij> values (cast ('xxxxxxFILTERED-TIMESTAMPxxxxxas timestamp));1 --------------------------xxxxxxFILTERED-TIMESTAMPxxxxxij> --------------------date/time ->other------------------values (cast (TIME('11:11:11') as char(20)));1 --------------------11:11:11 ij> values (cast (DATE('1999-09-09') as char(20)));1 --------------------1999-09-09 ij> values (cast (TIMESTAMP('xxxxxxFILTERED-TIMESTAMPxxxxx)as char(40)));1 ----------------------------------------xxxxxxFILTERED-TIMESTAMPxxxxxij> values (cast (TIME('11:11:11') as varchar(20)));1 --------------------11:11:11 ij> values (cast (DATE('1999-09-09') as varchar(20)));1 --------------------1999-09-09 ij> values (cast (TIMESTAMP('xxxxxxFILTERED-TIMESTAMPxxxxx)as varchar(40)));1 ----------------------------------------xxxxxxFILTERED-TIMESTAMPxxxxxij> values (cast (TIME('11:11:11') as long varchar));ERROR 42846: Cannot convert types 'TIME' to 'LONG VARCHAR'.ij> values (cast (DATE('1999-09-09') as long varchar));ERROR 42846: Cannot convert types 'DATE' to 'LONG VARCHAR'.ij> values (cast (TIMESTAMP('xxxxxxFILTERED-TIMESTAMPxxxxx)as long varchar));ERROR 42846: Cannot convert types 'TIMESTAMP' to 'LONG VARCHAR'.ij> -- truncation errorsvalues (cast (TIME('11:11:11') as char(2)));ERROR 22001: A truncation error was encountered trying to shrink CHAR '11:11:11' to length 2.ij> values (cast (DATE('1999-09-09') as char(2)));ERROR 22001: A truncation error was encountered trying to shrink CHAR '1999-09-09' to length 2.ij> values (cast (TIMESTAMP('xxxxxxFILTERED-TIMESTAMPxxxxx)as char(2)));ERROR 22001: A truncation error was encountered trying to shrink CHAR 'xxxxxxFILTERED-TIMESTAMPxxxxx' to length 2.ij> -- to date/timevalues (cast (TIME('11:11:11') as time));1 --------11:11:11ij> values (cast (TIME('11:11:11') as date));ERROR 42846: Cannot convert types 'TIME' to 'DATE'.ij> -- this piece of convoluted logic is to ensure that we-- get the current date for a conversion of time to timestampvalues cast (cast (TIME('11:11:11') as timestamp) as char(50)).substring(0, 10).equals(cast (current_date as char(10)));ERROR 42846: Cannot convert types 'TIME' to 'TIMESTAMP'.ij> -- now make sure we got the time rightvalues cast (cast (TIME('11:11:11') as timestamp) as char(30)).substring(11,21);ERROR 42846: Cannot convert types 'TIME' to 'TIMESTAMP'.ij> values (cast (DATE('1999-09-09') as date));1 ----------1999-09-09ij> values (cast (DATE('1999-09-09') as time));ERROR 42846: Cannot convert types 'DATE' to 'TIME'.ij> values (cast (DATE('1999-09-09') as timestamp));ERROR 42846: Cannot convert types 'DATE' to 'TIMESTAMP'.ij> values (cast (TIMESTAMP('xxxxxxFILTERED-TIMESTAMPxxxxx)as date));1 ----------1999-09-09ij> values (cast (TIMESTAMP('xxxxxxFILTERED-TIMESTAMPxxxxx)as time));1 --------11:11:11ij> values (cast (TIMESTAMP('xxxxxxFILTERED-TIMESTAMPxxxxx)as timestamp));1 --------------------------xxxxxxFILTERED-TIMESTAMPxxxxxij> --badvalues (cast (TIMESTAMP('xxxxxxFILTERED-TIMESTAMPxxxxx)as int));ERROR 42846: Cannot convert types 'TIMESTAMP' to 'INTEGER'.ij> values (cast (DATE('1999-09-09') as int));ERROR 42846: Cannot convert types 'DATE' to 'INTEGER'.ij> values (cast (TIME('11:11:11') as int));ERROR 42846: Cannot convert types 'TIME' to 'INTEGER'.ij> values (cast (TIMESTAMP('xxxxxxFILTERED-TIMESTAMPxxxxx)as smallint));ERROR 42846: Cannot convert types 'TIMESTAMP' to 'SMALLINT'.ij> values (cast (DATE('1999-09-09') as smallint));ERROR 42846: Cannot convert types 'DATE' to 'SMALLINT'.ij> values (cast (TIME('11:11:11') as smallint));ERROR 42846: Cannot convert types 'TIME' to 'SMALLINT'.ij> values (cast (TIMESTAMP('xxxxxxFILTERED-TIMESTAMPxxxxx)as bigint));ERROR 42846: Cannot convert types 'TIMESTAMP' to 'BIGINT'.ij> values (cast (DATE('1999-09-09') as bigint));ERROR 42846: Cannot convert types 'DATE' to 'BIGINT'.ij> values (cast (TIME('11:11:11') as bigint));ERROR 42846: Cannot convert types 'TIME' to 'BIGINT'.ij> values (cast (TIMESTAMP('xxxxxxFILTERED-TIMESTAMPxxxxx)as numeric));ERROR 42846: Cannot convert types 'TIMESTAMP' to 'NUMERIC'.ij> values (cast (DATE('1999-09-09') as numeric));ERROR 42846: Cannot convert types 'DATE' to 'NUMERIC'.ij> values (cast (TIME('11:11:11') as numeric));ERROR 42846: Cannot convert types 'TIME' to 'NUMERIC'.ij> values (cast (TIMESTAMP('xxxxxxFILTERED-TIMESTAMPxxxxx)as decimal));ERROR 42846: Cannot convert types 'TIMESTAMP' to 'DECIMAL'.ij> values (cast (DATE('1999-09-09') as decimal));ERROR 42846: Cannot convert types 'DATE' to 'DECIMAL'.ij> values (cast (TIME('11:11:11') as decimal));ERROR 42846: Cannot convert types 'TIME' to 'DECIMAL'.ij> values (cast (TIMESTAMP('xxxxxxFILTERED-TIMESTAMPxxxxx) as char(13) for bit data));ERROR 42846: Cannot convert types 'TIMESTAMP' to 'CHAR () FOR BIT DATA'.ij> values (cast (DATE('1999-09-09') as char(13) for bit data));ERROR 42846: Cannot convert types 'DATE' to 'CHAR () FOR BIT DATA'.ij> values (cast (TIME('11:11:11') as char(13) for bit data));ERROR 42846: Cannot convert types 'TIME' to 'CHAR () FOR BIT DATA'.ij> --------------bit ->char------------values (cast (X'00680065006c006c006f' as char(10)));ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'CHAR'.ij> --small bitvalues (cast (X'11' as char(10)));ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'CHAR'.ij> values (cast (X'11' as varchar(10)));ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'VARCHAR'.ij> values (cast (X'11' as long varchar));ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'LONG VARCHAR'.ij> --values (cast (X'00' as char(10)));--odd length won't work anymorevalues (cast (X'123' as char(20)));ERROR 42606: An invalid hexadecimal constant starting with 'X'123'' has been detected.ij> --truncate, (should be warning in future)values (cast ('1234' as char(1) for bit data));ERROR 42846: Cannot convert types 'CHAR' to 'CHAR () FOR BIT DATA'.ij> --truncate, okvalues (cast ('1200' as char(1) for bit data));ERROR 42846: Cannot convert types 'CHAR' to 'CHAR () FOR BIT DATA'.ij> -------------------------------------------------- Casting-----------------------------------------------create table tab1 ( i integer, s integer, b integer, l bigint, c char(10), v varchar(10), d double precision, r real, dt date, t time, ts timestamp, dc decimal);0 rows inserted/updated/deletedij> insert into tab1 values(1, cast(1 as smallint), cast(1 as int), cast(1 as bigint), 'char', 'varchar', cast(1.1 as double precision), cast(1.1 as real), DATE('1990-10-10'), TIME('11:11:11'), TIMESTAMP('xxxxxxFILTERED-TIMESTAMPxxxxx), 1.1);1 row inserted/updated/deletedij> insert into tab1 values (null, null, null, null, null, null, null, null, null, null, null, null);⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -