--
-- COMPRESS02
--
-- CASE 1.1
CREATE TABLE compress_20( a00 int PREFIX ) ;
CREATE TABLE compress_20( a00 int NUMSTR ) ;
CREATE TABLE compress_20( a00 int DELTA ) ;
ALTER TABLE compress_20 ADD COLUMN a01 int DICTIONARY NOT NULL;
ALTER TABLE compress_20 ADD COLUMN a02 int PREFIX NOT NULL;
ALTER TABLE compress_20 ADD COLUMN a02 int NUMSTR NOT NULL;
ALTER TABLE compress_20 ADD COLUMN a02 int DELTA NOT NULL;
-- CASE 1.2
CREATE TABLE compress_21( a00 text DELTA ) ;
CREATE TABLE compress_21( a00 text PREFIX ) ;
ALTER TABLE compress_21 ADD COLUMN a01 text DICTIONARY NOT NULL;
ALTER TABLE compress_21 ADD COLUMN a02 text DELTA NOT NULL;
ALTER TABLE compress_21 ADD COLUMN a02 text NUMSTR NOT NULL;
ALTER TABLE compress_21 ADD COLUMN a03 text PREFIX NOT NULL;
-- CASE 1.3
CREATE TABLE compress_22( a00 name DELTA ) ;
CREATE TABLE compress_22( a00 name PREFIX ) ;
CREATE TABLE compress_22( a00 name NUMSTR ) ;
CREATE TABLE compress_22( a00 name DICTIONARY ) ;
-- CASE 1.4
CREATE TABLE compress_23( a00 char DELTA ) ;
CREATE TABLE compress_23( a00 char PREFIX ) ;
ALTER TABLE compress_23 ADD COLUMN a01 char DELTA;
ALTER TABLE compress_23 ADD COLUMN a01 char NUMSTR;
ALTER TABLE compress_23 ADD COLUMN a02 char DICTIONARY;
-- CASE 1.5
CREATE TABLE compress_24( a00 "char" DELTA ) ;
CREATE TABLE compress_24( a00 "char" PREFIX ) ;
CREATE TABLE compress_24( a01 "char" NUMSTR ) ;
ALTER TABLE compress_24 ADD COLUMN a02 "char" DICTIONARY;
ALTER TABLE compress_24 ADD COLUMN a03 "char" DELTA;
ALTER TABLE compress_24 ADD COLUMN a04 "char" PREFIX;
ALTER TABLE compress_24 ADD COLUMN a04 "char" NUMSTR;
-- CASE 1.6
CREATE TABLE compress_25
(
c_smallint smallint NOT NULL,
c_integer integer delta NOT NULL,
c_bigint bigint delta NOT NULL,
c_decimal decimal NOT NULL,
c_numeric numeric NOT NULL,
c_real real delta NOT NULL, -- ???
c_double double precision delta NOT NULL, -- ???
c_character_1 character varying(100) NOT NULL,
c_varchar varchar(100) NOT NULL,
c_character_2 character(100) NOT NULL,
c_char_1 char(100) NOT NULL,
c_character_3 character NOT NULL,
c_char_2 char NOT NULL,
c_text text NOT NULL,
c_nvarchar2 nvarchar2 NOT NULL,
c_name name NOT NULL,
c_timestamp_1 timestamp without time zone NOT NULL,
c_timestamp_2 timestamp with time zone delta NOT NULL,
c_date date delta NOT NULL
) ;
-- skip lz_compress datum in tuple
CREATE TABLE compress_26 ( c_char_1 char(102400) ) ;
COPY compress_26 FROM STDIN;
DEF1
DEF2
DEF3
DEF4
DEF5
DEF6
DEF7
DEF8
DEF9
DEF10
\.
VACUUM FULL compress_26;
CREATE TABLE compress_27 ( c_char_1 char(102400) );
INSERT INTO compress_27 select * from compress_26;
(SELECT * FROM compress_26) MINUS ALL (SELECT * FROM compress_27);
(SELECT * FROM compress_27) MINUS ALL (SELECT * FROM compress_26);
DROP TABLE compress_26;
DROP TABLE compress_27;
-- test: the number of pages is at least equal to the number of uncompressed pages after compressor's handling
CREATE TABLE compress_30(
c_char_1 char NOT NULL,
c_char_2 char NOT NULL,
c_char_3 char NOT NULL,
c_char_4 char NOT NULL,
c_char_5 char NOT NULL,
c_char_6 char NOT NULL,
c_char_7 char NOT NULL,
c_char_8 char NOT NULL,
c_char_9 char NOT NULL,
c_char_10 char NOT NULL,
c_char_11 char NOT NULL,
c_char_12 char NOT NULL)
partition by range (c_char_1,c_char_2,c_char_3,c_char_4)
(
partition compress_30_1 values less than ('b','d','g','h'),
partition compress_30_2 values less than ('e','g','j','l'),
partition compress_30_3 values less than ('z','z','z','z')
);
CREATE TABLE compress_31(
c_char_1 char NOT NULL,
c_char_2 char NOT NULL,
c_char_3 char NOT NULL,
c_char_4 char NOT NULL,
c_char_5 char NOT NULL,
c_char_6 char NOT NULL,
c_char_7 char NOT NULL,
c_char_8 char NOT NULL,
c_char_9 char NOT NULL,
c_char_10 char NOT NULL,
c_char_11 char NOT NULL,
c_char_12 char NOT NULL)
partition by range (c_char_1,c_char_2,c_char_3,c_char_4)
(
partition compress_31_1 values less than ('b','d','g','h'),
partition compress_31_2 values less than ('e','g','j','l'),
partition compress_31_3 values less than ('z','z','z','z')
);
COPY compress_31 FROM '@abs_srcdir@/data/compress30.dat';
COPY compress_30 FROM '@abs_srcdir@/data/compress30.dat';
VACUUM compress_30;
VACUUM compress_31;
SELECT ((select pg_relation_size('compress_31')))/((select pg_relation_size('compress_30')))=1 AS COMPRESS_SCALE;