pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Composite type: Primary Key and validation
11+ messages / 5 participants
[nested] [flat]

* Composite type: Primary Key and validation
@ 2023-06-05 09:49 Lorusso Domenico <domenico.l76@gmail.com>
  2023-06-05 14:02 ` Re: Composite type: Primary Key and validation Laurenz Albe <laurenz.albe@cybertec.at>
  0 siblings, 1 reply; 11+ messages in thread

From: Lorusso Domenico @ 2023-06-05 09:49 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Hello guys,
I've a couple of questions about composite type.
Suppose this composite type:
CREATE TYPE my_type AS (
    user_ts_start My_start_timestamp,
    user_ts_end My_end_timestamp,
    db_ts_start My_start_timestamp,
    db_ts_end My_end_timestamp,
    audit_record jsonb
);
My_start_timestamp is a domain of timestamp with default as now().
My_end_timestamp is a domain of timestamp with default as infinite


   1. May I use user_ts_start and/or db_ts_start has part of Primary Key of
   a table that contains a field of my_type?
   2. to add an overall check constraint on the entire composite type,
   could be a valid approach to create a domain based on my_type and add a
   custom function to validate it? (check_my_type(VALUE)). In this way I've a
   dominan of composite type that contain others domain... what do you think?


thank's in advance

-- 
Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Composite type: Primary Key and validation
  2023-06-05 09:49 Composite type: Primary Key and validation Lorusso Domenico <domenico.l76@gmail.com>
@ 2023-06-05 14:02 ` Laurenz Albe <laurenz.albe@cybertec.at>
  2023-06-05 14:17   ` Re: Composite type: Primary Key and validation Ron <ronljohnsonjr@gmail.com>
  2023-06-05 15:06   ` Re: Composite type: Primary Key and validation Lorusso Domenico <domenico.l76@gmail.com>
  0 siblings, 2 replies; 11+ messages in thread

From: Laurenz Albe @ 2023-06-05 14:02 UTC (permalink / raw)
  To: Lorusso Domenico <domenico.l76@gmail.com>; pgsql-general@lists.postgresql.org

On Mon, 2023-06-05 at 11:49 +0200, Lorusso Domenico wrote:
> I've a couple of questions about composite type.
> Suppose this composite type:
> CREATE TYPE my_type AS (
>     user_ts_start My_start_timestamp,
>     user_ts_end My_end_timestamp,
>     db_ts_start My_start_timestamp,
>     db_ts_end My_end_timestamp,
>     audit_record jsonb
> );
> My_start_timestamp is a domain of timestamp with default as now().
> My_end_timestamp is a domain of timestamp with default as infinite
> 
>    1. May I use user_ts_start and/or db_ts_start has part of Primary Key of a table that contains a field of my_type?
>    2. to add an overall check constraint on the entire composite type, could be a valid
>       approach to create a domain based on my_type and add a custom function to validate it? (check_my_type(VALUE)).
>       In this way I've a dominan of composite type that contain others domain... what do you think?

Avoid using composite types as data types for a table column.
It adds complexity for no clear gain.

