create schema test_view_table_depend;
set current_schema to 'test_view_table_depend';
create table t1 (c1 int);
create view v1 as select * from t1;
drop table t1;
\d+ v1;
WARNING: View v1 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
c1 | integer | | plain |
View definition:
SELECT *
FROM t1;
create table t1 (c1 int);
\d+ v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
c1 | integer | | plain |
View definition:
SELECT t1.c1 FROM t1;
select valid = true from pg_class join pg_object on oid = object_oid where relname = 'v1';
?column?
----------
t
(1 row)
drop table t1 cascade;
NOTICE: drop cascades to view v1
create table t1 (c1 int);
create view v1 as select * from t1;
drop table t1;
\d v1;
WARNING: View v1 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.v1"
Column | Type | Modifiers
--------+---------+-----------
c1 | integer |
\d+ v1;
WARNING: View v1 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
c1 | integer | | plain |
View definition:
SELECT *
FROM t1;
select * from v1;
ERROR: The view v1 is invalid, please make it valid before operation.
HINT: Please re-add missing table fields.
create table t2 (c1 int);
select * from v1;
ERROR: The view v1 is invalid, please make it valid before operation.
HINT: Please re-add missing table fields.
drop table t2;
create table t1 (c2 int);
select * from v1;
ERROR: The view v1 is invalid, please make it valid before operation.
HINT: Please re-add missing table fields.
drop table t1;
create table t1 (c1 int);
insert into t1 values(1);
select * from v1;
c1
----
1
(1 row)
\d v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers
--------+---------+-----------
c1 | integer |
\d+ v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
c1 | integer | | plain |
View definition:
SELECT t1.c1
FROM t1;
drop table t1;
create table t1 (c1 varchar);
insert into t1 values('a');
select * from v1;
c1
----
a
(1 row)
\d v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers
--------+-------------------+-----------
c1 | character varying |
\d+ v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
--------+-------------------+-----------+----------+-------------
c1 | character varying | | extended |
View definition:
SELECT t1.c1
FROM t1;
drop table t1 cascade;
NOTICE: drop cascades to view v1
create table t1 (c1 int);
create view v1 as select * from t1;
insert into t1 values(1);
select * from v1;
c1
----
1
(1 row)
\d v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers
--------+---------+-----------
c1 | integer |
\d+ v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
c1 | integer | | plain |
View definition:
SELECT *
FROM t1;
drop table t1;
create table t1 (c2 int, c3 int, c1 int);
insert into t1 values(2, 3, 1);
select * from v1;
c1
----
1
(1 row)
\d v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers
--------+---------+-----------
c1 | integer |
\d+ v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
c1 | integer | | plain |
View definition:
SELECT t1.c1
FROM t1;
drop table t1 cascade;
NOTICE: drop cascades to view v1
create table t1 (c1 int, c2 int, c3 int);
create view v1 as select * from t1;
insert into t1 values(1, 2, 3);
select * from v1;
c1 | c2 | c3
----+----+----
1 | 2 | 3
(1 row)
\d v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers
--------+---------+-----------
c1 | integer |
c2 | integer |
c3 | integer |
\d+ v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
c1 | integer | | plain |
c2 | integer | | plain |
c3 | integer | | plain |
View definition:
SELECT *
FROM t1;
drop table t1;
create table t1 (c2 int);
insert into t1 values(2);
select * from v1;
ERROR: The view v1 is invalid, please make it valid before operation.
HINT: Please re-add missing table fields.
\d v1;
WARNING: View v1 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.v1"
Column | Type | Modifiers
--------+---------+-----------
c1 | integer |
c2 | integer |
c3 | integer |
\d+ v1;
WARNING: View v1 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
c1 | integer | | plain |
c2 | integer | | plain |
c3 | integer | | plain |
View definition:
SELECT *
FROM t1;
drop view v1;
drop table t1 cascade;
create table t1 (c1 int);
create view v1 as select * from t1;
create view v2 as select * from v1;
insert into t1 values(1);
select * from v1;
c1
----
1
(1 row)
select * from v2;
c1
----
1
(1 row)
\d v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers
--------+---------+-----------
c1 | integer |
\d v2;
View "test_view_table_depend.v2"
Column | Type | Modifiers
--------+---------+-----------
c1 | integer |
\d+ v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
c1 | integer | | plain |
View definition:
SELECT *
FROM t1;
\d+ v2;
View "test_view_table_depend.v2"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
c1 | integer | | plain |
View definition:
SELECT *
FROM v1;
drop table t1;
\d v1;
WARNING: View v1 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.v1"
Column | Type | Modifiers
--------+---------+-----------
c1 | integer |
\d v2;
WARNING: View v2 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.v2"
Column | Type | Modifiers
--------+---------+-----------
c1 | integer |
\d+ v1;
WARNING: View v1 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
c1 | integer | | plain |
View definition:
SELECT *
FROM t1;
\d+ v2;
WARNING: View v2 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.v2"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
c1 | integer | | plain |
View definition:
SELECT *
FROM v1;
select * from v1;
ERROR: The view v1 is invalid, please make it valid before operation.
HINT: Please re-add missing table fields.
select * from v2;
ERROR: The view v2 is invalid, please make it valid before operation.
HINT: Please re-add missing table fields.
create table t1 (c1 varchar);
insert into t1 values('a');
select * from v1;
c1
----
a
(1 row)
select * from v2;
c1
----
a
(1 row)
\d v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers
--------+-------------------+-----------
c1 | character varying |
\d v2;
View "test_view_table_depend.v2"
Column | Type | Modifiers
--------+-------------------+-----------
c1 | character varying |
\d+ v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
--------+-------------------+-----------+----------+-------------
c1 | character varying | | extended |
View definition:
SELECT t1.c1
FROM t1;
\d+ v2;
View "test_view_table_depend.v2"
Column | Type | Modifiers | Storage | Description
--------+-------------------+-----------+----------+-------------
c1 | character varying | | extended |
View definition:
SELECT v1.c1
FROM v1;
drop table t1 cascade;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to view v1
drop cascades to view v2
create table t1 (c1 int);
create view v1 as select c1 from t1;
create view v2 as select c1 + 1 from v1;
insert into t1 values(1);
select * from v1;
c1
----
1
(1 row)
select * from v2;
?column?
----------
2
(1 row)
\d v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers
--------+---------+-----------
c1 | integer |
\d v2;
View "test_view_table_depend.v2"
Column | Type | Modifiers
----------+--------+-----------
?column? | bigint |
\d+ v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
c1 | integer | | plain |
View definition:
SELECT t1.c1
FROM t1;
\d+ v2;
View "test_view_table_depend.v2"
Column | Type | Modifiers | Storage | Description
----------+--------+-----------+---------+-------------
?column? | bigint | | plain |
View definition:
SELECT v1.c1 OPERATOR(dolphin_catalog.+) 1::bigint AS "?column?"
FROM v1;
drop table t1;
\d v1;
WARNING: View v1 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.v1"
Column | Type | Modifiers
--------+---------+-----------
c1 | integer |
\d v2;
WARNING: View v2 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.v2"
Column | Type | Modifiers
----------+--------+-----------
?column? | bigint |
\d+ v1;
WARNING: View v1 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
c1 | integer | | plain |
View definition:
SELECT t1.c1
FROM t1;
\d+ v2;
WARNING: View v2 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.v2"
Column | Type | Modifiers | Storage | Description
----------+--------+-----------+---------+-------------
?column? | bigint | | plain |
View definition:
SELECT v1.c1 OPERATOR(dolphin_catalog.+) 1::bigint AS "?column?"
FROM v1;
select * from v1;
ERROR: The view v1 is invalid, please make it valid before operation.
HINT: Please re-add missing table fields.
select * from v2;
ERROR: The view v2 is invalid, please make it valid before operation.
HINT: Please re-add missing table fields.
create table t1 (c1 int);
insert into t1 values(1);
select * from v1;
c1
----
1
(1 row)
select * from v2;
?column?
----------
2
(1 row)
\d v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers
--------+---------+-----------
c1 | integer |
\d v2;
View "test_view_table_depend.v2"
Column | Type | Modifiers
----------+--------+-----------
?column? | bigint |
\d+ v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
c1 | integer | | plain |
View definition:
SELECT t1.c1
FROM t1;
\d+ v2;
View "test_view_table_depend.v2"
Column | Type | Modifiers | Storage | Description
----------+--------+-----------+---------+-------------
?column? | bigint | | plain |
View definition:
SELECT v1.c1::bigint OPERATOR(dolphin_catalog.+) 1::bigint AS "?column?"
FROM v1;
drop table t1 cascade;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to view v1
drop cascades to view v2
create table t1 (c1 int);
create view v1 as select c1 + 1 from t1;
insert into t1 values(1);
select * from v1;
?column?
----------
2
(1 row)
\d v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers
----------+--------+-----------
?column? | bigint |
\d+ v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
----------+--------+-----------+---------+-------------
?column? | bigint | | plain |
View definition:
SELECT t1.c1 OPERATOR(dolphin_catalog.+) 1::bigint AS "?column?"
FROM t1;
drop table t1;
\d v1;
WARNING: View v1 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.v1"
Column | Type | Modifiers
----------+--------+-----------
?column? | bigint |
\d+ v1;
WARNING: View v1 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
----------+--------+-----------+---------+-------------
?column? | bigint | | plain |
View definition:
SELECT t1.c1 OPERATOR(dolphin_catalog.+) 1::bigint AS "?column?"
FROM t1;
select * from v1;
ERROR: The view v1 is invalid, please make it valid before operation.
HINT: Please re-add missing table fields.
create table t1 (c1 int);
insert into t1 values(1);
select * from v1;
?column?
----------
2
(1 row)
\d v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers
----------+--------+-----------
?column? | bigint |
\d+ v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
----------+--------+-----------+---------+-------------
?column? | bigint | | plain |
View definition:
SELECT t1.c1::bigint OPERATOR(dolphin_catalog.+) 1::bigint AS "?column?"
FROM t1;
drop table t1 cascade;
NOTICE: drop cascades to view v1
create table t1 (c1 int);
create table t2 (c2 int);
create view v1 as select c1 + c2 from t1 join t2;
insert into t1 values(1);
insert into t2 values(2);
select * from v1;
?column?
----------
3
(1 row)
\d v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers
----------+--------+-----------
?column? | bigint |
\d+ v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
----------+--------+-----------+---------+-------------
?column? | bigint | | plain |
View definition:
SELECT t1.c1 OPERATOR(dolphin_catalog.+) t2.c2::bigint AS "?column?"
FROM t1
CROSS JOIN t2;
drop table t1;
\d v1;
WARNING: View v1 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.v1"
Column | Type | Modifiers
----------+--------+-----------
?column? | bigint |
\d+ v1;
WARNING: View v1 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
----------+--------+-----------+---------+-------------
?column? | bigint | | plain |
View definition:
SELECT t1.c1 OPERATOR(dolphin_catalog.+) t2.c2::bigint AS "?column?"
FROM t1
CROSS JOIN t2;
select * from v1;
ERROR: The view v1 is invalid, please make it valid before operation.
HINT: Please re-add missing table fields.
create table t1 (c1 int);
insert into t1 values(1);
select * from v1;
?column?
----------
3
(1 row)
\d v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers
----------+--------+-----------
?column? | bigint |
\d+ v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
----------+--------+-----------+---------+-------------
?column? | bigint | | plain |
View definition:
SELECT t1.c1::bigint OPERATOR(dolphin_catalog.+) t2.c2::bigint AS "?column?"
FROM t1
CROSS JOIN t2;
drop table t1 cascade;
NOTICE: drop cascades to view v1
-- enum
create table t1(c1 enum('male','female') not null);
create view v1 as select * from t1;
insert into t1 values('male');
select * from v1;
c1
------
male
(1 row)
\d+ v1;
--?.*
--?.*
--?.*
--?.*
View definition:
SELECT *
FROM t1;
drop table t1;
select * from v1;
ERROR: The view v1 is invalid, please make it valid before operation.
HINT: Please re-add missing table fields.
\d+ v1;
WARNING: View v1 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
--------+------+-----------+---------+-------------
c1 | ??? | | plain |
View definition:
SELECT *
FROM t1;
create table t1(c1 enum('male','female') not null);
select * from v1;
c1
----
(0 rows)
\d+ v1;
--?.*
--?.*
--?.*
--?.*
View definition:
SELECT t1.c1
FROM t1;
drop table t1 cascade;
NOTICE: drop cascades to view v1
-- set
create table t1(c1 set('a','b','c') not null);
NOTICE: CREATE TABLE will create implicit set "t1_c1_set" for column "t1.c1"
create view v1 as select * from t1;
insert into t1 values('a');
select * from v1;
c1
----
a
(1 row)
\d+ v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
--------+-----------+-----------------------------------------------+---------+-------------
c1 | t1_c1_set | character set UTF8 collate utf8mb4_general_ci | plain |
View definition:
SELECT *
FROM t1;
drop table t1;
select * from v1;
ERROR: The view v1 is invalid, please make it valid before operation.
HINT: Please re-add missing table fields.
\d+ v1;
WARNING: View v1 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
--------+------+-----------+---------+-------------
c1 | ??? | | plain |
View definition:
SELECT *
FROM t1;
create table t1(c1 set('a','b','c') not null);
NOTICE: CREATE TABLE will create implicit set "t1_c1_set" for column "t1.c1"
select * from v1;
c1
----
(0 rows)
\d+ v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
--------+-----------+-----------+---------+-------------
c1 | t1_c1_set | | plain |
View definition:
SELECT t1.c1
FROM t1;
drop table t1 cascade;
NOTICE: drop cascades to view v1
-- partition table
create table partition_t1 (a int, b int)
partition by range(a)
(
partition p1 values less than (100),
partition p2 values less than (200),
partition p3 values less than (MAXVALUE)
);
insert into partition_t1 values(99,1),(180,2),(300,4);
create view partition_v1 as select * from partition_t1 partition(p1);
select * from partition_v1;
a | b
----+---
99 | 1
(1 row)
\d+ partition_v1
View "test_view_table_depend.partition_v1"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
a | integer | | plain |
b | integer | | plain |
View definition:
SELECT *
FROM partition_t1 PARTITION(p1);
drop table partition_t1;
select * from partition_v1;
ERROR: The view partition_v1 is invalid, please make it valid before operation.
HINT: Please re-add missing table fields.
\d+ partition_v1
WARNING: View partition_v1 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.partition_v1"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
a | integer | | plain |
b | integer | | plain |
View definition:
SELECT *
FROM partition_t1 PARTITION(p1);
create table partition_t1 (a int, b int)
partition by range(a)
(
partition p1 values less than (100),
partition p2 values less than (200),
partition p3 values less than (MAXVALUE)
);
select * from partition_v1;
a | b
---+---
(0 rows)
\d+ partition_v1
View "test_view_table_depend.partition_v1"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
a | integer | | plain |
b | integer | | plain |
View definition:
SELECT partition_t1.a, partition_t1.b
FROM partition_t1 PARTITION(p1);
-- less partition and different partition type
drop table partition_t1;
create table partition_t1 (a char(20), b char(20))
partition by list(a)
(
partition p1 values ('100'),
partition p2 values ('200')
);
insert into partition_t1 values('100','1'),('200','2'),('200','3');
select * from partition_v1;
a | b
----------------------+----------------------
100 | 1
(1 row)
\d+ partition_v1
View "test_view_table_depend.partition_v1"
Column | Type | Modifiers | Storage | Description
--------+---------------+-----------+----------+-------------
a | character(20) | | extended |
b | character(20) | | extended |
View definition:
SELECT partition_t1.a, partition_t1.b
FROM partition_t1 PARTITION(p1);
drop table partition_t1 cascade;
NOTICE: drop cascades to view partition_v1
-- secondary partition
CREATE TABLE partition_t1
(
month_code VARCHAR2 ( 30 ) NOT NULL ,
dept_code VARCHAR2 ( 30 ) NOT NULL ,
user_no VARCHAR2 ( 30 ) NOT NULL ,
sales_amt int
)
PARTITION BY LIST (month_code) SUBPARTITION BY LIST (dept_code)
(
PARTITION p_201901 VALUES ( '201901' )
(
SUBPARTITION p_201901_a VALUES ( '1' ),
SUBPARTITION p_201901_b VALUES ( '2' )
),
PARTITION p_201902 VALUES ( '201902' )
(
SUBPARTITION p_201902_a VALUES ( '1' ),
SUBPARTITION p_201902_b VALUES ( '2' )
)
);
insert into partition_t1 values('201901', '1', '1', 1);
insert into partition_t1 values('201901', '2', '1', 1);
insert into partition_t1 values('201902', '1', '1', 1);
insert into partition_t1 values('201902', '2', '1', 1);
create view partition_v1 as select * from partition_t1 partition (p_201901);
create view partition_v2 as select * from partition_t1 partition (p_201901, p_201902);
create view partition_v3 as select * from partition_t1 partition for ('201901');
create view partition_v4 as select * from partition_t1 subpartition (p_201902_a);
create view partition_v5 as select * from partition_t1 subpartition for ('201902', '2');
select * from partition_v1;
month_code | dept_code | user_no | sales_amt
------------+-----------+---------+-----------
201901 | 1 | 1 | 1
201901 | 2 | 1 | 1
(2 rows)
\d+ partition_v1
View "test_view_table_depend.partition_v1"
Column | Type | Modifiers | Storage | Description
------------+-----------------------+-----------------------------------------------+----------+-------------
month_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
dept_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
user_no | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
sales_amt | integer | | plain |
View definition:
SELECT *
FROM partition_t1 PARTITION(p_201901);
select * from partition_v2;
month_code | dept_code | user_no | sales_amt
------------+-----------+---------+-----------
201901 | 1 | 1 | 1
201901 | 2 | 1 | 1
201902 | 1 | 1 | 1
201902 | 2 | 1 | 1
(4 rows)
\d+ partition_v2
View "test_view_table_depend.partition_v2"
Column | Type | Modifiers | Storage | Description
------------+-----------------------+-----------------------------------------------+----------+-------------
month_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
dept_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
user_no | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
sales_amt | integer | | plain |
View definition:
SELECT *
FROM partition_t1 PARTITION (p_201901, p_201902);
select * from partition_v3;
month_code | dept_code | user_no | sales_amt
------------+-----------+---------+-----------
201901 | 1 | 1 | 1
201901 | 2 | 1 | 1
(2 rows)
\d+ partition_v3
View "test_view_table_depend.partition_v3"
Column | Type | Modifiers | Storage | Description
------------+-----------------------+-----------------------------------------------+----------+-------------
month_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
dept_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
user_no | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
sales_amt | integer | | plain |
View definition:
SELECT *
FROM partition_t1 PARTITION FOR('201901'::character varying(30));
select * from partition_v4;
month_code | dept_code | user_no | sales_amt
------------+-----------+---------+-----------
201902 | 1 | 1 | 1
(1 row)
\d+ partition_v4
View "test_view_table_depend.partition_v4"
Column | Type | Modifiers | Storage | Description
------------+-----------------------+-----------------------------------------------+----------+-------------
month_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
dept_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
user_no | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
sales_amt | integer | | plain |
View definition:
SELECT *
FROM partition_t1 SUBPARTITION(p_201902_a);
select * from partition_v5;
month_code | dept_code | user_no | sales_amt
------------+-----------+---------+-----------
201902 | 2 | 1 | 1
(1 row)
\d+ partition_v5
View "test_view_table_depend.partition_v5"
Column | Type | Modifiers | Storage | Description
------------+-----------------------+-----------------------------------------------+----------+-------------
month_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
dept_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
user_no | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
sales_amt | integer | | plain |
View definition:
SELECT *
FROM partition_t1 SUBPARTITION FOR('201902'::character varying(30) ,'2'::character varying(30));
drop table partition_t1;
select * from partition_v1;
ERROR: The view partition_v1 is invalid, please make it valid before operation.
HINT: Please re-add missing table fields.
\d+ partition_v1
WARNING: View partition_v1 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.partition_v1"
Column | Type | Modifiers | Storage | Description
------------+-----------------------+-----------------------------------------------+----------+-------------
month_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
dept_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
user_no | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
sales_amt | integer | | plain |
View definition:
SELECT *
FROM partition_t1 PARTITION(p_201901);
select * from partition_v2;
ERROR: The view partition_v2 is invalid, please make it valid before operation.
HINT: Please re-add missing table fields.
\d+ partition_v2
WARNING: View partition_v2 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.partition_v2"
Column | Type | Modifiers | Storage | Description
------------+-----------------------+-----------------------------------------------+----------+-------------
month_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
dept_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
user_no | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
sales_amt | integer | | plain |
View definition:
SELECT *
FROM partition_t1 PARTITION (p_201901, p_201902);
select * from partition_v3;
ERROR: The view partition_v3 is invalid, please make it valid before operation.
HINT: Please re-add missing table fields.
\d+ partition_v3
WARNING: View partition_v3 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.partition_v3"
Column | Type | Modifiers | Storage | Description
------------+-----------------------+-----------------------------------------------+----------+-------------
month_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
dept_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
user_no | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
sales_amt | integer | | plain |
View definition:
SELECT *
FROM partition_t1 PARTITION FOR('201901'::character varying(30));
select * from partition_v4;
ERROR: The view partition_v4 is invalid, please make it valid before operation.
HINT: Please re-add missing table fields.
\d+ partition_v4
WARNING: View partition_v4 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.partition_v4"
Column | Type | Modifiers | Storage | Description
------------+-----------------------+-----------------------------------------------+----------+-------------
month_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
dept_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
user_no | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
sales_amt | integer | | plain |
View definition:
SELECT *
FROM partition_t1 SUBPARTITION(p_201902_a);
select * from partition_v5;
ERROR: The view partition_v5 is invalid, please make it valid before operation.
HINT: Please re-add missing table fields.
\d+ partition_v5
WARNING: View partition_v5 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.partition_v5"
Column | Type | Modifiers | Storage | Description
------------+-----------------------+-----------------------------------------------+----------+-------------
month_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
dept_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
user_no | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
sales_amt | integer | | plain |
View definition:
SELECT *
FROM partition_t1 SUBPARTITION FOR('201902'::character varying(30) ,'2'::character varying(30));
CREATE TABLE partition_t1
(
month_code VARCHAR2 ( 30 ) NOT NULL ,
dept_code VARCHAR2 ( 30 ) NOT NULL ,
user_no VARCHAR2 ( 30 ) NOT NULL ,
sales_amt int
)
PARTITION BY LIST (month_code) SUBPARTITION BY LIST (dept_code)
(
PARTITION p_201901 VALUES ( '201901' )
(
SUBPARTITION p_201901_a VALUES ( '1' ),
SUBPARTITION p_201901_b VALUES ( '2' )
),
PARTITION p_201902 VALUES ( '201902' )
(
SUBPARTITION p_201902_a VALUES ( '1' ),
SUBPARTITION p_201902_b VALUES ( '2' )
)
);
insert into partition_t1 values('201901', '1', '1', 1);
insert into partition_t1 values('201901', '2', '1', 1);
insert into partition_t1 values('201902', '1', '1', 1);
insert into partition_t1 values('201902', '2', '1', 1);
select * from partition_v1;
month_code | dept_code | user_no | sales_amt
------------+-----------+---------+-----------
201901 | 1 | 1 | 1
201901 | 2 | 1 | 1
(2 rows)
\d+ partition_v1
View "test_view_table_depend.partition_v1"
Column | Type | Modifiers | Storage | Description
------------+-----------------------+-----------------------------------------------+----------+-------------
month_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
dept_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
user_no | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
sales_amt | integer | | plain |
View definition:
SELECT partition_t1.month_code, partition_t1.dept_code, partition_t1.user_no,
partition_t1.sales_amt
FROM partition_t1 PARTITION(p_201901);
select * from partition_v2;
month_code | dept_code | user_no | sales_amt
------------+-----------+---------+-----------
201901 | 1 | 1 | 1
201901 | 2 | 1 | 1
201902 | 1 | 1 | 1
201902 | 2 | 1 | 1
(4 rows)
\d+ partition_v2
View "test_view_table_depend.partition_v2"
Column | Type | Modifiers | Storage | Description
------------+-----------------------+-----------------------------------------------+----------+-------------
month_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
dept_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
user_no | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
sales_amt | integer | | plain |
View definition:
SELECT partition_t1.month_code, partition_t1.dept_code, partition_t1.user_no,
partition_t1.sales_amt
FROM partition_t1 PARTITION (p_201901, p_201902);
select * from partition_v3;
month_code | dept_code | user_no | sales_amt
------------+-----------+---------+-----------
201901 | 1 | 1 | 1
201901 | 2 | 1 | 1
(2 rows)
\d+ partition_v3
View "test_view_table_depend.partition_v3"
Column | Type | Modifiers | Storage | Description
------------+-----------------------+-----------------------------------------------+----------+-------------
month_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
dept_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
user_no | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
sales_amt | integer | | plain |
View definition:
SELECT partition_t1.month_code, partition_t1.dept_code, partition_t1.user_no,
partition_t1.sales_amt
FROM partition_t1 PARTITION FOR('201901'::character varying(30));
select * from partition_v4;
month_code | dept_code | user_no | sales_amt
------------+-----------+---------+-----------
201902 | 1 | 1 | 1
(1 row)
\d+ partition_v4
View "test_view_table_depend.partition_v4"
Column | Type | Modifiers | Storage | Description
------------+-----------------------+-----------------------------------------------+----------+-------------
month_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
dept_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
user_no | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
sales_amt | integer | | plain |
View definition:
SELECT partition_t1.month_code, partition_t1.dept_code, partition_t1.user_no,
partition_t1.sales_amt
FROM partition_t1 SUBPARTITION(p_201902_a);
select * from partition_v5;
month_code | dept_code | user_no | sales_amt
------------+-----------+---------+-----------
201902 | 2 | 1 | 1
(1 row)
\d+ partition_v5
View "test_view_table_depend.partition_v5"
Column | Type | Modifiers | Storage | Description
------------+-----------------------+-----------------------------------------------+----------+-------------
month_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
dept_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
user_no | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
sales_amt | integer | | plain |
View definition:
SELECT partition_t1.month_code, partition_t1.dept_code, partition_t1.user_no,
partition_t1.sales_amt
FROM partition_t1 SUBPARTITION FOR('201902'::character varying(30) ,'2'::character varying(30));
-- less subpartition and different partition type
drop table partition_t1;
CREATE TABLE partition_t1
(
month_code int NOT NULL ,
dept_code int NOT NULL ,
user_no int NOT NULL ,
sales_amt varchar( 30 )
)
PARTITION BY RANGE (month_code) SUBPARTITION BY RANGE (dept_code)
(
PARTITION p_201901 VALUES LESS THAN ( 2019 )
(
SUBPARTITION p_201901_a VALUES LESS THAN ( 2 )
),
PARTITION p_201902 VALUES LESS THAN ( 2020 )
(
SUBPARTITION p_201902_a VALUES LESS THAN ( 3 )
)
);
insert into partition_t1 values(2018, 1, 1, 'a');
insert into partition_t1 values(2019, 2, 1, 'b');
select * from partition_v1;
month_code | dept_code | user_no | sales_amt
------------+-----------+---------+-----------
2018 | 1 | 1 | a
(1 row)
\d+ partition_v1
View "test_view_table_depend.partition_v1"
Column | Type | Modifiers | Storage | Description
------------+-----------------------+-----------+----------+-------------
month_code | integer | | plain |
dept_code | integer | | plain |
user_no | integer | | plain |
sales_amt | character varying(30) | | extended |
View definition:
SELECT partition_t1.month_code, partition_t1.dept_code, partition_t1.user_no,
partition_t1.sales_amt
FROM partition_t1 PARTITION(p_201901);
select * from partition_v2;
month_code | dept_code | user_no | sales_amt
------------+-----------+---------+-----------
2018 | 1 | 1 | a
2019 | 2 | 1 | b
(2 rows)
\d+ partition_v2
View "test_view_table_depend.partition_v2"
Column | Type | Modifiers | Storage | Description
------------+-----------------------+-----------+----------+-------------
month_code | integer | | plain |
dept_code | integer | | plain |
user_no | integer | | plain |
sales_amt | character varying(30) | | extended |
View definition:
SELECT partition_t1.month_code, partition_t1.dept_code, partition_t1.user_no,
partition_t1.sales_amt
FROM partition_t1 PARTITION (p_201901, p_201902);
select * from partition_v3;
ERROR: The view partition_v3 is invalid, please make it valid before operation.
HINT: Please re-add missing table fields.
\d+ partition_v3
ERROR: The view partition_v3 is invalid, please make it valid before operation.
HINT: Please re-add missing table fields.
CONTEXT: referenced column: pg_get_viewdef
select * from partition_v4;
month_code | dept_code | user_no | sales_amt
------------+-----------+---------+-----------
2019 | 2 | 1 | b
(1 row)
\d+ partition_v4
View "test_view_table_depend.partition_v4"
Column | Type | Modifiers | Storage | Description
------------+-----------------------+-----------+----------+-------------
month_code | integer | | plain |
dept_code | integer | | plain |
user_no | integer | | plain |
sales_amt | character varying(30) | | extended |
View definition:
SELECT partition_t1.month_code, partition_t1.dept_code, partition_t1.user_no,
partition_t1.sales_amt
FROM partition_t1 SUBPARTITION(p_201902_a);
select * from partition_v5;
ERROR: The view partition_v5 is invalid, please make it valid before operation.
HINT: Please re-add missing table fields.
\d+ partition_v5
WARNING: View partition_v5 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.partition_v5"
Column | Type | Modifiers | Storage | Description
------------+-----------------------+-----------------------------------------------+----------+-------------
month_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
dept_code | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
user_no | character varying(30) | character set UTF8 collate utf8mb4_general_ci | extended |
sales_amt | integer | | plain |
View definition:
SELECT partition_t1.month_code, partition_t1.dept_code, partition_t1.user_no,
partition_t1.sales_amt
FROM partition_t1 SUBPARTITION FOR('201902'::character varying(30) ,'2'::character varying(30));
drop table partition_t1 cascade;
NOTICE: drop cascades to 4 other objects
DETAIL: drop cascades to view partition_v1
drop cascades to view partition_v2
drop cascades to view partition_v4
drop cascades to view partition_v5
create table t1 (a int, b int);
create view v1 as select a + 3, b from t1;
alter table t1 modify (a time);
insert into t1 values ('14:00:00', 1);
select * from v1;
?column? | b
----------+---
140003 | 1
(1 row)
drop table t1 cascade;
NOTICE: drop cascades to view v1
create table t1 (c1 int, c2 int);
create view v1 as select * from t1;
drop table t1;
create table t1 (c2 int, c3 int, c4 int, c1 int);
insert into t1 values (2, 3, 4, 1);
select * from v1;
c1 | c2
----+----
1 | 2
(1 row)
drop table t1 cascade;
NOTICE: drop cascades to view v1
-- test \d with table column type changed
create table t1(b char);
create view v1 as select * from t1;
desc v1;
Field | Type | Null | Key | Default | Extra
-------+--------------+------+-----+---------+-------
b | character(1) | YES | | NULL |
(1 row)
alter table t1 ALTER COLUMN b type int;
desc v1;
Field | Type | Null | Key | Default | Extra
-------+---------+------+-----+---------+-------
b | integer | YES | | NULL |
(1 row)
desc t1;
Field | Type | Null | Key | Default | Extra
-------+---------+------+-----+---------+-------
b | integer | YES | | NULL |
(1 row)
drop view v1;
drop table t1;
create table t1 (c1 int, c2 varchar(10));
create view v1 as select * from t1;
drop table t1;
desc v1;
ERROR: View v1 references invalid table(s), view(s) or column(s).
create table t1 (c1 text, c2 float8);
desc v1;
Field | Type | Null | Key | Default | Extra
-------+------------------+------+-----+---------+-------
c1 | text | YES | | NULL |
c2 | double precision | YES | | NULL |
(2 rows)
drop view v1;
drop table t1;
create table t1 (c1 int, c2 varchar(10));
create view v1 as select * from t1;
drop table t1;
\d v1;
WARNING: View v1 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.v1"
Column | Type | Modifiers
--------+-----------------------+-----------------------------------------------
c1 | integer |
c2 | character varying(10) | character set UTF8 collate utf8mb4_general_ci
create table t1 (c1 text, c2 float8);
\d v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers
--------+------------------+-----------
c1 | text |
c2 | double precision |
drop view v1;
drop table t1;
-- test \d+ with table column type changed
create table t1 (c1 int, c2 varchar(10));
create view v1 as select * from t1;
drop table t1;
\d+ v1;
WARNING: View v1 references invalid table(s), view(s) or column(s).
CONTEXT: referenced column: pg_get_viewdef
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
--------+-----------------------+-----------------------------------------------+----------+-------------
c1 | integer | | plain |
c2 | character varying(10) | character set UTF8 collate utf8mb4_general_ci | extended |
View definition:
SELECT *
FROM t1;
create table t1 (c1 text, c2 float8);
\d+ v1;
View "test_view_table_depend.v1"
Column | Type | Modifiers | Storage | Description
--------+------------------+-----------+----------+-------------
c1 | text | | extended |
c2 | double precision | | plain |
View definition:
SELECT t1.c1, t1.c2 FROM t1;
drop view v1;
drop table t1;
describe pg_catalog.pg_tables;
Field | Type | Null | Key | Default | Extra
---------------+--------------------------+------+-----+---------+-------
schemaname | name | YES | | NULL |
tablename | name | YES | | NULL |
tableowner | name | YES | | NULL |
tablespace | name | YES | | NULL |
hasindexes | boolean | YES | | NULL |
hasrules | boolean | YES | | NULL |
hastriggers | boolean | YES | | NULL |
tablecreator | name | YES | | NULL |
created | timestamp with time zone | YES | | NULL |
last_ddl_time | timestamp with time zone | YES | | NULL |
(10 rows)
drop schema test_view_table_depend cascade;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to table t2
drop cascades to view partition_v3
reset current_schema;