\pset null _null_
\pset format unaligned
SET client_min_messages = warning;
SET ROLE postgres;
------
CREATE TABLE options (
       name text primary key,
       value text not null,
       regtype regtype not null default('text'::regtype)
);
ALTER TABLE options SET ( parallel_workers = 2 );
select ddlx_script('options');
ddlx_script
BEGIN;

/*
DROP TABLE options;
*/

-- Type: TABLE ; Name: options; Owner: postgres

CREATE TABLE options (
    name text NOT NULL,
    value text NOT NULL,
    regtype regtype NOT NULL
);

COMMENT ON TABLE options IS NULL;
ALTER TABLE options OWNER TO postgres;

ALTER TABLE options SET (parallel_workers='2');

ALTER TABLE options ALTER regtype SET DEFAULT 'text'::regtype;

ALTER TABLE options ADD CONSTRAINT options_pkey
  PRIMARY KEY (name);


END;

(1 row)
------
CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
CREATE INDEX ON measurement_y2006m02 (logdate);
CREATE INDEX ON measurement_y2006m03 (logdate);
select ddlx_script('measurement');
ddlx_script
BEGIN;

/*
DROP TABLE measurement;
*/

-- Type: TABLE ; Name: measurement; Owner: postgres

CREATE TABLE measurement (
    city_id integer NOT NULL,
    logdate date NOT NULL,
    peaktemp integer,
    unitsales integer
)
  PARTITION BY RANGE (logdate);

COMMENT ON TABLE measurement IS NULL;
ALTER TABLE measurement OWNER TO postgres;


END;

(1 row)
------
CREATE TABLE customers(cust_id bigint NOT NULL,cust_name varchar(32) NOT NULL,cust_address text,
cust_country text) PARTITION BY LIST(cust_country);
CREATE TABLE customer_ind PARTITION OF customers FOR VALUES IN ('ind');
CREATE TABLE customer_jap PARTITION OF customers FOR VALUES IN ('jap');
INSERT INTO customers VALUES (2039,'Puja','Hyderabad','ind');
SELECT tableoid::regclass,* FROM customers;
tableoid|cust_id|cust_name|cust_address|cust_country
customer_ind|2039|Puja|Hyderabad|ind
(1 row)
SELECT * FROM customer_ind;
cust_id|cust_name|cust_address|cust_country
2039|Puja|Hyderabad|ind
(1 row)
UPDATE customers SET cust_country ='jap' WHERE cust_id=2039;
ERROR:  new row for relation "customer_ind" violates partition constraint
DETAIL:  Failing row contains (2039, Puja, Hyderabad, jap).
SELECT * FROM customer_jap;
cust_id|cust_name|cust_address|cust_country
(0 rows)
select ddlx_script('customers');
ddlx_script
BEGIN;

/*
DROP TABLE customers;
*/

-- Type: TABLE ; Name: customers; Owner: postgres

CREATE TABLE customers (
    cust_id bigint NOT NULL,
    cust_name character varying(32) NOT NULL,
    cust_address text,
    cust_country text
)
  PARTITION BY LIST (cust_country);

COMMENT ON TABLE customers IS NULL;
ALTER TABLE customers OWNER TO postgres;


END;

(1 row)
select ddlx_script('customer_jap'); 
ddlx_script
BEGIN;

/*
DROP TABLE customer_jap;
*/

-- Type: TABLE ; Name: customer_jap; Owner: postgres

CREATE TABLE customer_jap PARTITION OF customers
  FOR VALUES IN ('jap');

COMMENT ON TABLE customer_jap IS NULL;
ALTER TABLE customer_jap OWNER TO postgres;


END;

(1 row)
create table log (
 ts timestamp,
 code int,
 t text,
 a boolean,
 d varchar collate "C",
 j json
)
partition by range (code,date_trunc('month',ts),t collate "C",a,((code%100)/100));
select ddlx_script('log');
ddlx_script
BEGIN;

/*
DROP TABLE log;
*/

-- Type: TABLE ; Name: log; Owner: postgres

CREATE TABLE log (
    ts timestamp without time zone,
    code integer,
    t text,
    a boolean,
    d character varying COLLATE "C",
    j json
)
  PARTITION BY RANGE (code, date_trunc('month'::text, ts), t COLLATE "C", a, (((code % 100) / 100)));

COMMENT ON TABLE log IS NULL;
ALTER TABLE log OWNER TO postgres;


END;

(1 row)
-- statistics
CREATE TABLE test_stat (
    a   int primary key,
    b   int
);
CREATE STATISTICS test_stat1 (dependencies) ON a, b FROM test_stat;
\x
select ddlx_create(oid),ddlx_drop(oid) from pg_statistic_ext where stxname='test_stat1';
ddlx_create|CREATE STATISTICS public.test_stat1 (dependencies) ON a, b FROM test_stat;
ALTER STATISTICS test_stat1 OWNER TO postgres;

ddlx_drop|DROP STATISTICS test_stat1;

\x
select ddlx_script('test_stat');
ddlx_script
BEGIN;

/*
DROP TABLE test_stat;
*/

-- Type: TABLE ; Name: test_stat; Owner: postgres

CREATE TABLE test_stat (
    a integer NOT NULL,
    b integer
);

COMMENT ON TABLE test_stat IS NULL;
ALTER TABLE test_stat OWNER TO postgres;

ALTER TABLE test_stat ADD CONSTRAINT test_stat_pkey
  PRIMARY KEY (a);

CREATE STATISTICS public.test_stat1 (dependencies) ON a, b FROM test_stat;
ALTER STATISTICS test_stat1 OWNER TO postgres;


END;

(1 row)
-- publication
begin;
create publication ddlx_test_pub
   for table customer_ind, customer_jap
  with ( publish='insert,delete' );
select ddlx_create(oid),ddlx_drop(oid) from pg_publication where pubname='ddlx_test_pub';
ddlx_create|ddlx_drop
CREATE PUBLICATION ddlx_test_pub
  FOR TABLE customer_ind, customer_jap
  WITH ( publish='insert,delete' );
ALTER PUBLICATION ddlx_test_pub OWNER TO postgres;
COMMENT ON PUBLICATION ddlx_test_pub IS NULL;
|DROP PUBLICATION ddlx_test_pub;

(1 row)
commit;
-- drop publication ddlx_test_pub;
create subscription ddlx_test_sub
  connection 'dbname=contrib_regression'
  publication ddlx_test_pub
  with ( connect=false );
WARNING:  tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
select ddlx_create(oid),ddlx_drop(oid) from pg_subscription where subname='ddlx_test_sub';
ddlx_create|ddlx_drop
CREATE SUBSCRIPTION ddlx_test_sub
  CONNECTION 'dbname=contrib_regression'
  PUBLICATION ddlx_test_pub
  WITH (
    connect='false'
    slot_name='ddlx_test_sub'
    synchronous_commit='off' );
ALTER SUBSCRIPTION ddlx_test_sub OWNER TO postgres;
COMMENT ON SUBSCRIPTION ddlx_test_sub IS NULL;
|DROP SUBSCRIPTION ddlx_test_sub;

(1 row)
alter subscription ddlx_test_sub set ( slot_name=none );
select ddlx_create(oid),ddlx_drop(oid) from pg_subscription where subname='ddlx_test_sub';
ddlx_create|ddlx_drop
CREATE SUBSCRIPTION ddlx_test_sub
  CONNECTION 'dbname=contrib_regression'
  PUBLICATION ddlx_test_pub
  WITH (
    connect='false'
    synchronous_commit='off' );
ALTER SUBSCRIPTION ddlx_test_sub OWNER TO postgres;
COMMENT ON SUBSCRIPTION ddlx_test_sub IS NULL;
|DROP SUBSCRIPTION ddlx_test_sub;

(1 row)
drop subscription ddlx_test_sub;
-- generated as identity
create table tab_generated (
       a integer generated by default as identity,
       b integer generated always as identity,
       d integer default 42,
       e serial,
       f integer
);
select ddlx_script('tab_generated');
ddlx_script
BEGIN;

/*
ALTER TABLE tab_generated ALTER e DROP DEFAULT;

DROP TABLE tab_generated;
*/

-- Type: TABLE ; Name: tab_generated; Owner: postgres

CREATE TABLE tab_generated (
    a integer GENERATED BY DEFAULT AS IDENTITY,
    b integer GENERATED ALWAYS AS IDENTITY,
    d integer,
    e integer NOT NULL,
    f integer
);

COMMENT ON TABLE tab_generated IS NULL;
ALTER TABLE tab_generated OWNER TO postgres;

ALTER TABLE tab_generated ALTER d SET DEFAULT 42;

ALTER SEQUENCE tab_generated_e_seq OWNED BY tab_generated.e;


-- DEPENDANTS

ALTER TABLE tab_generated ALTER e SET DEFAULT nextval('tab_generated_e_seq'::regclass);

END;

(1 row)