Yours,
Laurenz Albe





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Composite type: Primary Key and validation
  2023-06-05 09:49 Composite type: Primary Key and validation Lorusso Domenico <domenico.l76@gmail.com>
  2023-06-05 14:02 ` Re: Composite type: Primary Key and validation Laurenz Albe <laurenz.albe@cybertec.at>
@ 2023-06-05 14:17   ` Ron <ronljohnsonjr@gmail.com>
  2023-06-05 15:02     ` Re: Composite type: Primary Key and validation Lorusso Domenico <domenico.l76@gmail.com>
  1 sibling, 1 reply; 11+ messages in thread

From: Ron @ 2023-06-05 14:17 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 6/5/23 09:02, Laurenz Albe wrote:
> On Mon, 2023-06-05 at 11:49 +0200, Lorusso Domenico wrote:
>> I've a couple of questions about composite type.
>> Suppose this composite type:
>> CREATE TYPE my_type AS (
>>      user_ts_start My_start_timestamp,
>>      user_ts_end My_end_timestamp,
>>      db_ts_start My_start_timestamp,
>>      db_ts_end My_end_timestamp,
>>      audit_record jsonb
>> );
>> My_start_timestamp is a domain of timestamp with default as now().
>> My_end_timestamp is a domain of timestamp with default as infinite
>>
>>     1. May I use user_ts_start and/or db_ts_start has part of Primary Key of a table that contains a field of my_type?
>>     2. to add an overall check constraint on the entire composite type, could be a valid
>>        approach to create a domain based on my_type and add a custom function to validate it? (check_my_type(VALUE)).
>>        In this way I've a dominan of composite type that contain others domain... what do you think?
> Avoid using composite types as data types for a table column.
> It adds complexity for no clear gain.

Isn't now() also a bad idea, since it's the "now" at the start of the 
transaction?

Better to use clock_timestamp().

-- 
Born in Arizona, moved to Babylonia.





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Composite type: Primary Key and validation
  2023-06-05 09:49 Composite type: Primary Key and validation Lorusso Domenico <domenico.l76@gmail.com>
  2023-06-05 14:02 ` Re: Composite type: Primary Key and validation Laurenz Albe <laurenz.albe@cybertec.at>
  2023-06-05 14:17   ` Re: Composite type: Primary Key and validation Ron <ronljohnsonjr@gmail.com>
@ 2023-06-05 15:02     ` Lorusso Domenico <domenico.l76@gmail.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Lorusso Domenico @ 2023-06-05 15:02 UTC (permalink / raw)
  To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

Thank's a lot, I'll take care of it.

Il giorno lun 5 giu 2023 alle ore 16:18 Ron <ronljohnsonjr@gmail.com> ha
scritto:

> On 6/5/23 09:02, Laurenz Albe wrote:
> > On Mon, 2023-06-05 at 11:49 +0200, Lorusso Domenico wrote:
> >> I've a couple of questions about composite type.
> >> Suppose this composite type:
> >> CREATE TYPE my_type AS (
> >>      user_ts_start My_start_timestamp,
> >>      user_ts_end My_end_timestamp,
> >>      db_ts_start My_start_timestamp,
> >>      db_ts_end My_end_timestamp,
> >>      audit_record jsonb
> >> );
> >> My_start_timestamp is a domain of timestamp with default as now().
> >> My_end_timestamp is a domain of timestamp with default as infinite
> >>
> >>     1. May I use user_ts_start and/or db_ts_start has part of Primary
> Key of a table that contains a field of my_type?
> >>     2. to add an overall check constraint on the entire composite type,
> could be a valid
> >>        approach to create a domain based on my_type and add a custom
> function to validate it? (check_my_type(VALUE)).
> >>        In this way I've a dominan of composite type that contain others
> domain... what do you think?
> > Avoid using composite types as data types for a table column.
> > It adds complexity for no clear gain.
>
> Isn't now() also a bad idea, since it's the "now" at the start of the
> transaction?
>
> Better to use clock_timestamp().
>
> --
> Born in Arizona, moved to Babylonia.
>
>
>

