including_dump_test.sql

--pg_dump testing. --
/*
 * 1.test CREATE INDEX
 */
 -- Regular index with included columns
CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box);
CREATE INDEX tbl_idx ON tbl using btree(c1, c2) INCLUDING (c3,c4);

-- Unique index and unique constraint
CREATE TABLE tbl2 (c1 int, c2 int, c3 int, c4 box);
CREATE UNIQUE INDEX tbl2_idx_unique ON tbl2 using btree(c1, c2) INCLUDING (c3,c4);
ALTER TABLE tbl2 add UNIQUE USING INDEX tbl2_idx_unique;
ALTER TABLE tbl2 add UNIQUE(c1, c2) INCLUDING (c3, c4);

-- PK constraint
CREATE TABLE tbl3 (c1 int, c2 int, c3 int, c4 box);
ALTER TABLE tbl3 add PRIMARY KEY(c1, c2) INCLUDING (c3, c4);

CREATE TABLE tbl4 (c1 int, c2 int, c3 int, c4 box);
CREATE UNIQUE INDEX tbl4_idx_unique ON tbl4 using btree(c1, c2) INCLUDING (c3,c4);
ALTER TABLE tbl4 add PRIMARY KEY USING INDEX tbl4_idx_unique;

/*
 * 2. Test CREATE TABLE with constraint
 */
CREATE TABLE tbl5 (c1 int,c2 int, c3 int, c4 box,
				CONSTRAINT covering_u UNIQUE(c1,c2) INCLUDING(c3,c4));

CREATE TABLE tbl6 (c1 int,c2 int, c3 int, c4 box,
				CONSTRAINT covering_pk PRIMARY KEY(c1,c2) INCLUDING(c3,c4));

CREATE TABLE tbl7 (c1 int,c2 int, c3 int, c4 box,
				UNIQUE(c1,c2) INCLUDING(c3,c4));

CREATE TABLE tbl8 (c1 int,c2 int, c3 int, c4 box,
				PRIMARY KEY(c1,c2) INCLUDING(c3,c4));

select * from pg_indexes where tablename LIKE 'tbl%';