Thread: Two academic questions
Good afternoon.
There are a couple of questions that I come back to from time to time:
Why are COLUMN GENERATED ALWAYS AS called "methods" and singled out as a separate field type?
Is there a common name in the community for the approach in which the data schema is presented as data?
There are a couple of questions that I come back to from time to time:
Why are COLUMN GENERATED ALWAYS AS called "methods" and singled out as a separate field type?
Is there a common name in the community for the approach in which the data schema is presented as data?
--
Regards, Dmitry!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?
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.
ср, 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!On Tuesday, February 1, 2022, Дмитрий Иванов <firstdismay@gmail.com> wrote:
ср, 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?It is possible to give a link, but it would require authorization:Here are the contents of the topic: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 STOREDreply:They are in a separate list “Methods”.
This approach was used for other databases long before generated columns were implemented in PostgreSQL.====================
It has to do with the syntax of generated and that you can generate data in different ways. Calling those ways “methods” seems reasonable.
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.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 - shemaTable Entityid | propery1| property2| property3Shema-dataTable Entityid| nameTable Property Entityid | id_entity | name| val
The Shema-data thing is called the Boogyman pattern because teachers use it to scare students and illustrate what not to do when designing a data model. You can also find it abbreviated “EAV anti-pattern” where EAV stands for entity-attribute-value.
David J.
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 STOREDreply:They are in a separate list “Methods”.
This approach was used for other databases long before generated columns were implemented in PostgreSQL.====================It has to do with the syntax of generated and that you can generate data in different ways. Calling those ways “methods” seems reasonable.
It's hard to argue with that. However, it is not quite clear to me, what origin are we talking about? I have worked with MS products and currently with PostgreSQL and have not encountered this interpretation.
The Shema-data thing is called the Boogyman pattern because teachers use it to scare students and illustrate what not to do when designing a data model. You can also find it abbreviated “EAV anti-pattern” where EAV stands for entity-attribute-value.
I think that's what I need, thank you.
--Regards, Dmitry!
On Tuesday, February 1, 2022, Дмитрий Иванов <firstdismay@gmail.com> wrote:
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 STOREDreply:They are in a separate list “Methods”.
This approach was used for other databases long before generated columns were implemented in PostgreSQL.====================It has to do with the syntax of generated and that you can generate data in different ways. Calling those ways “methods” seems reasonable.It's hard to argue with that. However, it is not quite clear to me, what origin are we talking about? I have worked with MS products and currently with PostgreSQL and have not encountered this interpretation.
“Origin”? The documentation, and my internalizing of it based on my personal experiences.
Anyway, I did my best given the unclear (to me at least) and limited information you provided, and the fact I don’t really understand the question.
David J.
Thank you, I learned everything I needed to know.
--
Regards, Dmitry!ср, 2 февр. 2022 г. в 11:36, David G. Johnston <david.g.johnston@gmail.com>:
On Tuesday, February 1, 2022, Дмитрий Иванов <firstdismay@gmail.com> wrote: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 STOREDreply:They are in a separate list “Methods”.
This approach was used for other databases long before generated columns were implemented in PostgreSQL.====================It has to do with the syntax of generated and that you can generate data in different ways. Calling those ways “methods” seems reasonable.It's hard to argue with that. However, it is not quite clear to me, what origin are we talking about? I have worked with MS products and currently with PostgreSQL and have not encountered this interpretation.“Origin”? The documentation, and my internalizing of it based on my personal experiences.Anyway, I did my best given the unclear (to me at least) and limited information you provided, and the fact I don’t really understand the question.David J.