#587 [PostgreSQL] Export user types

[Alasdair]

There are multiple issues with exported SQL for a PostgreSQL database using adminer noticed when attempting to refeed (sample export further down).

1. User types - These are not created
2. Column Defaults - A non-nullable text column with a default of an empty string is not exported correctly
3. Ordering - Tables are exported out of order so FK relationships are invalid

Sample SQL generated from pg_dump:

CREATE TYPE test_status AS ENUM (
    'New',
    'Reviewed',
    'Resolved'
);

CREATE TABLE test (
    id integer NOT NULL,
    test text DEFAULT ''::text NOT NULL,
    created_by smallint NOT NULL,
    status test_status NOT NULL
);

CREATE SEQUENCE test_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

CREATE TABLE test_user (
    id integer NOT NULL,
    email text NOT NULL
);

CREATE SEQUENCE test_user_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE ONLY test ALTER COLUMN id SET DEFAULT nextval('test_id_seq'::regclass);

ALTER TABLE ONLY test_user ALTER COLUMN id SET DEFAULT nextval('test_user_id_seq'::regclass);

ALTER TABLE ONLY test
    ADD CONSTRAINT pk_test_id PRIMARY KEY (id);

ALTER TABLE ONLY test_user
    ADD CONSTRAINT pk_test_user_id PRIMARY KEY (id);

CREATE INDEX ix_test_created_by ON test USING btree (created_by);

ALTER TABLE ONLY test
    ADD CONSTRAINT test_created_by_fkey FOREIGN KEY (created_by) REFERENCES test_user(id) ON UPDATE CASCADE ON DELETE RESTRICT;

Resultant DB exported using adminer:

DROP TABLE IF EXISTS "test";
CREATE SEQUENCE test_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1;

CREATE TABLE "public"."test" (
    "id" integer DEFAULT nextval('test_id_seq') NOT NULL,
    "test" text DEFAULT  NOT NULL,
    "created_by" smallint NOT NULL,
    "status" test_status NOT NULL,
    CONSTRAINT "pk_test_id" PRIMARY KEY ("id"),
    CONSTRAINT "test_created_by_fkey" FOREIGN KEY (created_by) REFERENCES test_user(id) ON UPDATE CASCADE ON DELETE RESTRICT NOT DEFERRABLE
) WITH (oids = false);

CREATE INDEX "ix_test_created_by" ON "public"."test" USING btree ("created_by");

DROP TABLE IF EXISTS "test_user";
CREATE SEQUENCE test_user_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1;

CREATE TABLE "public"."test_user" (
    "id" integer DEFAULT nextval('test_user_id_seq') NOT NULL,
    "email" text NOT NULL,
    CONSTRAINT "pk_test_user_id" PRIMARY KEY ("id")
) WITH (oids = false);

We can see the test.testmissing the empty string as the column is defined as:

    "test" text DEFAULT  NOT NULL,

The adminer dump has no CREATE TYPE statement and due to the FK definition being embedded in the CREATE TABLE statement the tables are not created in the correct order thus preventing the table creation.

[Jakub Vrána]

There's no command to get a SQL definition of the user type which is why Adminer doesn't export them. The other problems are now fixed.

[Jakub Vrána]

Export of enums: d23a0eb7.