diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/create_table_like.out | 103 | ||||
| -rw-r--r-- | src/test/regress/sql/create_table_like.sql | 42 |
2 files changed, 145 insertions, 0 deletions
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out index 2cebe382432..bf34289e984 100644 --- a/src/test/regress/expected/create_table_like.out +++ b/src/test/regress/expected/create_table_like.out @@ -566,3 +566,106 @@ DROP TYPE ctlty1; DROP VIEW ctlv1; DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12; NOTICE: table "ctlt10" does not exist, skipping +-- +-- CREATE FOREIGN TABLE LIKE +-- +CREATE FOREIGN DATA WRAPPER ctl_dummy; +CREATE SERVER ctl_s0 FOREIGN DATA WRAPPER ctl_dummy; +CREATE TABLE ctl_table(a int PRIMARY KEY, + b varchar COMPRESSION pglz, + c int GENERATED ALWAYS AS (a * 2) STORED, + d bigint GENERATED ALWAYS AS IDENTITY, + e int DEFAULT 1); +CREATE INDEX ctl_table_a_key ON ctl_table(a); +COMMENT ON COLUMN ctl_table.b IS 'Column b'; +CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table; +ALTER TABLE ctl_table ADD CONSTRAINT foo CHECK (b = 'text'); +ALTER TABLE ctl_table ALTER COLUMN b SET STORAGE MAIN; +\d+ ctl_table + Table "public.ctl_table" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+-------------------+-----------+----------+------------------------------------+---------+--------------+------------- + a | integer | | not null | | plain | | + b | character varying | | | | main | | Column b + c | integer | | | generated always as (a * 2) stored | plain | | + d | bigint | | not null | generated always as identity | plain | | + e | integer | | | 1 | plain | | +Indexes: + "ctl_table_pkey" PRIMARY KEY, btree (a) + "ctl_table_a_key" btree (a) +Check constraints: + "foo" CHECK (b::text = 'text'::text) +Statistics objects: + "public.ctl_table_stat" ON a, b FROM ctl_table +Not-null constraints: + "ctl_table_a_not_null" NOT NULL "a" + "ctl_table_d_not_null" NOT NULL "d" + +-- Test EXCLUDING ALL +CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table EXCLUDING ALL) SERVER ctl_s0; +\d+ ctl_foreign_table1 + Foreign table "public.ctl_foreign_table1" + Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description +--------+-------------------+-----------+----------+---------+-------------+----------+--------------+------------- + a | integer | | not null | | | plain | | + b | character varying | | | | | extended | | + c | integer | | | | | plain | | + d | bigint | | not null | | | plain | | + e | integer | | | | | plain | | +Not-null constraints: + "ctl_table_a_not_null" NOT NULL "a" + "ctl_table_d_not_null" NOT NULL "d" +Server: ctl_s0 + +-- \d+ does not report the value of attcompression for a foreign table, so +-- check separately. +SELECT attname, attcompression FROM pg_attribute + WHERE attrelid = 'ctl_foreign_table1'::regclass and attnum > 0 ORDER BY attnum; + attname | attcompression +---------+---------------- + a | + b | + c | + d | + e | +(5 rows) + +-- Test INCLUDING ALL +-- INDEXES, IDENTITY, COMPRESSION, STORAGE are not copied. +CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0; +\d+ ctl_foreign_table2 + Foreign table "public.ctl_foreign_table2" + Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description +--------+-------------------+-----------+----------+------------------------------------+-------------+----------+--------------+------------- + a | integer | | not null | | | plain | | + b | character varying | | | | | extended | | Column b + c | integer | | | generated always as (a * 2) stored | | plain | | + d | bigint | | not null | | | plain | | + e | integer | | | 1 | | plain | | +Check constraints: + "foo" CHECK (b::text = 'text'::text) +Statistics objects: + "public.ctl_foreign_table2_a_b_stat" ON a, b FROM ctl_foreign_table2 +Not-null constraints: + "ctl_table_a_not_null" NOT NULL "a" + "ctl_table_d_not_null" NOT NULL "d" +Server: ctl_s0 + +-- \d+ does not report the value of attcompression for a foreign table, so +-- check separately. +SELECT attname, attcompression FROM pg_attribute + WHERE attrelid = 'ctl_foreign_table2'::regclass and attnum > 0 ORDER BY attnum; + attname | attcompression +---------+---------------- + a | + b | + c | + d | + e | +(5 rows) + +DROP TABLE ctl_table; +DROP FOREIGN TABLE ctl_foreign_table1; +DROP FOREIGN TABLE ctl_foreign_table2; +DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE; +NOTICE: drop cascades to server ctl_s0 diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql index 63a60303659..6e21722aaeb 100644 --- a/src/test/regress/sql/create_table_like.sql +++ b/src/test/regress/sql/create_table_like.sql @@ -225,3 +225,45 @@ DROP SEQUENCE ctlseq1; DROP TYPE ctlty1; DROP VIEW ctlv1; DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12; + +-- +-- CREATE FOREIGN TABLE LIKE +-- +CREATE FOREIGN DATA WRAPPER ctl_dummy; +CREATE SERVER ctl_s0 FOREIGN DATA WRAPPER ctl_dummy; + +CREATE TABLE ctl_table(a int PRIMARY KEY, + b varchar COMPRESSION pglz, + c int GENERATED ALWAYS AS (a * 2) STORED, + d bigint GENERATED ALWAYS AS IDENTITY, + e int DEFAULT 1); + +CREATE INDEX ctl_table_a_key ON ctl_table(a); +COMMENT ON COLUMN ctl_table.b IS 'Column b'; +CREATE STATISTICS ctl_table_stat ON a,b FROM ctl_table; +ALTER TABLE ctl_table ADD CONSTRAINT foo CHECK (b = 'text'); +ALTER TABLE ctl_table ALTER COLUMN b SET STORAGE MAIN; + +\d+ ctl_table + +-- Test EXCLUDING ALL +CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table EXCLUDING ALL) SERVER ctl_s0; +\d+ ctl_foreign_table1 +-- \d+ does not report the value of attcompression for a foreign table, so +-- check separately. +SELECT attname, attcompression FROM pg_attribute + WHERE attrelid = 'ctl_foreign_table1'::regclass and attnum > 0 ORDER BY attnum; + +-- Test INCLUDING ALL +-- INDEXES, IDENTITY, COMPRESSION, STORAGE are not copied. +CREATE FOREIGN TABLE ctl_foreign_table2(LIKE ctl_table INCLUDING ALL) SERVER ctl_s0; +\d+ ctl_foreign_table2 +-- \d+ does not report the value of attcompression for a foreign table, so +-- check separately. +SELECT attname, attcompression FROM pg_attribute + WHERE attrelid = 'ctl_foreign_table2'::regclass and attnum > 0 ORDER BY attnum; + +DROP TABLE ctl_table; +DROP FOREIGN TABLE ctl_foreign_table1; +DROP FOREIGN TABLE ctl_foreign_table2; +DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE; |