-- 
Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Composite type: Primary Key and validation
  2023-06-05 09:49 Composite type: Primary Key and validation Lorusso Domenico <domenico.l76@gmail.com>
  2023-06-05 14:02 ` Re: Composite type: Primary Key and validation Laurenz Albe <laurenz.albe@cybertec.at>
@ 2023-06-05 15:06   ` Lorusso Domenico <domenico.l76@gmail.com>
  2023-06-05 15:17     ` Re: Composite type: Primary Key and validation Adrian Klaver <adrian.klaver@aklaver.com>
  2023-06-05 17:08     ` Re: Composite type: Primary Key and validation Laurenz Albe <laurenz.albe@cybertec.at>
  2023-06-05 19:43     ` Re: Composite type: Primary Key and validation Ron <ronljohnsonjr@gmail.com>
  1 sibling, 3 replies; 11+ messages in thread

From: Lorusso Domenico @ 2023-06-05 15:06 UTC (permalink / raw)
  To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-general@lists.postgresql.org

Thank's, you are right, I've the same doubts.
A composite type is useful because I've to add all these information on
many tables and because it more easy to pass all these informations to
functions that have to ensure the right format and evaluation.

Talking about first point I could use the "table inheritance", but I've to
reset all constraints for each table :-(

Il giorno lun 5 giu 2023 alle ore 16:02 Laurenz Albe <
laurenz.albe@cybertec.at> ha scritto:

> On Mon, 2023-06-05 at 11:49 +0200, Lorusso Domenico wrote:
> > I've a couple of questions about composite type.
> > Suppose this composite type:
> > CREATE TYPE my_type AS (
> >     user_ts_start My_start_timestamp,
> >     user_ts_end My_end_timestamp,
> >     db_ts_start My_start_timestamp,
> >     db_ts_end My_end_timestamp,
> >     audit_record jsonb
> > );
> > My_start_timestamp is a domain of timestamp with default as now().
> > My_end_timestamp is a domain of timestamp with default as infinite
> >
> >    1. May I use user_ts_start and/or db_ts_start has part of Primary Key
> of a table that contains a field of my_type?
> >    2. to add an overall check constraint on the entire composite type,
> could be a valid
> >       approach to create a domain based on my_type and add a custom
> function to validate it? (check_my_type(VALUE)).
> >       In this way I've a dominan of composite type that contain others
> domain... what do you think?
>
> Avoid using composite types as data types for a table column.
> It adds complexity for no clear gain.
>
> Yours,
> Laurenz Albe
>


-- 
Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Composite type: Primary Key and validation
  2023-06-05 09:49 Composite type: Primary Key and validation Lorusso Domenico <domenico.l76@gmail.com>
  2023-06-05 14:02 ` Re: Composite type: Primary Key and validation Laurenz Albe <laurenz.albe@cybertec.at>
  2023-06-05 15:06   ` Re: Composite type: Primary Key and validation Lorusso Domenico <domenico.l76@gmail.com>
@ 2023-06-05 15:17     ` Adrian Klaver <adrian.klaver@aklaver.com>
  2023-06-05 18:19       ` Re: Composite type: Primary Key and validation Lorusso Domenico <domenico.l76@gmail.com>
  2 siblings, 1 reply; 11+ messages in thread

From: Adrian Klaver @ 2023-06-05 15:17 UTC (permalink / raw)
  To: Lorusso Domenico <domenico.l76@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-general@lists.postgresql.org

On 6/5/23 08:06, Lorusso Domenico wrote:
> Thank's, you are right, I've the same doubts.
> A composite type is useful because I've to add all these information on 
> many tables and because it more easy to pass all these informations to 
> functions that have to ensure the right format and evaluation.

I do that with a Sqitch(https://sqitch.org/) template, the relevant part 
being:

...

CREATE TABLE [% IF schema %][% schema %][% ELSE %]schema_name[% END 
%].[% IF table %][% table %][% ELSE %]table_name[% END %](
     line_id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

     ts_insert       timestamptz NOT NULL DEFAULT now(),
     ts_update       timestamptz,
     user_insert     varchar(20) NOT NULL DEFAULT "session_user"(),
     user_update     varchar(20)
);

...

I then add the table specific fields after Sqitch creates the deploy 
script from the template.

> 
> Talking about first point I could use the "table inheritance", but I've 
> to reset all constraints for each table :-(
> 

> -- 
> Domenico L.
> 
> per stupire mezz'ora basta un libro di storia,
> io cercai di imparare la Treccani a memoria... [F.d.A.]

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Composite type: Primary Key and validation
  2023-06-05 09:49 Composite type: Primary Key and validation Lorusso Domenico <domenico.l76@gmail.com>
  2023-06-05 14:02 ` Re: Composite type: Primary Key and validation Laurenz Albe <laurenz.albe@cybertec.at>
  2023-06-05 15:06   ` Re: Composite type: Primary Key and validation Lorusso Domenico <domenico.l76@gmail.com>
  2023-06-05 15:17     ` Re: Composite type: Primary Key and validation Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-06-05 18:19       ` Lorusso Domenico <domenico.l76@gmail.com>
  2023-06-05 18:22         ` Re: Composite type: Primary Key and validation Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Lorusso Domenico @ 2023-06-05 18:19 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org

thank's Adrian, my problem is I've to use CloudSql, so I can't install
extensions not verified by google...

Il giorno lun 5 giu 2023 alle ore 17:17 Adrian Klaver <
adrian.klaver@aklaver.com> ha scritto:

