Re: Two academic questions
| От | Дмитрий Иванов |
|---|---|
| Тема | Re: Two academic questions |
| Дата | |
| Msg-id | CAPL5KHpQPe=P3YGdftksv55BZJ=KZeESx6TDAy6Fzszj+fQrtQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Two academic questions ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Ответы |
Re: Two academic questions
("David G. Johnston" <david.g.johnston@gmail.com>)
|
| Список | pgsql-general |
ср, 2 февр. 2022 г. в 09:19, David G. Johnston <david.g.johnston@gmail.com>:
On Tue, Feb 1, 2022 at 8:15 PM Дмитрий Иванов <firstdismay@gmail.com> wrote:Why are COLUMN GENERATED ALWAYS AS called "methods" and singled out as a separate field type?This sounds like you are describing something written. Can you provide a link to where that is?Is there a common name in the community for the approach in which the data schema is presented as data?
It is possible to give a link, but it would require authorization:
Here are the contents of the topic:
====================
Actual code include ALWAYS AS column:
CREATE TABLE bpd.schedules_calendar
(
id bigint NOT NULL DEFAULT nextval('bpd.work_calendar_id_seq'::regclass),
work_date date NOT NULL,
work_year integer NOT NULL GENERATED ALWAYS AS (date_part('year'::text, work_date)) STORED,
work_month integer NOT NULL GENERATED ALWAYS AS (date_part('month'::text, work_date)) STORED,
work_day integer NOT NULL GENERATED ALWAYS AS (date_part('day'::text, work_date)) STORED,
day_type bpd.day_type NOT NULL,
name_holiday character varying(100) COLLATE pg_catalog."default" NOT NULL DEFAULT 'будний день'::character varying,
week40_day interval NOT NULL,
week40_month interval NOT NULL,
week36_day interval NOT NULL,
week36_month interval NOT NULL,
week35_day interval NOT NULL,
week35_month interval NOT NULL,
week24_day interval NOT NULL,
week24_month interval NOT NULL,
CONSTRAINT work_calendar_pkey PRIMARY KEY (id)
)
The code suggested in the studio:
CREATE TABLE "bpd"."schedules_calendar" (
"id" BigInt DEFAULT nextval('bpd.work_calendar_id_seq'::regclass) NOT NULL,
"work_date" Date NOT NULL,
"work_year" Integer DEFAULT date_part('year'::text, work_date) NOT NULL,
"work_month" Integer DEFAULT date_part('month'::text, work_date) NOT NULL,
"work_day" Integer DEFAULT date_part('day'::text, work_date) NOT NULL,
"day_type" "bpd"."day_type" NOT NULL,
"name_holiday" Character Varying( 100 ) DEFAULT 'будний день'::character varying NOT NULL,
"week40_day" Interval NOT NULL,
"week40_month" Interval NOT NULL,
"week36_day" Interval NOT NULL,
"week36_month" Interval NOT NULL,
"week35_day" Interval NOT NULL,
"week35_month" Interval NOT NULL,
"week24_day" Interval NOT NULL,
"week24_month" Interval NOT NULL,
PRIMARY KEY ( "id" ) );
DEFAULT VALUE <> GENERATED ALWAYS AS STORED
the DEFAULT value is not recalculated when the reference column are changed. is not an identical construction GENERATED ALWAYS AS STORED
CREATE TABLE bpd.schedules_calendar
(
id bigint NOT NULL DEFAULT nextval('bpd.work_calendar_id_seq'::regclass),
work_date date NOT NULL,
work_year integer NOT NULL GENERATED ALWAYS AS (date_part('year'::text, work_date)) STORED,
work_month integer NOT NULL GENERATED ALWAYS AS (date_part('month'::text, work_date)) STORED,
work_day integer NOT NULL GENERATED ALWAYS AS (date_part('day'::text, work_date)) STORED,
day_type bpd.day_type NOT NULL,
name_holiday character varying(100) COLLATE pg_catalog."default" NOT NULL DEFAULT 'будний день'::character varying,
week40_day interval NOT NULL,
week40_month interval NOT NULL,
week36_day interval NOT NULL,
week36_month interval NOT NULL,
week35_day interval NOT NULL,
week35_month interval NOT NULL,
week24_day interval NOT NULL,
week24_month interval NOT NULL,
CONSTRAINT work_calendar_pkey PRIMARY KEY (id)
)
The code suggested in the studio:
CREATE TABLE "bpd"."schedules_calendar" (
"id" BigInt DEFAULT nextval('bpd.work_calendar_id_seq'::regclass) NOT NULL,
"work_date" Date NOT NULL,
"work_year" Integer DEFAULT date_part('year'::text, work_date) NOT NULL,
"work_month" Integer DEFAULT date_part('month'::text, work_date) NOT NULL,
"work_day" Integer DEFAULT date_part('day'::text, work_date) NOT NULL,
"day_type" "bpd"."day_type" NOT NULL,
"name_holiday" Character Varying( 100 ) DEFAULT 'будний день'::character varying NOT NULL,
"week40_day" Interval NOT NULL,
"week40_month" Interval NOT NULL,
"week36_day" Interval NOT NULL,
"week36_month" Interval NOT NULL,
"week35_day" Interval NOT NULL,
"week35_month" Interval NOT NULL,
"week24_day" Interval NOT NULL,
"week24_month" Interval NOT NULL,
PRIMARY KEY ( "id" ) );
DEFAULT VALUE <> GENERATED ALWAYS AS STORED
the DEFAULT value is not recalculated when the reference column are changed. is not an identical construction GENERATED ALWAYS AS STORED
reply:
They are in a separate list “Methods”.
This approach was used for other databases long before generated columns were implemented in PostgreSQL.
This approach was used for other databases long before generated columns were implemented in PostgreSQL.
====================
Which community? There are many ways in which a "data schema [can be] presented as data". In PostgreSQL there is only a single source of truth for what the data schema is - the "System Catalogs" [1]. Those tables are made available to the user in the pg_catalog schema.David J.
I came across a long acronym defining the name of the approach, then I decided that to come up with something fundamentally new is difficult enough. That everything already has a formal name. But I can't find it anymore. I need it to position my solution.
Shema - shema
Table Entity
id | propery1| property2| property3
Shema-data
Table Entity
id| name
Table Property Entity
id | id_entity | name| val
--
Regards, Dmitry!В списке pgsql-general по дате отправления: