int8.sql 3.11 KB
Newer Older
1
--
2
-- INT8
3 4 5 6
-- Test int8 64-bit integers.
--
CREATE TABLE INT8_TBL(q1 int8, q2 int8);

7 8
INSERT INTO INT8_TBL VALUES('  123   ','  456');
INSERT INTO INT8_TBL VALUES('123   ','4567890123456789');
9 10 11 12
INSERT INTO INT8_TBL VALUES('4567890123456789','123');
INSERT INTO INT8_TBL VALUES('4567890123456789','4567890123456789');
INSERT INTO INT8_TBL VALUES('4567890123456789','-4567890123456789');

13 14 15 16 17 18 19 20 21
-- bad inputs
INSERT INTO INT8_TBL(q1) VALUES ('      ');
INSERT INTO INT8_TBL(q1) VALUES ('xxx');
INSERT INTO INT8_TBL(q1) VALUES ('3908203590239580293850293850329485');
INSERT INTO INT8_TBL(q1) VALUES ('-1204982019841029840928340329840934');
INSERT INTO INT8_TBL(q1) VALUES ('- 123');
INSERT INTO INT8_TBL(q1) VALUES ('  345     5');
INSERT INTO INT8_TBL(q1) VALUES ('');

22 23 24 25 26 27
SELECT * FROM INT8_TBL;

SELECT '' AS five, q1 AS plus, -q1 AS minus FROM INT8_TBL;

SELECT '' AS five, q1, q2, q1 + q2 AS plus FROM INT8_TBL;
SELECT '' AS five, q1, q2, q1 - q2 AS minus FROM INT8_TBL;
28
SELECT '' AS three, q1, q2, q1 * q2 AS multiply FROM INT8_TBL;
29 30 31 32 33 34 35 36 37
SELECT '' AS three, q1, q2, q1 * q2 AS multiply FROM INT8_TBL
 WHERE q1 < 1000 or (q2 > 0 and q2 < 1000);
SELECT '' AS five, q1, q2, q1 / q2 AS divide FROM INT8_TBL;

SELECT '' AS five, q1, float8(q1) FROM INT8_TBL;
SELECT '' AS five, q2, float8(q2) FROM INT8_TBL;

SELECT '' AS five, 2 * q1 AS "twice int4" FROM INT8_TBL;
SELECT '' AS five, q1 * 2 AS "twice int4" FROM INT8_TBL;
Bruce Momjian's avatar
Hi,  
Bruce Momjian committed
38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63

-- TO_CHAR()
--
SELECT '' AS to_char_1, to_char(q1, '9G999G999G999G999G999'), to_char(q2, '9,999,999,999,999,999') 
	FROM INT8_TBL;

SELECT '' AS to_char_2, to_char(q1, '9G999G999G999G999G999D999G999'), to_char(q2, '9,999,999,999,999,999.999,999') 
	FROM INT8_TBL;	

SELECT '' AS to_char_3, to_char( (q1 * -1), '9999999999999999PR'), to_char( (q2 * -1), '9999999999999999.999PR') 
	FROM INT8_TBL;

SELECT '' AS to_char_4, to_char( (q1 * -1), '9999999999999999S'), to_char( (q2 * -1), 'S9999999999999999') 
	FROM INT8_TBL;

SELECT '' AS to_char_5,  to_char(q2, 'MI9999999999999999')     FROM INT8_TBL;	
SELECT '' AS to_char_6,  to_char(q2, 'FMS9999999999999999')    FROM INT8_TBL;
SELECT '' AS to_char_7,  to_char(q2, 'FM9999999999999999THPR') FROM INT8_TBL;
SELECT '' AS to_char_8,  to_char(q2, 'SG9999999999999999th')   FROM INT8_TBL;	
SELECT '' AS to_char_9,  to_char(q2, '0999999999999999')       FROM INT8_TBL;	
SELECT '' AS to_char_10, to_char(q2, 'S0999999999999999')      FROM INT8_TBL;	
SELECT '' AS to_char_11, to_char(q2, 'FM0999999999999999')     FROM INT8_TBL;	
SELECT '' AS to_char_12, to_char(q2, 'FM9999999999999999.000') FROM INT8_TBL;
SELECT '' AS to_char_13, to_char(q2, 'L9999999999999999.000')  FROM INT8_TBL;	
SELECT '' AS to_char_14, to_char(q2, 'FM9999999999999999.999') FROM INT8_TBL;
SELECT '' AS to_char_15, to_char(q2, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9') FROM INT8_TBL;
64
SELECT '' AS to_char_16, to_char(q2, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM INT8_TBL;
Bruce Momjian's avatar
Hi,  
Bruce Momjian committed
65
SELECT '' AS to_char_17, to_char(q2, '999999SG9999999999')     FROM INT8_TBL;
66 67 68 69 70 71

-- check min/max values
select '-9223372036854775808'::int8;
select '-9223372036854775809'::int8;
select '9223372036854775807'::int8;
select '9223372036854775808'::int8;