Q: Fetching table DDL in Postgres

I have a table that was created by this script:

	Code:
	CREATE TABLE "bd"."day_of_week" ( "tid_day_of_week" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY , "name" text , CONSTRAINT "pk_day_of_week" PRIMARY KEY ( "tid_day_of_week" ), CONSTRAINT "uk_day_of_week$name" UNIQUE ( "name" ) ); COMMENT ON TABLE "bd"."day_of_week" IS 'A particular week day (from Monday to Sunday).'; COMMENT ON COLUMN "bd"."day_of_week"."name" IS 'Name of DAY of WEEK.';

However, when I request the DDL for the table, this is what I get:

Code:

SET SCHEMA 'bd';

CREATE TABLE bd.day_of_week2 (
tid_day_of_week integer NOT NULL,
name text
);

ALTER TABLE bd.day_of_week2 OWNER TO dev_gem;

--
-- Name: TABLE day_of_week2; Type: COMMENT; Schema: bd; Owner: dev_gem
--

COMMENT ON TABLE bd.day_of_week2 IS 'A particular week day (from Monday to Sunday).';

--
-- Name: COLUMN day_of_week2.name; Type: COMMENT; Schema: bd; Owner: dev_gem
--

COMMENT ON COLUMN bd.day_of_week2.name IS 'Name of DAY of WEEK.';

--
-- Name: day_of_week2_tid_day_of_week_seq; Type: SEQUENCE; Schema: bd; Owner: dev_gem
--

ALTER TABLE bd.day_of_week2 ALTER COLUMN tid_day_of_week ADD GENERATED BY DEFAULT AS IDENTITY (
SEQUENCE NAME bd.day_of_week2_tid_day_of_week_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);

--
-- Name: day_of_week2 pk_day_of_week2; Type: CONSTRAINT; Schema: bd; Owner: dev_gem
--

ALTER TABLE ONLY bd.day_of_week2
ADD CONSTRAINT pk_day_of_week2 PRIMARY KEY (tid_day_of_week);

--
-- Name: day_of_week2 uk_day_of_week$name2; Type: CONSTRAINT; Schema: bd; Owner: dev_gem
--

ALTER TABLE ONLY bd.day_of_week2
ADD CONSTRAINT "uk_day_of_week$name2" UNIQUE (name);

The original was 8 lines. Now it is 52. All the constraints were moved outside of the CREATE TABLE statement and tons of empty lines and comments were added, which aren't really that helpful, as the statements the comments were added are pretty much self-explanatory. The identity definition for the primary key column is also moved outside the CREATE TABLE statement, and now instead of using the defaults, it uses constants. For most parts, those constants aren't much of a problem (those are still the default values, even if given by constants), but the sequence name can be a ticking time bomb just laying dormant and waiting for that table in the deployment script to be renamed.

Is there a setting/config/trick to turn this behavior off?

Is that Postgres, if I'm guessing correctly?

Yes, as it was stated in the topic title.

But wait, it gets even weirder. If I right-click a database in the Database Explorer, click Script DDL..., select a few schemas, and choose to save the tables in separate files, I get two files for each table. For the table in the above example, I got "test_db.bd.day_of_week.sql" and "test_db.bd.day_of_week_tid_day_of_week_seq.sql". The first file contains the CREATE TABLE statement and the already mentioned comments, separated constraints and identity (all commented to death), and the second file contains a CREATE SEQUENCE with the same sequence name as the ALTER TABLE ... ADD ... IDENTITY has. It's redundant and I'm not even sure both can be run without checking for the existence of that sequence first.

Thank you for clarifying. The DDL is generated by Postgres. We take advantage of the native facilities for reverse engineering the DDL. You will get the exact same DDL code if you use pg_dump or other tools, the code is compatible.
The first table features GENERATED BY DEFAULT AS IDENTITY which internally makes Postgre create a sequence providing unique values to the table, without the sequence that table won't work. That's why you see and get two files, one for the sequence, and one for the table. The original DDL used to create the table is not stored anywhere and is not available. There is no setting to alter behavior of the native code generation and make it generate semantically different code.

P.S. You will find the same behavior (not syntax) with other databases that provide native DDL generation APIs (SQL Server, Oracle, Snowflake, etc...). For those that don't, like MariaDB, we generate their code using Schema Compare rules, which you can customize to a great degree. There are a few exceptions to the above rule, for example, view and procedures definitions may or may not have the original DDL stored, so in some cases We can retrieved it from the system catalog, and in some case we cannot or don't want it. In some tools we always reverse-engineer DDL code using Schema Compare rules because native DDL doesn't suit tool's needs, for instance, when we need to generate a modified version for schema cloning or other purposes.