> On 6/5/23 08:06, Lorusso Domenico wrote:
> > Thank's, you are right, I've the same doubts.
> > A composite type is useful because I've to add all these information on
> > many tables and because it more easy to pass all these informations to
> > functions that have to ensure the right format and evaluation.
>
> I do that with a Sqitch(https://sqitch.org/) template, the relevant part
> being:
>
> ...
>
> CREATE TABLE [% IF schema %][% schema %][% ELSE %]schema_name[% END
> %].[% IF table %][% table %][% ELSE %]table_name[% END %](
>      line_id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
>
>      ts_insert       timestamptz NOT NULL DEFAULT now(),
>      ts_update       timestamptz,
>      user_insert     varchar(20) NOT NULL DEFAULT "session_user"(),
>      user_update     varchar(20)
> );
>
> ...
>
> I then add the table specific fields after Sqitch creates the deploy
> script from the template.
>
> >
> > Talking about first point I could use the "table inheritance", but I've
> > to reset all constraints for each table :-(
> >
>
> > --
> > Domenico L.
> >
> > per stupire mezz'ora basta un libro di storia,
> > io cercai di imparare la Treccani a memoria... [F.d.A.]
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>

-- 
Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Composite type: Primary Key and validation
  2023-06-05 09:49 Composite type: Primary Key and validation Lorusso Domenico <domenico.l76@gmail.com>
  2023-06-05 14:02 ` Re: Composite type: Primary Key and validation Laurenz Albe <laurenz.albe@cybertec.at>
  2023-06-05 15:06   ` Re: Composite type: Primary Key and validation Lorusso Domenico <domenico.l76@gmail.com>
  2023-06-05 15:17     ` Re: Composite type: Primary Key and validation Adrian Klaver <adrian.klaver@aklaver.com>
  2023-06-05 18:19       ` Re: Composite type: Primary Key and validation Lorusso Domenico <domenico.l76@gmail.com>
@ 2023-06-05 18:22         ` Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Adrian Klaver @ 2023-06-05 18:22 UTC (permalink / raw)
  To: Lorusso Domenico <domenico.l76@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org

On 6/5/23 11:19 AM, Lorusso Domenico wrote:
> thank's Adrian, my problem is I've to use CloudSql, so I can't install 
> extensions not verified by google...

It is not actually an extension, it is PERL program. Though I'm guessing 
that will still be an issue.

-- 
Adrian Klaver
adrian.klaver@aklaver.com





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Composite type: Primary Key and validation
  2023-06-05 09:49 Composite type: Primary Key and validation Lorusso Domenico <domenico.l76@gmail.com>
  2023-06-05 14:02 ` Re: Composite type: Primary Key and validation Laurenz Albe <laurenz.albe@cybertec.at>
  2023-06-05 15:06   ` Re: Composite type: Primary Key and validation Lorusso Domenico <domenico.l76@gmail.com>
@ 2023-06-05 17:08     ` Laurenz Albe <laurenz.albe@cybertec.at>
  2023-06-05 17:41       ` Re: Composite type: Primary Key and validation Merlin Moncure <mmoncure@gmail.com>
  2 siblings, 1 reply; 11+ messages in thread

From: Laurenz Albe @ 2023-06-05 17:08 UTC (permalink / raw)
  To: Lorusso Domenico <domenico.l76@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

On Mon, 2023-06-05 at 17:06 +0200, Lorusso Domenico wrote:
> A composite type is useful because I've to add all these information on many tables and because
> it more easy to pass all these informations to functions that have to ensure the right format and evaluation.
> 
> Talking about first point I could use the "table inheritance", but I've to reset all constraints for each table :-(

Inheritance is actually a very good way to do this.
You don't inherit constraints, but at least the column definitions.

Yours,
Laurenz Albe





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Composite type: Primary Key and validation
  2023-06-05 09:49 Composite type: Primary Key and validation Lorusso Domenico <domenico.l76@gmail.com>
  2023-06-05 14:02 ` Re: Composite type: Primary Key and validation Laurenz Albe <laurenz.albe@cybertec.at>
  2023-06-05 15:06   ` Re: Composite type: Primary Key and validation Lorusso Domenico <domenico.l76@gmail.com>
  2023-06-05 17:08     ` Re: Composite type: Primary Key and validation Laurenz Albe <laurenz.albe@cybertec.at>
@ 2023-06-05 17:41       ` Merlin Moncure <mmoncure@gmail.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Merlin Moncure @ 2023-06-05 17:41 UTC (permalink / raw)
  To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Lorusso Domenico <domenico.l76@gmail.com>; pgsql-general@lists.postgresql.org

On Mon, Jun 5, 2023 at 12:08 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

> On Mon, 2023-06-05 at 17:06 +0200, Lorusso Domenico wrote:
> > A composite type is useful because I've to add all these information on
> many tables and because
> > it more easy to pass all these informations to functions that have to
> ensure the right format and evaluation.
> >
> > Talking about first point I could use the "table inheritance", but I've
> to reset all constraints for each table :-(
>
> Inheritance is actually a very good way to do this.
> You don't inherit constraints, but at least the column definitions.


Agreed on not using composite type.

Another suggestion, this might be a good idea for range type.

merlin

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Composite type: Primary Key and validation
  2023-06-05 09:49 Composite type: Primary Key and validation Lorusso Domenico <domenico.l76@gmail.com>
  2023-06-05 14:02 ` Re: Composite type: Primary Key and validation Laurenz Albe <laurenz.albe@cybertec.at>
  2023-06-05 15:06   ` Re: Composite type: Primary Key and validation Lorusso Domenico <domenico.l76@gmail.com>
@ 2023-06-05 19:43     ` Ron <ronljohnsonjr@gmail.com>
  2 siblings, 0 replies; 11+ messages in thread

From: Ron @ 2023-06-05 19:43 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Consider applying "database normalization" to the schema so that the columns 
are only in one table, and then pass around synthetic keys.

On 6/5/23 10:06, Lorusso Domenico wrote:
> Thank's, you are right, I've the same doubts.
> A composite type is useful because I've to add all these information on 
> many tables and because it more easy to pass all these informations to 
> functions that have to ensure the right format and evaluation.
>
> Talking about first point I could use the "table inheritance", but I've to 
> reset all constraints for each table :-(
>
> Il giorno lun 5 giu 2023 alle ore 16:02 Laurenz Albe 
> <laurenz.albe@cybertec.at> ha scritto:
>
>     On Mon, 2023-06-05 at 11:49 +0200, Lorusso Domenico wrote:
>     > I've a couple of questions about composite type.
>     > Suppose this composite type:
>     > CREATE TYPE my_type AS (
>     >     user_ts_start My_start_timestamp,
>     >     user_ts_end My_end_timestamp,
>     >     db_ts_start My_start_timestamp,
>     >     db_ts_end My_end_timestamp,
>     >     audit_record jsonb
>     > );
>     > My_start_timestamp is a domain of timestamp with default as now().
>     > My_end_timestamp is a domain of timestamp with default as infinite
>     >
>     >    1. May I use user_ts_start and/or db_ts_start has part of Primary
>     Key of a table that contains a field of my_type?
>     >    2. to add an overall check constraint on the entire composite
>     type, could be a valid
>     >       approach to create a domain based on my_type and add a custom
>     function to validate it? (check_my_type(VALUE)).
>     >       In this way I've a dominan of composite type that contain
>     others domain... what do you think?
>
>     Avoid using composite types as data types for a table column.
>     It adds complexity for no clear gain.
>
>     Yours,
>     Laurenz Albe
>
>
>
> -- 
> Domenico L.
>
> per stupire mezz'ora basta un libro di storia,
> io cercai di imparare la Treccani a memoria... [F.d.A.]

-- 
Born in Arizona, moved to Babylonia.

^ permalink  raw  reply  [nested|flat] 11+ messages in thread


end of thread, other threads:[~2023-06-05 19:43 UTC | newest]

Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-06-05 09:49 Composite type: Primary Key and validation Lorusso Domenico <domenico.l76@gmail.com>
2023-06-05 14:02 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-06-05 14:17   ` Ron <ronljohnsonjr@gmail.com>
2023-06-05 15:02     ` Lorusso Domenico <domenico.l76@gmail.com>
2023-06-05 15:06   ` Lorusso Domenico <domenico.l76@gmail.com>
2023-06-05 15:17     ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-06-05 18:19       ` Lorusso Domenico <domenico.l76@gmail.com>
2023-06-05 18:22         ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-06-05 17:08     ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-06-05 17:41       ` Merlin Moncure <mmoncure@gmail.com>
2023-06-05 19:43     ` Ron <ronljohnsonjr@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox