create schema uint_cast3;
set current_schema to 'uint_cast3';
select 1::uint1::int16;
int16
-------
1
(1 row)
select 1::int16::uint1;
uint1
-------
1
(1 row)
select 1::uint2::int16;
int16
-------
1
(1 row)
select 1::int16::uint2;
uint2
-------
1
(1 row)
select 1::uint4::int16;
int16
-------
1
(1 row)
select 1::int16::uint4;
uint4
-------
1
(1 row)
select 1::uint8::int16;
int16
-------
1
(1 row)
select 1::int16::uint8;
uint8
-------
1
(1 row)
select 1::uint1::clob;
clob
------
1
(1 row)
select 1::clob::uint1;
uint1
-------
1
(1 row)
select 1::uint2::clob;
clob
------
1
(1 row)
select 1::clob::uint2;
uint2
-------
1
(1 row)
select 1::uint4::clob;
clob
------
1
(1 row)
select 1::clob::uint4;
uint4
-------
1
(1 row)
select 1::uint8::clob;
clob
------
1
(1 row)
select 1::clob::uint8;
uint8
-------
1
(1 row)
select 1::uint4::abstime;
abstime
------------------------
1969-12-31 16:00:01-08
(1 row)
select 1::abstime::uint4;
uint4
-------
1
(1 row)
select 1::uint4::reltime;
reltime
---------
@ 1 sec
(1 row)
select 1::reltime::uint4;
uint4
-------
1
(1 row)
select 1::uint1::bpchar;
bpchar
--------
1
(1 row)
select 1::bpchar::uint1;
uint1
-------
1
(1 row)
select 1::uint2::bpchar;
bpchar
--------
1
(1 row)
select 1::bpchar::uint2;
uint2
-------
1
(1 row)
select 1::uint4::bpchar;
bpchar
--------
1
(1 row)
select 1::bpchar::uint4;
uint4
-------
1
(1 row)
select 1::uint8::bpchar;
bpchar
--------
1
(1 row)
select 1::bpchar::uint8;
uint8
-------
1
(1 row)
select 1::uint1::varchar;
varchar
---------
1
(1 row)
select 1::varchar::uint1;
uint1
-------
1
(1 row)
select 1::uint2::varchar;
varchar
---------
1
(1 row)
select 1::varchar::uint2;
uint2
-------
1
(1 row)
select 1::uint4::varchar;
varchar
---------
1
(1 row)
select 1::varchar::uint4;
uint4
-------
1
(1 row)
select 1::uint8::varchar;
varchar
---------
1
(1 row)
select 1::varchar::uint8;
uint8
-------
1
(1 row)
select 1::uint4::bit;
bit
-----
1
(1 row)
select 1::bit::uint4;
uint4
-------
1
(1 row)
select 1::uint8::bit;
bit
-----
1
(1 row)
select 1::bit::uint8;
uint8
-------
1
(1 row)
select 1::uint1::nvarchar2;
nvarchar2
-----------
1
(1 row)
select 1::nvarchar2::uint1;
uint1
-------
1
(1 row)
select 1::uint2::nvarchar2;
nvarchar2
-----------
1
(1 row)
select 1::nvarchar2::uint2;
uint2
-------
1
(1 row)
select 1::uint4::nvarchar2;
nvarchar2
-----------
1
(1 row)
select 1::nvarchar2::uint4;
uint4
-------
1
(1 row)
select 1::uint8::nvarchar2;
nvarchar2
-----------
1
(1 row)
select 1::nvarchar2::uint8;
uint8
-------
1
(1 row)
select 1::uint1::money;
money
-------
$1.00
(1 row)
select 1::uint2::money;;
money
-------
$1.00
(1 row)
select 1::uint4::money;
money
-------
$1.00
(1 row)
select 1::uint8::money;
money
-------
$1.00
(1 row)
select 1::uint1::interval;
interval
----------
@ 1 day
(1 row)
select 1::uint2::interval;;
interval
----------
@ 1 day
(1 row)
select 1::uint4::interval;
interval
----------
@ 1 day
(1 row)
select 151503::uint4::time;
time
----------
15:15:03
(1 row)
select 19990101::uint4::date;
date
------------
1999-01-01
(1 row)
select 19990101::uint4::timestamp;
timestamptz
------------------------
1999-01-01 00:00:00-08
(1 row)
select 19990101::uint4::timestamptz;
timestamptz
------------------------
1999-01-01 00:00:00-08
(1 row)
select 19990101::uint4::datetime;
timestamp
---------------------
1999-01-01 00:00:00
(1 row)
select 19990101222222::uint8::timestamp;
timestamptz
------------------------
1999-01-01 22:22:22-08
(1 row)
select 19990101222222::uint8::timestamptz;
timestamptz
------------------------
1999-01-01 22:22:22-08
(1 row)
select 19990101222222::uint8::datetime;
timestamp
---------------------
1999-01-01 22:22:22
(1 row)
select '-1'::uint1::date;
WARNING: Cast to tinyint unsigned converted negative integer to it's positive complement
CONTEXT: referenced column: date
WARNING: Out of range value for date
CONTEXT: referenced column: date
date
------
(1 row)
select '256'::uint1::date;
WARNING: tinyint unsigned out of range
CONTEXT: referenced column: date
WARNING: Out of range value for date
CONTEXT: referenced column: date
date
------
(1 row)
select 2004::uint2::date;
WARNING: Out of range value for date
CONTEXT: referenced column: date
date
------
(1 row)
select '65536'::uint2::date;
WARNING: smallint unsigned out of range
CONTEXT: referenced column: date
WARNING: Out of range value for date
CONTEXT: referenced column: date
date
------
(1 row)
select '65535'::uint2::date;
WARNING: Out of range value for date
CONTEXT: referenced column: date
date
------
(1 row)
select 2067::uint8::date;
WARNING: Out of range value for date
CONTEXT: referenced column: date
date
------
(1 row)
select '-1'::uint8::date;
WARNING: Cast to bigint unsigned converted negative integer to it's positive complement
CONTEXT: referenced column: date
WARNING: Out of range value for date
CONTEXT: referenced column: date
date
------
(1 row)
select '18446744073709551615'::uint8::date;
WARNING: Out of range value for date
CONTEXT: referenced column: date
date
------
(1 row)
select 1999::uint4::year;
year
------
1999
(1 row)
select 1999::year::uint4;
uint4
-------
1999
(1 row)
select (-1)::text::uint1;
WARNING: Cast to tinyint unsigned converted negative integer to it's positive complement
CONTEXT: referenced column: uint1
uint1
-------
255
(1 row)
select (-1)::text::uint2;
WARNING: Cast to smallint unsigned converted negative integer to it's positive complement
CONTEXT: referenced column: uint2
uint2
-------
65535
(1 row)
select (-1)::text::uint4;
WARNING: Cast to int unsigned converted negative integer to it's positive complement
CONTEXT: referenced column: uint4
uint4
------------
4294967295
(1 row)
select (-1)::text::uint8;
WARNING: Cast to bigint unsigned converted negative integer to it's positive complement
CONTEXT: referenced column: uint8
uint8
----------------------
18446744073709551615
(1 row)
select '255'::text::uint1;
uint1
-------
255
(1 row)
select '65535'::text::uint2;
uint2
-------
65535
(1 row)
select '4294967295'::text::uint4;
uint4
------------
4294967295
(1 row)
select '18446744073709551615'::text::uint8;
uint8
----------------------
18446744073709551615
(1 row)
select '256'::text::uint1;
WARNING: tinyint unsigned out of range
CONTEXT: referenced column: uint1
uint1
-------
255
(1 row)
select '65536'::text::uint2;
WARNING: smallint unsigned out of range
CONTEXT: referenced column: uint2
uint2
-------
65535
(1 row)
select '4294967296'::text::uint4;
WARNING: int unsigned out of range
CONTEXT: referenced column: uint4
uint4
------------
4294967295
(1 row)
select '18446744073709551616'::text::uint8;
WARNING: bigint unsigned out of range
CONTEXT: referenced column: uint8
uint8
----------------------
18446744073709551615
(1 row)
-- test: binary to unsigned
--- 配置参数
set dolphin.b_compatibility_mode=on;
set b_format_behavior_compat_options=enable_set_variables;
set bytea_output=escape;
--- 建表
drop table if exists t_binary0001 cascade;
NOTICE: table "t_binary0001" does not exist, skipping
create table t_binary0001(
c1 int not null,
c2 binary,
c3 binary(10),
c4 binary(255),
c5 varbinary(1),
c6 varbinary(10),
c7 varbinary(255)) charset utf8mb3;
WARNING: utf8mb3 is not a valid encoding name. default value set
--- 插入数据
set @v1='abcdefghijklmnopqrstuvwxyz';
set @v2='a熊猫竹竹爱吃竹子';
set @v3=hex(@v2);
set @v4=unhex(@v3);
set @v5=bin(121314);
set @v6=oct(999999);
insert into t_binary0001 values (
1, substr(@v1,1,1), substr(@v1,1,10), repeat(@v1, 9),
substr(@v1,1,1), substr(@v1,1,10), repeat(@v1, 9));
insert into t_binary0001 values (
2, substr(@v2,1,1), substr(@v2,1,4), repeat(@v2, 9),
substr(@v2,1,1), substr(@v2,1,4), repeat(@v2, 9));
insert into t_binary0001 values (
3, substr(@v3,1,1), substr(@v3,1,4), substr(repeat(@v3, 10),1,255),
substr(@v3,1,1), substr(@v3,1,4), substr(repeat(@v3, 10),1,255));
insert into t_binary0001 values (
4, substr(@v4,1,1), substr(@v4,1,4), substr(repeat(@v4, 10),1,255),
substr(@v4,1,1), substr(@v4,1,4), substr(repeat(@v4, 10),1,255));
insert into t_binary0001 values (
5, substr(@v5,1,1), substr(@v5,1,4), substr(repeat(@v5, 10),1,255),
substr(@v5,1,1), substr(@v5,1,4), substr(repeat(@v5, 10),1,255));
insert into t_binary0001 values (
6, substr(@v6,1,1), substr(@v6,1,4), substr(repeat(@v6, 10),1,255),
substr(@v6,1,1), substr(@v6,1,4), substr(repeat(@v6, 10),1,255));
--- 使用cast/convert函数转换
select c1, cast(c2 as unsigned), cast(c3 as unsigned), cast(c4 as unsigned),
cast(c5 as unsigned) from t_binary0001 order by 1,2,3,4,5;
WARNING: invalid input syntax for type double precision: "a"
CONTEXT: referenced column: c2
WARNING: invalid input syntax for type double precision: "abcdefghij"
CONTEXT: referenced column: c3
WARNING: invalid input syntax for type double precision: "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
CONTEXT: referenced column: c4
WARNING: invalid input syntax for type double precision: "a"
CONTEXT: referenced column: c5
WARNING: invalid input syntax for type double precision: "a"
CONTEXT: referenced column: c2
WARNING: invalid input syntax for type double precision: "a熊猫竹"
CONTEXT: referenced column: c3
WARNING: invalid input syntax for type double precision: "a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子"
CONTEXT: referenced column: c4
WARNING: invalid input syntax for type double precision: "a"
CONTEXT: referenced column: c5
WARNING: invalid input syntax for type double precision: "61E7"
CONTEXT: referenced column: c3
WARNING: invalid input syntax for type double precision: "61E7868AE78CABE7ABB9E7ABB9E788B1E59083E7ABB9E5AD9061E7868AE78CABE7ABB9E7ABB9E788B1E59083E7ABB9E5AD9061E7868AE78CABE7ABB9E7ABB9E788B1E59083E7ABB9E5AD9061E7868AE78CABE7ABB9E7ABB9E788B1E59083E7ABB9E5AD9061E7868AE78CABE7ABB9E7ABB9E788B1E59083E7ABB9E5AD9061E78"
CONTEXT: referenced column: c4
WARNING: invalid input syntax for type double precision: "a"
CONTEXT: referenced column: c2
WARNING: invalid input syntax for type double precision: "a熊"
CONTEXT: referenced column: c3
WARNING: invalid input syntax for type double precision: "a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子"
CONTEXT: referenced column: c4
WARNING: invalid input syntax for type double precision: "a"
CONTEXT: referenced column: c5
WARNING: Data truncated for input data: "1110"
CONTEXT: referenced column: c3
WARNING: invalid input syntax for type double precision: "11101100111100010111011001111000101110110011110001011101100111100010111011001111000101110110011110001011101100111100010111011001111000101110110011110001011101100111100010"
CONTEXT: referenced column: c4
WARNING: Data truncated for input data: "3641"
CONTEXT: referenced column: c3
WARNING: invalid input syntax for type double precision: "3641077364107736410773641077364107736410773641077364107736410773641077"
CONTEXT: referenced column: c4
c1 | c2 | c3 | c4 | c5
----+----+------+----------------------+----
1 | 0 | 0 | 0 | 0
2 | 0 | 0 | 0 | 0
3 | 6 | 61 | 61 | 6
4 | 0 | 0 | 0 | 0
5 | 1 | 1110 | 18446744073709551615 | 1
6 | 3 | 3641 | 18446744073709551615 | 3
(6 rows)
select c1, convert(c2, unsigned), convert(c3, unsigned), convert(c4, unsigned),
convert(c5, unsigned), convert(c6, unsigned), convert(c7, unsigned) from
t_binary0001 order by 1,2,3,4,5,6,7;
WARNING: invalid input syntax for type double precision: "a"
CONTEXT: referenced column: c2
WARNING: invalid input syntax for type double precision: "abcdefghij"
CONTEXT: referenced column: c3
WARNING: invalid input syntax for type double precision: "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
CONTEXT: referenced column: c4
WARNING: invalid input syntax for type double precision: "a"
CONTEXT: referenced column: c5
WARNING: invalid input syntax for type double precision: "abcdefghij"
CONTEXT: referenced column: c6
WARNING: invalid input syntax for type double precision: "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"
CONTEXT: referenced column: c7
WARNING: invalid input syntax for type double precision: "a"
CONTEXT: referenced column: c2
WARNING: invalid input syntax for type double precision: "a熊猫竹"
CONTEXT: referenced column: c3
WARNING: invalid input syntax for type double precision: "a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子"
CONTEXT: referenced column: c4
WARNING: invalid input syntax for type double precision: "a"
CONTEXT: referenced column: c5
WARNING: invalid input syntax for type double precision: "a熊猫竹"
CONTEXT: referenced column: c6
WARNING: invalid input syntax for type double precision: "a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子"
CONTEXT: referenced column: c7
WARNING: invalid input syntax for type double precision: "61E7"
CONTEXT: referenced column: c3
WARNING: invalid input syntax for type double precision: "61E7868AE78CABE7ABB9E7ABB9E788B1E59083E7ABB9E5AD9061E7868AE78CABE7ABB9E7ABB9E788B1E59083E7ABB9E5AD9061E7868AE78CABE7ABB9E7ABB9E788B1E59083E7ABB9E5AD9061E7868AE78CABE7ABB9E7ABB9E788B1E59083E7ABB9E5AD9061E7868AE78CABE7ABB9E7ABB9E788B1E59083E7ABB9E5AD9061E78"
CONTEXT: referenced column: c4
WARNING: invalid input syntax for type double precision: "61E7"
CONTEXT: referenced column: c6
WARNING: invalid input syntax for type double precision: "61E7868AE78CABE7ABB9E7ABB9E788B1E59083E7ABB9E5AD9061E7868AE78CABE7ABB9E7ABB9E788B1E59083E7ABB9E5AD9061E7868AE78CABE7ABB9E7ABB9E788B1E59083E7ABB9E5AD9061E7868AE78CABE7ABB9E7ABB9E788B1E59083E7ABB9E5AD9061E7868AE78CABE7ABB9E7ABB9E788B1E59083E7ABB9E5AD9061E78"
CONTEXT: referenced column: c7
WARNING: invalid input syntax for type double precision: "a"
CONTEXT: referenced column: c2
WARNING: invalid input syntax for type double precision: "a熊"
CONTEXT: referenced column: c3
WARNING: invalid input syntax for type double precision: "a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子"
CONTEXT: referenced column: c4
WARNING: invalid input syntax for type double precision: "a"
CONTEXT: referenced column: c5
WARNING: invalid input syntax for type double precision: "a熊"
CONTEXT: referenced column: c6
WARNING: invalid input syntax for type double precision: "a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子a熊猫竹竹爱吃竹子"
CONTEXT: referenced column: c7
WARNING: Data truncated for input data: "1110"
CONTEXT: referenced column: c3
WARNING: invalid input syntax for type double precision: "11101100111100010111011001111000101110110011110001011101100111100010111011001111000101110110011110001011101100111100010111011001111000101110110011110001011101100111100010"
CONTEXT: referenced column: c4
WARNING: invalid input syntax for type double precision: "11101100111100010111011001111000101110110011110001011101100111100010111011001111000101110110011110001011101100111100010111011001111000101110110011110001011101100111100010"
CONTEXT: referenced column: c7
WARNING: Data truncated for input data: "3641"
CONTEXT: referenced column: c3
WARNING: invalid input syntax for type double precision: "3641077364107736410773641077364107736410773641077364107736410773641077"
CONTEXT: referenced column: c4
WARNING: invalid input syntax for type double precision: "3641077364107736410773641077364107736410773641077364107736410773641077"
CONTEXT: referenced column: c7
c1 | c2 | c3 | c4 | c5 | c6 | c7
----+----+------+----------------------+----+------+----------------------
1 | 0 | 0 | 0 | 0 | 0 | 0
2 | 0 | 0 | 0 | 0 | 0 | 0
3 | 6 | 61 | 61 | 6 | 61 | 61
4 | 0 | 0 | 0 | 0 | 0 | 0
5 | 1 | 1110 | 18446744073709551615 | 1 | 1110 | 18446744073709551615
6 | 3 | 3641 | 18446744073709551615 | 3 | 3641 | 18446744073709551615
(6 rows)
--- 建表并插入数据
create table t_binary(a binary(255));
create table t_integer(a1 int8 unsigned, a2 int4 unsigned, a3 int2 unsigned, a4 int1 unsigned,
a5 int8, a6 int4, a7 int2, a8 int1);
insert into t_binary values('2e1');
insert into t_binary values('1ab');
insert into t_binary values('0x1ab');
insert into t_binary values('123');
--- 测试转换结果
select cast(a as int8 unsigned), cast(a as int4 unsigned), cast(a as int2 unsigned), cast(a as int1 unsigned),
cast(a as int8), cast(a as int4), cast(a as int2), cast(a as int1) from t_binary;
WARNING: invalid input syntax for type double precision: "2e1"
CONTEXT: referenced column: a
WARNING: invalid input syntax for type double precision: "2e1"
CONTEXT: referenced column: a
WARNING: invalid input syntax for type double precision: "2e1"
CONTEXT: referenced column: a
WARNING: invalid input syntax for type double precision: "2e1"
CONTEXT: referenced column: a
WARNING: invalid input syntax for type int16: "2e1"
DETAIL: text contain invalid character
CONTEXT: referenced column: a
WARNING: invalid input syntax for type double precision: "2e1"
CONTEXT: referenced column: a
WARNING: invalid input syntax for type double precision: "2e1"
CONTEXT: referenced column: a
WARNING: invalid input syntax for type double precision: "2e1"
CONTEXT: referenced column: a
WARNING: invalid input syntax for type double precision: "1ab"
CONTEXT: referenced column: a
WARNING: invalid input syntax for type double precision: "1ab"
CONTEXT: referenced column: a
WARNING: invalid input syntax for type double precision: "1ab"
CONTEXT: referenced column: a
WARNING: invalid input syntax for type double precision: "1ab"
CONTEXT: referenced column: a
WARNING: invalid input syntax for type int16: "1ab"
DETAIL: text contain invalid character
CONTEXT: referenced column: a
WARNING: invalid input syntax for type double precision: "1ab"
CONTEXT: referenced column: a
WARNING: invalid input syntax for type double precision: "1ab"
CONTEXT: referenced column: a
WARNING: invalid input syntax for type double precision: "1ab"
CONTEXT: referenced column: a
WARNING: invalid input syntax for type double precision: "0x1ab"
CONTEXT: referenced column: a
WARNING: invalid input syntax for type double precision: "0x1ab"
CONTEXT: referenced column: a
WARNING: invalid input syntax for type double precision: "0x1ab"
CONTEXT: referenced column: a
WARNING: invalid input syntax for type double precision: "0x1ab"
CONTEXT: referenced column: a
WARNING: invalid input syntax for type int16: "0x1ab"
DETAIL: text contain invalid character
CONTEXT: referenced column: a
WARNING: invalid input syntax for type double precision: "0x1ab"
CONTEXT: referenced column: a
WARNING: invalid input syntax for type double precision: "0x1ab"
CONTEXT: referenced column: a
WARNING: invalid input syntax for type double precision: "0x1ab"
CONTEXT: referenced column: a
WARNING: Data truncated for input data: "123"
CONTEXT: referenced column: a
WARNING: Data truncated for input data: "123"
CONTEXT: referenced column: a
WARNING: Data truncated for input data: "123"
CONTEXT: referenced column: a
WARNING: Data truncated for input data: "123"
CONTEXT: referenced column: a
WARNING: Data truncated for input data: "123"
CONTEXT: referenced column: a
WARNING: Data truncated for input data: "123"
CONTEXT: referenced column: a
WARNING: Data truncated for input data: "123"
CONTEXT: referenced column: a
WARNING: Data truncated for input data: "123"
CONTEXT: referenced column: a
a | a | a | a | a | a | a | a
-----+-----+-----+-----+-----+-----+-----+-----
2 | 2 | 2 | 2 | 2 | 2 | 2 | 2
1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
123 | 123 | 123 | 123 | 123 | 123 | 123 | 123
(4 rows)
insert ignore into t_integer select a, a, a, a, a, a, a, a from t_binary;
WARNING: Data truncated for input data: "2e1"
CONTEXT: referenced column: a1
WARNING: Data truncated for input data: "2e1"
CONTEXT: referenced column: a2
WARNING: Data truncated for input data: "2e1"
CONTEXT: referenced column: a3
WARNING: Data truncated for input data: "2e1"
CONTEXT: referenced column: a4
WARNING: Data truncated for input data: "2e1"
CONTEXT: referenced column: a5
WARNING: Data truncated for input data: "2e1"
CONTEXT: referenced column: a6
WARNING: Data truncated for input data: "2e1"
CONTEXT: referenced column: a7
WARNING: Data truncated for input data: "2e1"
CONTEXT: referenced column: a8
WARNING: invalid input syntax for type double precision: "1ab"
CONTEXT: referenced column: a1
WARNING: invalid input syntax for type double precision: "1ab"
CONTEXT: referenced column: a2
WARNING: invalid input syntax for type double precision: "1ab"
CONTEXT: referenced column: a3
WARNING: invalid input syntax for type double precision: "1ab"
CONTEXT: referenced column: a4
WARNING: invalid input syntax for type double precision: "1ab"
CONTEXT: referenced column: a5
WARNING: invalid input syntax for type double precision: "1ab"
CONTEXT: referenced column: a6
WARNING: invalid input syntax for type double precision: "1ab"
CONTEXT: referenced column: a7
WARNING: invalid input syntax for type double precision: "1ab"
CONTEXT: referenced column: a8
WARNING: invalid input syntax for type double precision: "0x1ab"
CONTEXT: referenced column: a1
WARNING: invalid input syntax for type double precision: "0x1ab"
CONTEXT: referenced column: a2
WARNING: invalid input syntax for type double precision: "0x1ab"
CONTEXT: referenced column: a3
WARNING: invalid input syntax for type double precision: "0x1ab"
CONTEXT: referenced column: a4
WARNING: invalid input syntax for type double precision: "0x1ab"
CONTEXT: referenced column: a5
WARNING: invalid input syntax for type double precision: "0x1ab"
CONTEXT: referenced column: a6
WARNING: invalid input syntax for type double precision: "0x1ab"
CONTEXT: referenced column: a7
WARNING: invalid input syntax for type double precision: "0x1ab"
CONTEXT: referenced column: a8
WARNING: Data truncated for input data: "123"
CONTEXT: referenced column: a1
WARNING: Data truncated for input data: "123"
CONTEXT: referenced column: a2
WARNING: Data truncated for input data: "123"
CONTEXT: referenced column: a3
WARNING: Data truncated for input data: "123"
CONTEXT: referenced column: a4
WARNING: Data truncated for input data: "123"
CONTEXT: referenced column: a5
WARNING: Data truncated for input data: "123"
CONTEXT: referenced column: a6
WARNING: Data truncated for input data: "123"
CONTEXT: referenced column: a7
WARNING: Data truncated for input data: "123"
CONTEXT: referenced column: a8
select * from t_integer;
a1 | a2 | a3 | a4 | a5 | a6 | a7 | a8
-----+-----+-----+-----+-----+-----+-----+-----
20 | 20 | 20 | 20 | 20 | 20 | 20 | 20
1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
123 | 123 | 123 | 123 | 123 | 123 | 123 | 123
(4 rows)
--- 清理
drop table t_integer cascade;
drop table t_binary cascade;
drop table t_binary0001 cascade;
--- 建表并插入数据
drop table if exists t_bigint0007;
NOTICE: table "t_bigint0007" does not exist, skipping
create table t_bigint0007(
c1 int,
c2 bigint unsigned,
c3 bigint(1) unsigned,
c4 bigint(255) unsigned,
c5 bigint unsigned default '10',
c6 bigint unsigned auto_increment primary key,
c7 bigint(1) unsigned default 0,
c8 bigint(10) unsigned not null default 99,
c9 bigint unsigned unique);
NOTICE: CREATE TABLE will create implicit sequence "t_bigint0007_c6_seq" for serial column "t_bigint0007.c6"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_bigint0007_pkey" for table "t_bigint0007"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "t_bigint0007_c9_key" for table "t_bigint0007"
set @val = 18446744073709551615;
insert t_bigint0007 values(1, @val, @val, @val, @val, @val, @val, @val, @val);
--- 测试转换结果
select * from t_bigint0007;
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9
----+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------
1 | 18446744073709551615 | 18446744073709551615 | 18446744073709551615 | 18446744073709551615 | 18446744073709551615 | 18446744073709551615 | 18446744073709551615 | 18446744073709551615
(1 row)
--- 清理
drop table t_bigint0007 cascade;
SET dolphin.sql_mode = '';
select cast('-0' as unsigned);
uint8
-------
0
(1 row)
create table t_uint(a uint1, b uint2, c uint4, d uint8);
insert into t_uint values('-0', '-0', '-0', '-0');
select * from t_uint;
a | b | c | d
---+---+---+---
0 | 0 | 0 | 0
(1 row)
drop table t_uint;
select cast(1 as signed int);
int8
------
1
(1 row)
select cast(1 as unsigned int);
uint8
-------
1
(1 row)
select convert(1 , signed int);
int8
------
1
(1 row)
select convert(1 , unsigned int);
uint8
-------
1
(1 row)
select cast(1 as signed integer);
int8
------
1
(1 row)
select cast(1 as unsigned integer);
uint8
-------
1
(1 row)
select convert(1 , signed integer);
int8
------
1
(1 row)
select convert(1 , unsigned integer);
uint8
-------
1
(1 row)
select cast(1 as integer signed);
int8
------
1
(1 row)
select cast(1 as integer unsigned);
uint8
-------
1
(1 row)
select convert(1 , integer signed);
int8
------
1
(1 row)
select convert(1 , integer unsigned);
uint8
-------
1
(1 row)
select cast(1 as signed);
int8
------
1
(1 row)
select cast(1 as unsigned);
uint8
-------
1
(1 row)
select convert(1 , signed);
int8
------
1
(1 row)
select convert(1 , unsigned);
uint8
-------
1
(1 row)
select (-1)::nvarchar2::uint1;
uint1
-------
255
(1 row)
select (-1)::nvarchar2::uint2;
uint2
-------
65535
(1 row)
select (-1)::nvarchar2::uint4;
uint4
------------
4294967295
(1 row)
select (-1)::nvarchar2::uint8;
uint8
----------------------
18446744073709551615
(1 row)
select '255'::nvarchar2::uint1;
uint1
-------
255
(1 row)
select '65535'::nvarchar2::uint2;
uint2
-------
65535
(1 row)
select '4294967295'::nvarchar2::uint4;
uint4
------------
4294967295
(1 row)
select '18446744073709551615'::nvarchar2::uint8;
WARNING: bigint unsigned out of range
CONTEXT: referenced column: uint8
SQL function "nvarchar2_cast_ui8" statement 1
referenced column: uint8
uint8
----------------------
18446744073709551615
(1 row)
select '256'::nvarchar2::uint1;
WARNING: tinyint unsigned out of range
CONTEXT: referenced column: uint1
SQL function "nvarchar2_cast_ui1" statement 1
referenced column: uint1
uint1
-------
255
(1 row)
select '65536'::nvarchar2::uint2;
WARNING: smallint unsigned out of range
CONTEXT: referenced column: uint2
SQL function "nvarchar2_cast_ui2" statement 1
referenced column: uint2
uint2
-------
65535
(1 row)
select '4294967296'::nvarchar2::uint4;
WARNING: int unsigned out of range
CONTEXT: referenced column: uint4
SQL function "nvarchar2_cast_ui4" statement 1
referenced column: uint4
uint4
------------
4294967295
(1 row)
select '18446744073709551616'::nvarchar2::uint8;
WARNING: bigint unsigned out of range
CONTEXT: referenced column: uint8
SQL function "nvarchar2_cast_ui8" statement 1
referenced column: uint8
uint8
----------------------
18446744073709551615
(1 row)
--- 测试cast uint to varchar
drop table if exists t_cast_uint_varchar;
NOTICE: table "t_cast_uint_varchar" does not exist, skipping
drop view if exists v_cast_uint_varchar;
NOTICE: view "v_cast_uint_varchar" does not exist, skipping
create table t_cast_uint_varchar (
flag int,
vc varchar(10),
vi int
);
insert into t_cast_uint_varchar values(1, 'a', 1), (2, 'b', 2);
select case when flag=1 then vc else cast(vi as unsigned) end as v from t_cast_uint_varchar;
v
---
a
2
(2 rows)
create view v_cast_uint_varchar as select case when flag=1 then vc else cast(vi as unsigned) end as v from t_cast_uint_varchar;
select * from v_cast_uint_varchar;
v
---
a
2
(2 rows)
drop view v_cast_uint_varchar;
drop table t_cast_uint_varchar;
drop schema uint_cast3 cascade;
reset current_schema;