Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- This script was generated by the Schema Diff utility in pgAdmin 4
-- For the circular dependencies, the order in which Schema Diff writes the objects is not very sophisticated
-- and may require manual changes to the script to ensure changes are applied in the correct order.
-- Please report an issue for any failure with the reproduction steps.
BEGIN;
-- Cast: money -> bigint
-- DROP CAST (money AS bigint);
CREATE CAST (money AS bigint)
WITHOUT FUNCTION
AS IMPLICIT;
COMMENT ON CAST (money AS bigint) IS 'money -> bigint';
-- Event Trigger: evt_tri_src on database diff_source
-- DROP EVENT TRIGGER IF EXISTS evt_tri_src;
CREATE EVENT TRIGGER evt_tri_src ON DDL_COMMAND_START
EXECUTE PROCEDURE public.evt_tri_fun();
COMMENT ON EVENT TRIGGER evt_tri_src
IS 'Event Trigger Source';
ALTER EVENT TRIGGER evt_tri_src
OWNER TO postgres;
DROP EVENT TRIGGER IF EXISTS evt_tri_tar;
-- WARNING:
-- We have found the difference in either of Trigger Function, Event or WHEN
-- so we need to drop the existing event trigger first and re-create it.
DROP EVENT TRIGGER IF EXISTS evt_tri_diff_event1;
CREATE EVENT TRIGGER evt_tri_diff_event1 ON DDL_COMMAND_START
EXECUTE PROCEDURE public.evt_tri_fun();
-- WARNING:
-- We have found the difference in either of Trigger Function, Event or WHEN
-- so we need to drop the existing event trigger first and re-create it.
DROP EVENT TRIGGER IF EXISTS evt_tri_diff_event2;
CREATE EVENT TRIGGER evt_tri_diff_event2 ON DDL_COMMAND_END
EXECUTE PROCEDURE public.evt_tri_fun();
-- WARNING:
-- We have found the difference in either of Trigger Function, Event or WHEN
-- so we need to drop the existing event trigger first and re-create it.
DROP EVENT TRIGGER IF EXISTS evt_tri_diff_event3;
CREATE EVENT TRIGGER evt_tri_diff_event3 ON SQL_DROP
EXECUTE PROCEDURE public.evt_tri_fun();
ALTER EVENT TRIGGER evt_tri_diff_enable_status3
ENABLE ALWAYS;
-- WARNING:
-- We have found the difference in either of Trigger Function, Event or WHEN
-- so we need to drop the existing event trigger first and re-create it.
DROP EVENT TRIGGER IF EXISTS evt_tri_diff_func;
CREATE EVENT TRIGGER evt_tri_diff_func ON DDL_COMMAND_START
EXECUTE PROCEDURE public.evt_tri_fun();
ALTER EVENT TRIGGER evt_tri_diff_enable_status2
ENABLE REPLICA;
ALTER EVENT TRIGGER evt_tri_diff_enable_status1
DISABLE;
ALTER EXTENSION adminpack
UPDATE TO "1.1";
-- Foreign Data Wrapper: fdw_src
-- DROP FOREIGN DATA WRAPPER fdw_src
CREATE FOREIGN DATA WRAPPER fdw_src
VALIDATOR pg_catalog.postgresql_fdw_validator;
ALTER FOREIGN DATA WRAPPER fdw_src
OWNER TO postgres;
COMMENT ON FOREIGN DATA WRAPPER fdw_src
IS 'Foreign Data Wrapper';
DROP FOREIGN DATA WRAPPER fdw_tar ;
ALTER FOREIGN DATA WRAPPER fdw_diff_add_validator
VALIDATOR pg_catalog.postgresql_fdw_validator;
ALTER FOREIGN DATA WRAPPER fdw_diff_remove_options
OPTIONS (DROP debug);
ALTER FOREIGN DATA WRAPPER fdw_diff_remove_validator
NO VALIDATOR;
GRANT USAGE ON FOREIGN DATA WRAPPER fdw_diff_acl_add TO PUBLIC;
GRANT USAGE ON FOREIGN DATA WRAPPER fdw_diff_acl_add TO postgres WITH GRANT OPTION;
REVOKE ALL ON FOREIGN DATA WRAPPER fdw_diff_acl_revoke FROM PUBLIC;
REVOKE ALL ON FOREIGN DATA WRAPPER fdw_diff_acl_revoke FROM postgres;
ALTER FOREIGN DATA WRAPPER fdw_diff_options
OPTIONS (SET debug 'false');
ALTER FOREIGN DATA WRAPPER fdw_diff_add_options
OPTIONS (ADD debug 'true');
-- Language: src_trusted_language
-- DROP LANGUAGE src_trusted_language
CREATE TRUSTED PROCEDURAL LANGUAGE src_trusted_language
HANDLER plpgsql_call_handler
INLINE plpgsql_inline_handler
VALIDATOR plpgsql_validator;
ALTER LANGUAGE src_trusted_language
OWNER TO postgres;
COMMENT ON LANGUAGE src_trusted_language
IS 'Custom Trusted Language';
GRANT USAGE ON LANGUAGE src_trusted_language TO PUBLIC;
GRANT USAGE ON LANGUAGE src_trusted_language TO postgres WITH GRANT OPTION;
-- Language: src_proc_language
-- DROP LANGUAGE src_proc_language
CREATE PROCEDURAL LANGUAGE src_proc_language
HANDLER plpgsql_call_handler
INLINE plpgsql_inline_handler
VALIDATOR plpgsql_validator;
ALTER LANGUAGE src_proc_language
OWNER TO postgres;
COMMENT ON LANGUAGE src_proc_language
IS 'Custom Procedural Language';
DROP LANGUAGE tar_language;
REVOKE ALL ON LANGUAGE lan_diff_acl_revoke FROM PUBLIC;
REVOKE ALL ON LANGUAGE lan_diff_acl_revoke FROM postgres;
GRANT USAGE ON LANGUAGE lan_diff_acl_add TO PUBLIC;
GRANT USAGE ON LANGUAGE lan_diff_acl_add TO postgres WITH GRANT OPTION;
-- WARNING:
-- We have found the difference in either of TRUSTED, HANDLER, INLINE or VALIDATOR,
-- so we need to drop the existing language first and re-create it.
DROP LANGUAGE lan_diff_type CASCADE;
CREATE TRUSTED PROCEDURAL LANGUAGE lan_diff_type
HANDLER plpgsql_call_handler
INLINE plpgsql_inline_handler
VALIDATOR plpgsql_validator
;
-- WARNING:
-- We have found the difference in either of TRUSTED, HANDLER, INLINE or VALIDATOR,
-- so we need to drop the existing language first and re-create it.
DROP LANGUAGE lan_diff_inline_validator CASCADE;
CREATE TRUSTED PROCEDURAL LANGUAGE lan_diff_inline_validator
HANDLER plpgsql_call_handler
INLINE prsd_end
VALIDATOR pg_stat_reset_single_table_counters
;
-- Publication: with_one_table_alter
-- DROP PUBLICATION with_one_table_alter;
CREATE PUBLICATION with_one_table_alter
FOR TABLE test_schema_diff.table_for_publication
WITH (publish = 'insert, update, truncate');
DROP PUBLICATION with_one_table_in_target_alter;
-- Foreign Server: fs_src
-- DROP SERVER fs_src
CREATE SERVER fs_src
TYPE 'PG'
VERSION '10'
FOREIGN DATA WRAPPER test_fdw_for_foreign_server
OPTIONS (host '127.0.0.1', port '5432');
ALTER SERVER fs_src
OWNER TO postgres;
COMMENT ON SERVER fs_src
IS 'Foreign Server';
DROP SERVER fs_tar ;
ALTER SERVER fs_diff_options_modify
OPTIONS (SET host '192.168.1.1', SET port '8080');
-- WARNING:
-- We have found the difference in SERVER TYPE OR FOREIGN DATA WRAPPER
-- so we need to drop the existing foreign server first and re-create it.
DROP SERVER fs_diff_type_version_add;
CREATE SERVER fs_diff_type_version_add
TYPE 'PG'
VERSION '10'
FOREIGN DATA WRAPPER test_fdw_for_foreign_server;
ALTER SERVER fs_diff_type_version_add
VERSION '10';
GRANT USAGE ON FOREIGN SERVER fs_diff_acl_add TO PUBLIC;
GRANT USAGE ON FOREIGN SERVER fs_diff_acl_add TO postgres WITH GRANT OPTION;
ALTER SERVER fs_diff_options_remove
OPTIONS (DROP host, DROP port);
REVOKE ALL ON FOREIGN SERVER fs_diff_acl_revoke FROM PUBLIC;
REVOKE ALL ON FOREIGN SERVER fs_diff_acl_revoke FROM postgres;
-- WARNING:
-- We have found the difference in SERVER TYPE OR FOREIGN DATA WRAPPER
-- so we need to drop the existing foreign server first and re-create it.
DROP SERVER fs_diff_type_version_modify;
CREATE SERVER fs_diff_type_version_modify
TYPE 'PG'
VERSION '10'
FOREIGN DATA WRAPPER test_fdw_for_foreign_server;
ALTER SERVER fs_diff_type_version_modify
VERSION '10';
ALTER SERVER fs_diff_options_add
OPTIONS (ADD host '127.0.0.1', ADD port '5432');
-- WARNING:
-- We have found the difference in SERVER TYPE OR FOREIGN DATA WRAPPER
-- so we need to drop the existing foreign server first and re-create it.
DROP SERVER fs_diff_type_version_remove;
CREATE SERVER fs_diff_type_version_remove
FOREIGN DATA WRAPPER test_fdw_for_foreign_server;
ALTER USER MAPPING FOR postgres SERVER test_fs_for_user_mapping
OPTIONS (ADD password 'admin123');
ALTER USER MAPPING FOR public SERVER test_fs_for_user_mapping
OPTIONS (DROP password);
DROP FUNCTION public.evt_tri_fun2();
-- Collation: coll_src;
-- DROP COLLATION test_schema_diff.coll_src;
CREATE COLLATION test_schema_diff.coll_src
(LC_COLLATE = 'POSIX', LC_CTYPE = 'POSIX');
ALTER COLLATION test_schema_diff.coll_src
OWNER TO postgres;
COMMENT ON COLLATION test_schema_diff.coll_src
IS 'Test Comment';
DROP COLLATION test_schema_diff.coll_tar;
-- WARNING:
-- We have found the difference in either of LC_COLLATE or LC_CTYPE or LOCALE,
-- so we need to drop the existing collation first and re-create it.
DROP COLLATION test_schema_diff.coll_diff;
CREATE COLLATION test_schema_diff.coll_diff
(LC_COLLATE = 'POSIX', LC_CTYPE = 'POSIX');
COMMENT ON COLLATION test_schema_diff.coll_diff
IS 'Test Comment';
-- DOMAIN: test_schema_diff.dom_src
-- DROP DOMAIN test_schema_diff.dom_src;
CREATE DOMAIN test_schema_diff.dom_src
AS bigint
DEFAULT 100
NOT NULL;
ALTER DOMAIN test_schema_diff.dom_src OWNER TO postgres;
ALTER DOMAIN test_schema_diff.dom_src
ADD CONSTRAINT con_src CHECK (VALUE <> 100);
DROP DOMAIN test_schema_diff.dom_tar CASCADE;
-- WARNING:
-- We have found the difference in either of datatype or collation,
-- so we need to drop the existing domain first and re-create it.
DROP DOMAIN test_schema_diff.dom_type_diff;
CREATE DOMAIN test_schema_diff.dom_type_diff
AS character varying(40)
COLLATE pg_catalog."POSIX";
ALTER DOMAIN test_schema_diff.dom_type_diff
ADD CONSTRAINT cons1 CHECK (VALUE::text <> 'pgAdmin3'::text);
ALTER DOMAIN test_schema_diff.dom_type_diff
ADD CONSTRAINT cons2 CHECK (VALUE::text <> 'pgAdmin4'::text);
COMMENT ON DOMAIN test_schema_diff.dom_type_diff
IS 'Test comment';
ALTER DOMAIN test_schema_diff.dom_cons_diff
SET NOT NULL;
ALTER DOMAIN test_schema_diff.dom_cons_diff
SET DEFAULT 100;
ALTER DOMAIN test_schema_diff.dom_cons_diff
DROP CONSTRAINT cons_tar_only;
ALTER DOMAIN test_schema_diff.dom_cons_diff
DROP CONSTRAINT cons_diff_1;
ALTER DOMAIN test_schema_diff.dom_cons_diff
ADD CONSTRAINT cons_diff_1 CHECK (VALUE <> 50);
COMMENT ON CONSTRAINT cons_diff_1 ON DOMAIN test_schema_diff.dom_cons_diff
IS NULL;
ALTER DOMAIN test_schema_diff.dom_cons_diff
ADD CONSTRAINT cons_src_only CHECK (VALUE <> 25);
CREATE FOREIGN TABLE test_schema_diff.ft_src(
fid bigint NULL,
fname text NULL COLLATE pg_catalog."default"
)
SERVER test_fs_for_foreign_table;
ALTER FOREIGN TABLE test_schema_diff.ft_src
OWNER TO postgres;
ALTER FOREIGN TABLE test_schema_diff.ft_src
ADD CONSTRAINT fcheck CHECK ((fid > 1000)) NO INHERIT;
COMMENT ON FOREIGN TABLE test_schema_diff.ft_src
IS 'Test Comment';
GRANT INSERT ON TABLE test_schema_diff.ft_src TO pg_monitor;
GRANT ALL ON TABLE test_schema_diff.ft_src TO postgres;
DROP FOREIGN TABLE test_schema_diff.ft_tar;
ALTER FOREIGN TABLE test_schema_diff.ft_diff_opt
OPTIONS ( DROP debug);
ALTER FOREIGN TABLE test_schema_diff.ft_diff_opt
OPTIONS ( DROP opt_tar);
ALTER FOREIGN TABLE test_schema_diff.ft_diff_opt
OPTIONS (ADD opt1 'val1');
ALTER FOREIGN TABLE test_schema_diff.ft_diff_opt
OPTIONS (ADD opt_src 'val_src');
ALTER FOREIGN TABLE test_schema_diff.ft_diff_opt
OPTIONS (SET opt2 'val20');
-- WARNING:
-- We have found the difference in foreign server
-- so we need to drop the existing foreign table first and re-create it.
DROP FOREIGN TABLE test_schema_diff.ft_diff_foreign_server;
CREATE FOREIGN TABLE test_schema_diff.ft_diff_foreign_server(
fid bigint NULL,
fname text NULL COLLATE pg_catalog."default"
)
SERVER test_fs_for_foreign_table;
ALTER FOREIGN TABLE test_schema_diff.ft_diff_const
DROP CONSTRAINT fcheck_tar;
ALTER FOREIGN TABLE test_schema_diff.ft_diff_const
ADD CONSTRAINT fcheck_src CHECK ((fid > 50));
ALTER FOREIGN TABLE test_schema_diff.ft_diff_const
DROP CONSTRAINT fcheck1;
ALTER FOREIGN TABLE test_schema_diff.ft_diff_const
ADD CONSTRAINT fcheck1 CHECK ((fid > 1000)) NOT VALID NO INHERIT;
ALTER FOREIGN TABLE test_schema_diff.ft_diff_const
DROP CONSTRAINT fcheck2;
ALTER FOREIGN TABLE test_schema_diff.ft_diff_const
ADD CONSTRAINT fcheck2 CHECK ((fid > 20));
ALTER FOREIGN TABLE test_schema_diff.ft_diff_col
ADD COLUMN fcity character varying(40) NULL COLLATE pg_catalog."POSIX";
ALTER FOREIGN TABLE test_schema_diff.ft_diff_col
ALTER COLUMN fname DROP NOT NULL;
COMMENT ON FOREIGN TABLE test_schema_diff.ft_diff_col
IS 'Test Comment';
REVOKE ALL ON TABLE test_schema_diff.ft_diff_col FROM pg_monitor;
REVOKE ALL ON TABLE test_schema_diff.ft_diff_col FROM postgres;
-- WARNING:
-- We have found the difference in foreign server
-- so we need to drop the existing foreign table first and re-create it.
DROP FOREIGN TABLE test_schema_diff.ft_diff_foreign_server_1;
CREATE FOREIGN TABLE test_schema_diff.ft_diff_foreign_server_1(
fid bigint NULL,
fname text NULL COLLATE pg_catalog."default"
)
SERVER test_fs_for_foreign_table
OPTIONS (opt1 'val1');
ALTER FOREIGN TABLE test_schema_diff.ft_diff_foreign_server_1
ADD CONSTRAINT cs1 CHECK ((fid > 200)) NO INHERIT;
-- Text Search CONFIGURATION: test_schema_diff.fts_con_src
-- DROP TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_src
CREATE TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_src (
PARSER = default
);
COMMENT ON TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_src IS 'Test Comment';
ALTER TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_src ADD MAPPING FOR asciihword WITH german_stem;
ALTER TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_src ADD MAPPING FOR asciiword WITH german_stem;
ALTER TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_src ADD MAPPING FOR email WITH simple;
ALTER TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_src ADD MAPPING FOR file WITH simple;
ALTER TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_src ADD MAPPING FOR float WITH simple;
ALTER TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_src ADD MAPPING FOR host WITH simple;
ALTER TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_src ADD MAPPING FOR hword WITH german_stem;
ALTER TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_src ADD MAPPING FOR hword_asciipart WITH german_stem;
ALTER TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_src ADD MAPPING FOR hword_numpart WITH simple;
ALTER TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_src ADD MAPPING FOR hword_part WITH german_stem;
ALTER TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_src ADD MAPPING FOR int WITH simple;
ALTER TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_src ADD MAPPING FOR numhword WITH simple;
ALTER TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_src ADD MAPPING FOR numword WITH simple;
ALTER TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_src ADD MAPPING FOR sfloat WITH simple;
ALTER TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_src ADD MAPPING FOR uint WITH simple;
ALTER TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_src ADD MAPPING FOR url WITH simple;
ALTER TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_src ADD MAPPING FOR url_path WITH simple;
ALTER TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_src ADD MAPPING FOR version WITH simple;
ALTER TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_src ADD MAPPING FOR word WITH german_stem;
DROP TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_tar ;
ALTER TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_diff
ALTER MAPPING FOR asciiword
WITH german_stem;
ALTER TEXT SEARCH CONFIGURATION test_schema_diff.fts_con_diff
ALTER MAPPING FOR hword
WITH dutch_stem;
-- Text Search Dictionary: test_schema_diff.fts_dict_src
-- DROP TEXT SEARCH DICTIONARY test_schema_diff.fts_dict_src;
CREATE TEXT SEARCH DICTIONARY test_schema_diff.fts_dict_src (
TEMPLATE = simple,
stopwords = english
);
COMMENT ON TEXT SEARCH DICTIONARY test_schema_diff.fts_dict_src
IS 'Test Comment';
DROP TEXT SEARCH DICTIONARY test_schema_diff.fts_dict_tar ;
ALTER TEXT SEARCH DICTIONARY test_schema_diff.fts_dict_diff
(stopwords=english);
COMMENT ON TEXT SEARCH DICTIONARY test_schema_diff.fts_dict_diff
IS 'Test Comment';
-- Text Search Parser: test_schema_diff.fts_par_src
-- DROP TEXT SEARCH PARSER test_schema_diff.fts_par_src
CREATE TEXT SEARCH PARSER test_schema_diff.fts_par_src (
START = prsd_start,
GETTOKEN = prsd_nexttoken,
END = prsd_end,
LEXTYPES = prsd_lextype
);
COMMENT ON TEXT SEARCH PARSER test_schema_diff.fts_par_src IS 'Test Comment';
DROP TEXT SEARCH PARSER test_schema_diff.fts_par_tar ;
-- WARNING:
-- We have found the difference in either of START or GETTOKEN or END or
-- LEXTYPES or HEADLINE, so we need to drop the existing parser first
-- and re-create it.
DROP TEXT SEARCH PARSER test_schema_diff.fts_par_diff;
CREATE TEXT SEARCH PARSER test_schema_diff.fts_par_diff (
START = prsd_start,
GETTOKEN = prsd_nexttoken,
END = prsd_end,
LEXTYPES = prsd_lextype
);
COMMENT ON TEXT SEARCH PARSER test_schema_diff.fts_par_diff
IS 'Test Comment';
-- Text Search Template: test_schema_diff.fts_templ_src
-- DROP TEXT SEARCH TEMPLATE test_schema_diff.fts_templ_src
CREATE TEXT SEARCH TEMPLATE test_schema_diff.fts_templ_src (
INIT = dispell_init,
LEXIZE = dispell_lexize
);
COMMENT ON TEXT SEARCH TEMPLATE test_schema_diff.fts_templ_src IS 'Test Comment';
DROP TEXT SEARCH TEMPLATE test_schema_diff.fts_templ_tar ;
-- WARNING:
-- We have found the difference in either of INIT or LEXIZE,
-- so we need to drop the existing template first and re-create it.
DROP TEXT SEARCH TEMPLATE test_schema_diff.fts_templ_diff;
CREATE TEXT SEARCH TEMPLATE test_schema_diff.fts_templ_diff (
INIT = dispell_init,
LEXIZE = dispell_lexize
);
COMMENT ON TEXT SEARCH TEMPLATE test_schema_diff.fts_templ_diff
IS 'Test Comment';
CREATE OR REPLACE FUNCTION test_schema_diff.typ_range_src(
text,
text,
text)
RETURNS test_schema_diff.typ_range_src
LANGUAGE 'internal'
COST 1
IMMUTABLE PARALLEL SAFE
AS $BODY$
range_constructor3
$BODY$;
ALTER FUNCTION test_schema_diff.typ_range_src(text, text, text)
OWNER TO postgres;
CREATE OR REPLACE FUNCTION test_schema_diff.typ_range_comp_diff(
text,
text,
text)
RETURNS test_schema_diff.typ_range_comp_diff
LANGUAGE 'internal'
COST 1
IMMUTABLE PARALLEL SAFE
AS $BODY$
range_constructor3
$BODY$;
ALTER FUNCTION test_schema_diff.typ_range_comp_diff(text, text, text)
OWNER TO postgres;
CREATE OR REPLACE FUNCTION test_schema_diff.typ_range_subtype_diff(
character,
character)
RETURNS test_schema_diff.typ_range_subtype_diff
LANGUAGE 'internal'
COST 1
IMMUTABLE PARALLEL SAFE
AS $BODY$
range_constructor2
$BODY$;
ALTER FUNCTION test_schema_diff.typ_range_subtype_diff(character, character)
OWNER TO postgres;
CREATE OR REPLACE FUNCTION test_schema_diff.typ_range_src(
text,
text)
RETURNS test_schema_diff.typ_range_src
LANGUAGE 'internal'
COST 1
IMMUTABLE PARALLEL SAFE
AS $BODY$
range_constructor2
$BODY$;
ALTER FUNCTION test_schema_diff.typ_range_src(text, text)
OWNER TO postgres;
CREATE OR REPLACE FUNCTION test_schema_diff.typ_range_enum_diff(
text,
text,
text)
RETURNS test_schema_diff.typ_range_enum_diff
LANGUAGE 'internal'
COST 1
IMMUTABLE PARALLEL SAFE
AS $BODY$
range_constructor3
$BODY$;
ALTER FUNCTION test_schema_diff.typ_range_enum_diff(text, text, text)
OWNER TO postgres;
CREATE OR REPLACE FUNCTION test_schema_diff.typ_range_subtype_diff(
character,
character,
text)
RETURNS test_schema_diff.typ_range_subtype_diff
LANGUAGE 'internal'
COST 1
IMMUTABLE PARALLEL SAFE
AS $BODY$
range_constructor3
$BODY$;
ALTER FUNCTION test_schema_diff.typ_range_subtype_diff(character, character, text)
OWNER TO postgres;
CREATE OR REPLACE FUNCTION test_schema_diff.typ_range_comp_diff(
text,
text)
RETURNS test_schema_diff.typ_range_comp_diff
LANGUAGE 'internal'
COST 1
IMMUTABLE PARALLEL SAFE
AS $BODY$
range_constructor2
$BODY$;
ALTER FUNCTION test_schema_diff.typ_range_comp_diff(text, text)
OWNER TO postgres;
CREATE OR REPLACE FUNCTION test_schema_diff.typ_range_enum_diff(
text,
text)
RETURNS test_schema_diff.typ_range_enum_diff
LANGUAGE 'internal'
COST 1
IMMUTABLE PARALLEL SAFE
AS $BODY$
range_constructor2
$BODY$;
ALTER FUNCTION test_schema_diff.typ_range_enum_diff(text, text)
OWNER TO postgres;
DROP FUNCTION test_schema_diff.typ_enum_range_diff(text, text);
DROP FUNCTION test_schema_diff.typ_range_tar(text, text, text);
DROP FUNCTION test_schema_diff.typ_range_subtype_diff(boolean, boolean);
DROP FUNCTION test_schema_diff.typ_comp_range_diff(text, text);
DROP FUNCTION test_schema_diff.typ_enum_range_diff(text, text, text);
DROP FUNCTION test_schema_diff.typ_range_subtype_diff(boolean, boolean, text);
DROP FUNCTION test_schema_diff.typ_range_tar(text, text);
DROP FUNCTION test_schema_diff.typ_comp_range_diff(text, text, text);
CREATE SEQUENCE test_schema_diff.seq_src
CYCLE
INCREMENT 1
START 1
MINVALUE 1
MAXVALUE 3
CACHE 6;
ALTER SEQUENCE test_schema_diff.seq_src
OWNER TO postgres;
COMMENT ON SEQUENCE test_schema_diff.seq_src
IS 'Test Comment';
GRANT ALL ON SEQUENCE test_schema_diff.seq_src TO PUBLIC;
GRANT ALL ON SEQUENCE test_schema_diff.seq_src TO postgres;
DROP SEQUENCE test_schema_diff.seq_tar;
ALTER SEQUENCE test_schema_diff.seq_start_diff
START 3
MINVALUE 3;
ALTER SEQUENCE test_schema_diff.seq_diff
INCREMENT 3
MAXVALUE 100
CACHE 2
CYCLE;
COMMENT ON SEQUENCE test_schema_diff.seq_diff_comment_acl
IS 'Test Comment';
GRANT ALL ON SEQUENCE test_schema_diff.seq_diff_comment_acl TO PUBLIC;
GRANT ALL ON SEQUENCE test_schema_diff.seq_diff_comment_acl TO postgres;
COMMENT ON SEQUENCE test_schema_diff.seq_diff_comment_acl_remove
IS '';
REVOKE ALL ON SEQUENCE test_schema_diff.seq_diff_comment_acl_remove FROM PUBLIC;
REVOKE ALL ON SEQUENCE test_schema_diff.seq_diff_comment_acl_remove FROM postgres;
CREATE TABLE test_schema_diff.table_for_partition
(
col1 bigint NOT NULL
) PARTITION BY RANGE (col1)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE test_schema_diff.table_for_partition
OWNER to postgres;
CREATE TABLE test_schema_diff.part1 PARTITION OF test_schema_diff.table_for_partition
FOR VALUES FROM ('1') TO ('23');
ALTER TABLE test_schema_diff.part1
OWNER to postgres;
DROP TABLE test_schema_diff.table_for_publication_in_target CASCADE;
DROP TABLE test_schema_diff.table_for_del CASCADE;
DROP TABLE test_schema_diff.table_for_foreign_key CASCADE;
ALTER TABLE test_schema_diff.table_for_partition_1 DETACH PARTITION test_schema_diff.part3;
CREATE TABLE test_schema_diff.temp_partitioned_289277 (
LIKE test_schema_diff.table_for_partition_1 INCLUDING ALL
) PARTITION BY RANGE (col1);
CREATE TABLE test_schema_diff.partition_8713859 PARTITION OF test_schema_diff.temp_partitioned_289277
FOR VALUES FROM ('1') TO ('10');
CREATE TABLE test_schema_diff.partition_7663013 PARTITION OF test_schema_diff.temp_partitioned_289277
FOR VALUES FROM ('11') TO ('20');
-- Create a default partition to prevent the data loss.
-- It helps when none of the partitions of a relation
-- matches the inserted data.
CREATE TABLE IF NOT EXISTS test_schema_diff.table_for_partition_1_default PARTITION OF test_schema_diff.temp_partitioned_289277 DEFAULT;
INSERT INTO test_schema_diff.temp_partitioned_289277(
col1)
SELECT col1 FROM test_schema_diff.table_for_partition_1;
DROP TABLE IF EXISTS test_schema_diff.part3;
ALTER TABLE test_schema_diff.partition_8713859
RENAME TO part3;
DROP TABLE IF EXISTS test_schema_diff.part4;
ALTER TABLE test_schema_diff.partition_7663013
RENAME TO part4;
DROP TABLE test_schema_diff.table_for_partition_1;
ALTER TABLE test_schema_diff.temp_partitioned_289277
RENAME TO table_for_partition_1;
-- WARNING:
-- The SQL statement below would normally be used to alter the datatype for the col1 column, however,
-- the current datatype cannot be cast to the target datatype so this conversion cannot be made automatically.
-- ALTER TABLE test_schema_diff.table_for_primary_key
-- ALTER COLUMN col1 TYPE integer;
ALTER TABLE test_schema_diff.table_for_primary_key DROP CONSTRAINT table_for_primary_key_pkey;
ALTER TABLE test_schema_diff.table_for_primary_key
ADD CONSTRAINT table_for_primary_key_pkey PRIMARY KEY (col1, col2);
CREATE INDEX index_source
ON test_schema_diff.table_for_index USING btree
(col2 COLLATE pg_catalog."default" text_pattern_ops ASC NULLS LAST)
TABLESPACE pg_default;
DROP INDEX test_schema_diff.index2;
DROP INDEX test_schema_diff.index1;
CREATE INDEX index1
ON test_schema_diff.table_for_index USING btree
(col2 COLLATE pg_catalog."default" varchar_pattern_ops ASC NULLS LAST)
TABLESPACE pg_default;
-- WARNING:
-- The SQL statement below would normally be used to alter the datatype for the col1 column, however,
-- the current datatype cannot be cast to the target datatype so this conversion cannot be made automatically.
-- ALTER TABLE test_schema_diff.table_for_constraints
-- ALTER COLUMN col1 TYPE integer;
ALTER TABLE test_schema_diff.table_for_constraints
ADD CONSTRAINT table_for_constraints_pkey PRIMARY KEY (col1);
ALTER TABLE test_schema_diff.table_for_constraints
ADD CONSTRAINT "unique" UNIQUE (col1);
COMMENT ON CONSTRAINT "unique" ON test_schema_diff.table_for_constraints
IS 'cmnt';
ALTER TABLE test_schema_diff.table_for_constraints DROP CONSTRAINT check_con;
ALTER TABLE test_schema_diff.table_for_constraints
ADD CONSTRAINT check_con CHECK (col1 > 10)
NOT VALID;
COMMENT ON CONSTRAINT check_con ON test_schema_diff.table_for_constraints
IS 'coment';
ALTER INDEX test_schema_diff."Exclusion"
SET (FILLFACTOR=12);
ALTER TABLE test_schema_diff.table_for_column DROP COLUMN col4;
-- WARNING:
-- The SQL statement below would normally be used to alter the datatype for the col1 column, however,
-- the current datatype cannot be cast to the target datatype so this conversion cannot be made automatically.
-- ALTER TABLE test_schema_diff.table_for_column
-- ALTER COLUMN col1 TYPE bigint;
ALTER TABLE test_schema_diff.table_for_column
ALTER COLUMN col1 SET NOT NULL;
ALTER TABLE test_schema_diff.table_for_column
ALTER COLUMN col2 TYPE text COLLATE pg_catalog."default";
ALTER TABLE test_schema_diff.table_for_column
ALTER COLUMN col2 SET STORAGE EXTENDED;
ALTER TABLE test_schema_diff.table_for_column
ADD COLUMN col3 text COLLATE pg_catalog."default";
ALTER TABLE test_schema_diff.table_for_column
ADD CONSTRAINT table_for_column_pkey PRIMARY KEY (col1);
-- WARNING:
-- The SQL statement below would normally be used to alter the datatype for the col1 column, however,
-- the current datatype cannot be cast to the target datatype so this conversion cannot be made automatically.
-- ALTER TABLE test_schema_diff.table_for_rule
-- ALTER COLUMN col1 TYPE bigint;
DROP RULE rule3 ON test_schema_diff.table_for_rule ;
CREATE OR REPLACE RULE rule2 AS
ON INSERT TO test_schema_diff.table_for_rule
DO NOTHING;
-- Type: typ_enum_src
-- DROP TYPE test_schema_diff.typ_enum_src;
CREATE TYPE test_schema_diff.typ_enum_src AS ENUM
('test_enum');
ALTER TYPE test_schema_diff.typ_enum_src
OWNER TO postgres;
GRANT USAGE ON TYPE test_schema_diff.typ_enum_src TO PUBLIC;
GRANT USAGE ON TYPE test_schema_diff.typ_enum_src TO pg_monitor WITH GRANT OPTION;
GRANT USAGE ON TYPE test_schema_diff.typ_enum_src TO postgres;
-- Type: typ_comp_src
-- DROP TYPE test_schema_diff.typ_comp_src;
CREATE TYPE test_schema_diff.typ_comp_src AS
(
m1 bit(5),
m2 text COLLATE pg_catalog."POSIX"
);
ALTER TYPE test_schema_diff.typ_comp_src
OWNER TO postgres;
-- Type: typ_range_src
-- DROP TYPE test_schema_diff.typ_range_src;
CREATE TYPE test_schema_diff.typ_range_src AS RANGE
(
SUBTYPE=text,
COLLATION = pg_catalog."POSIX",
SUBTYPE_OPCLASS = text_ops
);
ALTER TYPE test_schema_diff.typ_range_src
OWNER TO postgres;
-- Type: typ_shell_src
-- DROP TYPE test_schema_diff.typ_shell_src;
CREATE TYPE test_schema_diff.typ_shell_src;
ALTER TYPE test_schema_diff.typ_shell_src
OWNER TO postgres;
DROP TYPE test_schema_diff.typ_shell_tar;
DROP TYPE test_schema_diff.typ_comp_tar;
DROP TYPE test_schema_diff.typ_enum_tar;
DROP TYPE test_schema_diff.typ_range_tar;
-- WARNING:
-- We have found the difference in either of Type or SubType or Collation,
-- so we need to drop the existing type first and re-create it.
DROP TYPE test_schema_diff.typ_range_enum_diff CASCADE;
CREATE TYPE test_schema_diff.typ_range_enum_diff AS RANGE
(
SUBTYPE=text
);
-- WARNING:
-- We have found the difference in either of Type or SubType or Collation,
-- so we need to drop the existing type first and re-create it.
DROP TYPE test_schema_diff.typ_enum_comp_diff CASCADE;
CREATE TYPE test_schema_diff.typ_enum_comp_diff AS ENUM
('test_enum', 'test_enum_1');
ALTER TYPE test_schema_diff.typ_comp_diff_no_column
DROP ATTRIBUTE a;
ALTER TYPE test_schema_diff.typ_comp_diff_no_column
DROP ATTRIBUTE b;
-- WARNING:
-- We have found the difference in either of Type or SubType or Collation,
-- so we need to drop the existing type first and re-create it.
DROP TYPE test_schema_diff.typ_comp_range_diff CASCADE;
CREATE TYPE test_schema_diff.typ_comp_range_diff AS
(
m1 bigint,
m2 text[] COLLATE pg_catalog."POSIX"
);
-- WARNING:
-- We have found the difference in either of Type or SubType or Collation,
-- so we need to drop the existing type first and re-create it.
DROP TYPE test_schema_diff.typ_comp_enum_diff CASCADE;
CREATE TYPE test_schema_diff.typ_comp_enum_diff AS
(
m1 bigint,
m2 text[] COLLATE pg_catalog."POSIX"
);
ALTER TYPE test_schema_diff.typ_comp_enum_diff
OWNER TO "steve-chavez";
-- WARNING:
-- We have found the difference in either of Type or SubType or Collation,
-- so we need to drop the existing type first and re-create it.
DROP TYPE test_schema_diff.typ_range_comp_diff CASCADE;
CREATE TYPE test_schema_diff.typ_range_comp_diff AS RANGE
(
SUBTYPE=text
);
COMMENT ON TYPE test_schema_diff.typ_comp_diff
IS 'Test Comment';
ALTER TYPE test_schema_diff.typ_comp_diff
DROP ATTRIBUTE m2;
ALTER TYPE test_schema_diff.typ_comp_diff
ADD ATTRIBUTE m3 character varying(30) COLLATE pg_catalog."C";
ALTER TYPE test_schema_diff.typ_comp_diff
ALTER ATTRIBUTE m1 SET DATA TYPE numeric(5, 2);
GRANT USAGE ON TYPE test_schema_diff.typ_comp_diff TO PUBLIC;
GRANT USAGE ON TYPE test_schema_diff.typ_comp_diff TO pg_monitor WITH GRANT OPTION;
GRANT USAGE ON TYPE test_schema_diff.typ_comp_diff TO postgres;
-- WARNING:
-- We have found the difference in either of Type or SubType or Collation,
-- so we need to drop the existing type first and re-create it.
DROP TYPE test_schema_diff.typ_range_subtype_diff CASCADE;
CREATE TYPE test_schema_diff.typ_range_subtype_diff AS RANGE
(
SUBTYPE=bpchar,
COLLATION = pg_catalog."POSIX",
SUBTYPE_OPCLASS = bpchar_ops
);
COMMENT ON TYPE test_schema_diff.typ_enum_diff
IS 'Test Comment';
-- WARNING:
-- We have found the difference in either of Type or SubType or Collation,
-- so we need to drop the existing type first and re-create it.
DROP TYPE test_schema_diff.typ_enum_range_diff CASCADE;
CREATE TYPE test_schema_diff.typ_enum_range_diff AS ENUM
('test_enum', 'test_enum_1');
-- WARNING:
-- We have found the difference in either of Type or SubType or Collation,
-- so we need to drop the existing type first and re-create it.
DROP TYPE test_schema_diff.typ_range_col_diff CASCADE;
CREATE TYPE test_schema_diff.typ_range_col_diff AS RANGE
(
SUBTYPE=text,
COLLATION = pg_catalog."C"
);
ALTER TYPE test_schema_diff.typ_range_col_diff
OWNER TO pg_monitor;
COMMENT ON TYPE test_schema_diff.typ_range_col_diff
IS 'Test Comment';
GRANT USAGE ON TYPE test_schema_diff.typ_range_col_diff TO PUBLIC;
GRANT USAGE ON TYPE test_schema_diff.typ_range_col_diff TO pg_monitor WITH GRANT OPTION;
ALTER TYPE test_schema_diff.typ_shell_diff
OWNER TO postgres;
COMMENT ON TYPE test_schema_diff.typ_shell_diff
IS 'Test Comment';
ALTER VIEW test_schema_diff."test view f"
SET (security_barrier=false);
ALTER TABLE test_schema_diff."test view f"
OWNER TO postgres;
DROP MATERIALIZED VIEW test_schema_diff."MView" ;
END;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment