| -- |
| -- IF EXISTS tests |
| -- |
| -- table (will be really dropped at the end) |
| DROP TABLE test_exists; |
| ERROR: table "test_exists" does not exist |
| DROP TABLE IF EXISTS test_exists; |
| NOTICE: table "test_exists" does not exist, skipping |
| CREATE TABLE test_exists (a int, b text); |
| -- view |
| DROP VIEW test_view_exists; |
| ERROR: view "test_view_exists" does not exist |
| DROP VIEW IF EXISTS test_view_exists; |
| NOTICE: view "test_view_exists" does not exist, skipping |
| CREATE VIEW test_view_exists AS select * from test_exists; |
| DROP VIEW IF EXISTS test_view_exists; |
| DROP VIEW test_view_exists; |
| ERROR: view "test_view_exists" does not exist |
| -- index |
| DROP INDEX test_index_exists; |
| ERROR: index "test_index_exists" does not exist |
| DROP INDEX IF EXISTS test_index_exists; |
| NOTICE: index "test_index_exists" does not exist, skipping |
| CREATE INDEX test_index_exists on test_exists(a); |
| DROP INDEX IF EXISTS test_index_exists; |
| DROP INDEX test_index_exists; |
| ERROR: index "test_index_exists" does not exist |
| -- sequence |
| DROP SEQUENCE test_sequence_exists; |
| ERROR: sequence "test_sequence_exists" does not exist |
| DROP SEQUENCE IF EXISTS test_sequence_exists; |
| NOTICE: sequence "test_sequence_exists" does not exist, skipping |
| CREATE SEQUENCE test_sequence_exists; |
| DROP SEQUENCE IF EXISTS test_sequence_exists; |
| DROP SEQUENCE test_sequence_exists; |
| ERROR: sequence "test_sequence_exists" does not exist |
| -- schema |
| DROP SCHEMA test_schema_exists; |
| ERROR: schema "test_schema_exists" does not exist |
| DROP SCHEMA IF EXISTS test_schema_exists; |
| NOTICE: schema "test_schema_exists" does not exist, skipping |
| CREATE SCHEMA test_schema_exists; |
| DROP SCHEMA IF EXISTS test_schema_exists; |
| DROP SCHEMA test_schema_exists; |
| ERROR: schema "test_schema_exists" does not exist |
| -- type |
| DROP TYPE test_type_exists; |
| ERROR: type "test_type_exists" does not exist |
| DROP TYPE IF EXISTS test_type_exists; |
| NOTICE: type "test_type_exists" does not exist, skipping |
| CREATE type test_type_exists as (a int, b text); |
| DROP TYPE IF EXISTS test_type_exists; |
| DROP TYPE test_type_exists; |
| ERROR: type "test_type_exists" does not exist |
| -- domain |
| DROP DOMAIN test_domain_exists; |
| ERROR: type "test_domain_exists" does not exist |
| DROP DOMAIN IF EXISTS test_domain_exists; |
| NOTICE: type "test_domain_exists" does not exist, skipping |
| CREATE domain test_domain_exists as int not null check (value > 0); |
| DROP DOMAIN IF EXISTS test_domain_exists; |
| DROP DOMAIN test_domain_exists; |
| ERROR: type "test_domain_exists" does not exist |
| --- |
| --- role/user/group |
| --- |
| CREATE USER regress_test_u1; |
| CREATE ROLE regress_test_r1; |
| CREATE GROUP regress_test_g1; |
| DROP USER regress_test_u2; |
| ERROR: role "regress_test_u2" does not exist |
| DROP USER IF EXISTS regress_test_u1, regress_test_u2; |
| NOTICE: role "regress_test_u2" does not exist, skipping |
| DROP USER regress_test_u1; |
| ERROR: role "regress_test_u1" does not exist |
| DROP ROLE regress_test_r2; |
| ERROR: role "regress_test_r2" does not exist |
| DROP ROLE IF EXISTS regress_test_r1, regress_test_r2; |
| NOTICE: role "regress_test_r2" does not exist, skipping |
| DROP ROLE regress_test_r1; |
| ERROR: role "regress_test_r1" does not exist |
| DROP GROUP regress_test_g2; |
| ERROR: role "regress_test_g2" does not exist |
| DROP GROUP IF EXISTS regress_test_g1, regress_test_g2; |
| NOTICE: role "regress_test_g2" does not exist, skipping |
| DROP GROUP regress_test_g1; |
| ERROR: role "regress_test_g1" does not exist |
| --create a schema with the same name as the logged in user name and try to drop and recreate a table |
| drop database if exists drop_table_test; |
| NOTICE: database "drop_table_test" does not exist, skipping |
| create database drop_table_test; |
| \c drop_table_test |
| --get the username and set it to a variable |
| \set cur_user `echo $USER` |
| CREATE SCHEMA :cur_user; |
| --the following table will be created in the ":cur_user" schema because |
| -- the search_path is '"$user",public' |
| CREATE TABLE tbl_to_drop(i int); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| DROP TABLE tbl_to_drop; |
| CREATE TABLE tbl_to_drop(i int); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| DROP SCHEMA :cur_user CASCADE; |
| NOTICE: drop cascades to table tbl_to_drop |
| \c regression |
| drop database drop_table_test; |
| -- collation |
| DROP COLLATION IF EXISTS test_collation_exists; |
| NOTICE: collation "test_collation_exists" does not exist, skipping |
| -- conversion |
| DROP CONVERSION test_conversion_exists; |
| ERROR: conversion "test_conversion_exists" does not exist |
| DROP CONVERSION IF EXISTS test_conversion_exists; |
| NOTICE: conversion "test_conversion_exists" does not exist, skipping |
| CREATE CONVERSION test_conversion_exists |
| FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8; |
| DROP CONVERSION test_conversion_exists; |
| -- text search parser |
| DROP TEXT SEARCH PARSER test_tsparser_exists; |
| ERROR: text search parser "test_tsparser_exists" does not exist |
| DROP TEXT SEARCH PARSER IF EXISTS test_tsparser_exists; |
| NOTICE: text search parser "test_tsparser_exists" does not exist, skipping |
| -- text search dictionary |
| DROP TEXT SEARCH DICTIONARY test_tsdict_exists; |
| ERROR: text search dictionary "test_tsdict_exists" does not exist |
| DROP TEXT SEARCH DICTIONARY IF EXISTS test_tsdict_exists; |
| NOTICE: text search dictionary "test_tsdict_exists" does not exist, skipping |
| CREATE TEXT SEARCH DICTIONARY test_tsdict_exists ( |
| Template=ispell, |
| DictFile=ispell_sample, |
| AffFile=ispell_sample |
| ); |
| DROP TEXT SEARCH DICTIONARY test_tsdict_exists; |
| -- test search template |
| DROP TEXT SEARCH TEMPLATE test_tstemplate_exists; |
| ERROR: text search template "test_tstemplate_exists" does not exist |
| DROP TEXT SEARCH TEMPLATE IF EXISTS test_tstemplate_exists; |
| NOTICE: text search template "test_tstemplate_exists" does not exist, skipping |
| -- text search configuration |
| DROP TEXT SEARCH CONFIGURATION test_tsconfig_exists; |
| ERROR: text search configuration "test_tsconfig_exists" does not exist |
| DROP TEXT SEARCH CONFIGURATION IF EXISTS test_tsconfig_exists; |
| NOTICE: text search configuration "test_tsconfig_exists" does not exist, skipping |
| CREATE TEXT SEARCH CONFIGURATION test_tsconfig_exists (COPY=english); |
| DROP TEXT SEARCH CONFIGURATION test_tsconfig_exists; |
| -- extension |
| DROP EXTENSION test_extension_exists; |
| ERROR: extension "test_extension_exists" does not exist |
| DROP EXTENSION IF EXISTS test_extension_exists; |
| NOTICE: extension "test_extension_exists" does not exist, skipping |
| -- functions |
| DROP FUNCTION test_function_exists(); |
| ERROR: function test_function_exists() does not exist |
| DROP FUNCTION IF EXISTS test_function_exists(); |
| NOTICE: function test_function_exists() does not exist, skipping |
| DROP FUNCTION test_function_exists(int, text, int[]); |
| ERROR: function test_function_exists(integer, text, integer[]) does not exist |
| DROP FUNCTION IF EXISTS test_function_exists(int, text, int[]); |
| NOTICE: function test_function_exists(pg_catalog.int4,text,pg_catalog.int4[]) does not exist, skipping |
| -- aggregate |
| DROP AGGREGATE test_aggregate_exists(*); |
| ERROR: aggregate test_aggregate_exists(*) does not exist |
| DROP AGGREGATE IF EXISTS test_aggregate_exists(*); |
| NOTICE: aggregate test_aggregate_exists() does not exist, skipping |
| DROP AGGREGATE test_aggregate_exists(int); |
| ERROR: aggregate test_aggregate_exists(integer) does not exist |
| DROP AGGREGATE IF EXISTS test_aggregate_exists(int); |
| NOTICE: aggregate test_aggregate_exists(pg_catalog.int4) does not exist, skipping |
| -- operator |
| DROP OPERATOR @#@ (int, int); |
| ERROR: operator does not exist: integer @#@ integer |
| DROP OPERATOR IF EXISTS @#@ (int, int); |
| NOTICE: operator @#@ does not exist, skipping |
| CREATE OPERATOR @#@ |
| (leftarg = int8, rightarg = int8, procedure = int8xor); |
| DROP OPERATOR @#@ (int8, int8); |
| -- language |
| DROP LANGUAGE test_language_exists; |
| ERROR: language "test_language_exists" does not exist |
| DROP LANGUAGE IF EXISTS test_language_exists; |
| NOTICE: language "test_language_exists" does not exist, skipping |
| -- cast |
| DROP CAST (text AS text); |
| ERROR: cast from type text to type text does not exist |
| DROP CAST IF EXISTS (text AS text); |
| NOTICE: cast from type text to type text does not exist, skipping |
| -- trigger |
| DROP TRIGGER test_trigger_exists ON test_exists; |
| ERROR: trigger "test_trigger_exists" for table "test_exists" does not exist |
| DROP TRIGGER IF EXISTS test_trigger_exists ON test_exists; |
| NOTICE: trigger "test_trigger_exists" for relation "test_exists" does not exist, skipping |
| DROP TRIGGER test_trigger_exists ON no_such_table; |
| ERROR: relation "no_such_table" does not exist |
| DROP TRIGGER IF EXISTS test_trigger_exists ON no_such_table; |
| NOTICE: relation "no_such_table" does not exist, skipping |
| DROP TRIGGER test_trigger_exists ON no_such_schema.no_such_table; |
| ERROR: schema "no_such_schema" does not exist |
| DROP TRIGGER IF EXISTS test_trigger_exists ON no_such_schema.no_such_table; |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| CREATE TRIGGER test_trigger_exists |
| BEFORE UPDATE ON test_exists |
| FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger(); |
| DROP TRIGGER test_trigger_exists ON test_exists; |
| -- rule |
| DROP RULE test_rule_exists ON test_exists; |
| ERROR: rule "test_rule_exists" for relation "test_exists" does not exist |
| DROP RULE IF EXISTS test_rule_exists ON test_exists; |
| NOTICE: rule "test_rule_exists" for relation "test_exists" does not exist, skipping |
| DROP RULE test_rule_exists ON no_such_table; |
| ERROR: relation "no_such_table" does not exist |
| DROP RULE IF EXISTS test_rule_exists ON no_such_table; |
| NOTICE: relation "no_such_table" does not exist, skipping |
| DROP RULE test_rule_exists ON no_such_schema.no_such_table; |
| ERROR: schema "no_such_schema" does not exist |
| DROP RULE IF EXISTS test_rule_exists ON no_such_schema.no_such_table; |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| CREATE RULE test_rule_exists AS ON INSERT TO test_exists |
| DO INSTEAD |
| INSERT INTO test_exists VALUES (NEW.a, NEW.b || NEW.a::text); |
| DROP RULE test_rule_exists ON test_exists; |
| -- foreign data wrapper |
| DROP FOREIGN DATA WRAPPER test_fdw_exists; |
| ERROR: foreign-data wrapper "test_fdw_exists" does not exist |
| DROP FOREIGN DATA WRAPPER IF EXISTS test_fdw_exists; |
| NOTICE: foreign-data wrapper "test_fdw_exists" does not exist, skipping |
| -- foreign server |
| DROP SERVER test_server_exists; |
| ERROR: server "test_server_exists" does not exist |
| DROP SERVER IF EXISTS test_server_exists; |
| NOTICE: server "test_server_exists" does not exist, skipping |
| -- operator class |
| DROP OPERATOR CLASS test_operator_class USING btree; |
| ERROR: operator class "test_operator_class" does not exist for access method "btree" |
| DROP OPERATOR CLASS IF EXISTS test_operator_class USING btree; |
| NOTICE: operator class "test_operator_class" does not exist for access method "btree", skipping |
| DROP OPERATOR CLASS test_operator_class USING no_such_am; |
| ERROR: access method "no_such_am" does not exist |
| DROP OPERATOR CLASS IF EXISTS test_operator_class USING no_such_am; |
| ERROR: access method "no_such_am" does not exist |
| -- operator family |
| DROP OPERATOR FAMILY test_operator_family USING btree; |
| ERROR: operator family "test_operator_family" does not exist for access method "btree" |
| DROP OPERATOR FAMILY IF EXISTS test_operator_family USING btree; |
| NOTICE: operator family "test_operator_family" does not exist for access method "btree", skipping |
| DROP OPERATOR FAMILY test_operator_family USING no_such_am; |
| ERROR: access method "no_such_am" does not exist |
| DROP OPERATOR FAMILY IF EXISTS test_operator_family USING no_such_am; |
| ERROR: access method "no_such_am" does not exist |
| -- access method |
| DROP ACCESS METHOD no_such_am; |
| ERROR: access method "no_such_am" does not exist |
| DROP ACCESS METHOD IF EXISTS no_such_am; |
| NOTICE: access method "no_such_am" does not exist, skipping |
| -- drop the table |
| DROP TABLE IF EXISTS test_exists; |
| DROP TABLE test_exists; |
| ERROR: table "test_exists" does not exist |
| -- be tolerant with missing schemas, types, etc |
| DROP AGGREGATE IF EXISTS no_such_schema.foo(int); |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP AGGREGATE IF EXISTS foo(no_such_type); |
| NOTICE: type "no_such_type" does not exist, skipping |
| DROP AGGREGATE IF EXISTS foo(no_such_schema.no_such_type); |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP CAST IF EXISTS (INTEGER AS no_such_type2); |
| NOTICE: type "no_such_type2" does not exist, skipping |
| DROP CAST IF EXISTS (no_such_type1 AS INTEGER); |
| NOTICE: type "no_such_type1" does not exist, skipping |
| DROP CAST IF EXISTS (INTEGER AS no_such_schema.bar); |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP CAST IF EXISTS (no_such_schema.foo AS INTEGER); |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP COLLATION IF EXISTS no_such_schema.foo; |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP CONVERSION IF EXISTS no_such_schema.foo; |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP DOMAIN IF EXISTS no_such_schema.foo; |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP FOREIGN TABLE IF EXISTS no_such_schema.foo; |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP FUNCTION IF EXISTS no_such_schema.foo(); |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP FUNCTION IF EXISTS foo(no_such_type); |
| NOTICE: type "no_such_type" does not exist, skipping |
| DROP FUNCTION IF EXISTS foo(no_such_schema.no_such_type); |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP INDEX IF EXISTS no_such_schema.foo; |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP MATERIALIZED VIEW IF EXISTS no_such_schema.foo; |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP OPERATOR IF EXISTS no_such_schema.+ (int, int); |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP OPERATOR IF EXISTS + (no_such_type, no_such_type); |
| NOTICE: type "no_such_type" does not exist, skipping |
| DROP OPERATOR IF EXISTS + (no_such_schema.no_such_type, no_such_schema.no_such_type); |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP OPERATOR IF EXISTS # (NONE, no_such_schema.no_such_type); |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP OPERATOR CLASS IF EXISTS no_such_schema.widget_ops USING btree; |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP OPERATOR FAMILY IF EXISTS no_such_schema.float_ops USING btree; |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP RULE IF EXISTS foo ON no_such_schema.bar; |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP SEQUENCE IF EXISTS no_such_schema.foo; |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP TABLE IF EXISTS no_such_schema.foo; |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP TEXT SEARCH CONFIGURATION IF EXISTS no_such_schema.foo; |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP TEXT SEARCH DICTIONARY IF EXISTS no_such_schema.foo; |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP TEXT SEARCH PARSER IF EXISTS no_such_schema.foo; |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP TEXT SEARCH TEMPLATE IF EXISTS no_such_schema.foo; |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP TRIGGER IF EXISTS foo ON no_such_schema.bar; |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP TYPE IF EXISTS no_such_schema.foo; |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| DROP VIEW IF EXISTS no_such_schema.foo; |
| NOTICE: schema "no_such_schema" does not exist, skipping |
| -- Check we receive an ambiguous function error when there are |
| -- multiple matching functions. |
| CREATE FUNCTION test_ambiguous_funcname(int) returns int as $$ select $1; $$ language sql; |
| CREATE FUNCTION test_ambiguous_funcname(text) returns text as $$ select $1; $$ language sql; |
| DROP FUNCTION test_ambiguous_funcname; |
| ERROR: function name "test_ambiguous_funcname" is not unique |
| HINT: Specify the argument list to select the function unambiguously. |
| DROP FUNCTION IF EXISTS test_ambiguous_funcname; |
| ERROR: function name "test_ambiguous_funcname" is not unique |
| HINT: Specify the argument list to select the function unambiguously. |
| -- cleanup |
| DROP FUNCTION test_ambiguous_funcname(int); |
| DROP FUNCTION test_ambiguous_funcname(text); |
| -- Likewise for procedures. |
| CREATE PROCEDURE test_ambiguous_procname(int) as $$ begin end; $$ language plpgsql; |
| CREATE PROCEDURE test_ambiguous_procname(text) as $$ begin end; $$ language plpgsql; |
| DROP PROCEDURE test_ambiguous_procname; |
| ERROR: procedure name "test_ambiguous_procname" is not unique |
| HINT: Specify the argument list to select the procedure unambiguously. |
| DROP PROCEDURE IF EXISTS test_ambiguous_procname; |
| ERROR: procedure name "test_ambiguous_procname" is not unique |
| HINT: Specify the argument list to select the procedure unambiguously. |
| -- Check we get a similar error if we use ROUTINE instead of PROCEDURE. |
| DROP ROUTINE IF EXISTS test_ambiguous_procname; |
| ERROR: routine name "test_ambiguous_procname" is not unique |
| HINT: Specify the argument list to select the routine unambiguously. |
| -- cleanup |
| DROP PROCEDURE test_ambiguous_procname(int); |
| DROP PROCEDURE test_ambiguous_procname(text); |
| -- This test checks both the functionality of 'if exists' and the syntax |
| -- of the drop database command. |
| drop database test_database_exists (force); |
| ERROR: database "test_database_exists" does not exist |
| drop database test_database_exists with (force); |
| ERROR: database "test_database_exists" does not exist |
| drop database if exists test_database_exists (force); |
| NOTICE: database "test_database_exists" does not exist, skipping |
| drop database if exists test_database_exists with (force); |
| NOTICE: database "test_database_exists" does not exist, skipping |
