Thread: Snippets?
Is there any way in postgresql to have a "snippet" of SQL code which you could apply to all tables. Something you could add which would ensure that every table always has a Created, Changed and Deleted column for example. Sort of like a C include I suppose. Of course if I’m building a DB schema by hand I could just copy and paste those columns but it still seems a bit too, darn humans!, error prone.
Ken LaCrosse
Senior IT InfoSec and Infrastructure Monitoring Specialist
I.T. | American Recovery Service & Skipbusters
Phone: (800)398-6480 x3758
Email: KLacrosse@pkwillis.com
* Visibility is key. *
Without visibility you can't see.
What you can't see you can't measure.
What you can't measure you can’t planfully change.
What you can’t planfully change you can't manage.
And if we can’t manage then why are we here?
On 5/24/19 1:15 PM, Ken Lacrosse wrote:
Inheritance might be what you want.
Is there any way in postgresql to have a "snippet" of SQL code which you could apply to all tables. Something you could add which would ensure that every table always has a Created, Changed and Deleted column for example. Sort of like a C include I suppose. Of course if I’m building a DB schema by hand I could just copy and paste those columns but it still seems a bit too, darn humans!, error prone.
Inheritance might be what you want.
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
On 5/24/19 11:15 AM, Ken Lacrosse wrote: > Is there any way in postgresql to have a "snippet" of SQL code which you > could apply to all tables. Something you could add which would ensure > that every table always has a Created, Changed and Deleted column for > example. Sort of like a C include I suppose. Of course if I’m building > a DB schema by hand I could just copy and paste those columns but it > still seems a bit too, darn humans!, error prone. Not that I know of. Things I have done: 1) Template table that I includes those fields that I then add to. FYI the Sqitch schema management system allows you to do the above: https://sqitch.org/docs/manual/sqitch-add/#templates 2) A script that I run over table definition to add the fields. > > Ken LaCrosse > > Senior IT InfoSec and Infrastructure Monitoring Specialist > > I.T. | American Recovery Service & Skipbusters > > Phone: (800)398-6480 x3758 > > Email: KLacrosse@pkwillis.com <mailto:KLacrosse@pkwillis.com> > > www.pkwillis.com <http://www.pkwillis.com/> > > * Visibility is key. * > > Without visibility you can't see. > > What you can't see you can't measure. > > What you can't measure you can’t planfully change. > > What you can’t planfully change you can't manage. > > And if we can’t manage then why are we here? > > NOTICE: The information contained in this transmission, including > attachments, may contain confidential information that is privileged, > confidential and/or exempt from disclosure by applicable law. It is > intended only for the use of the person(s) or entity to which it is > addressed. If the reader of this transmission is not the intended > recipient, the reader is hereby notified that any review, use, > dissemination, distribution or duplication of this communication > (including any reliance thereon) is strictly prohibited. If you have > received this transmission in error, please contact the sender by reply > email, then delete and destroy the material in its entirety, whether in > electronic or hard copy format. WE SPECIFICALLY DISCLAIM RESPONSIBILITY > FOR ANY UNAUTHORIZED USE OF THIS COMMUNICATION OR ANY ATTACHMENTS TO IT. > Thank you. -- Adrian Klaver adrian.klaver@aklaver.com
On 5/24/19 1:27 PM, Adrian Klaver wrote: > On 5/24/19 11:15 AM, Ken Lacrosse wrote: >> Is there any way in postgresql to have a "snippet" of SQL code which you >> could apply to all tables. Something you could add which would ensure >> that every table always has a Created, Changed and Deleted column for >> example. Sort of like a C include I suppose. Of course if I’m building a >> DB schema by hand I could just copy and paste those columns but it still >> seems a bit too, darn humans!, error prone. > > Not that I know of. > > Things I have done: > 1) Template table that I includes those fields that I then add to. > > FYI the Sqitch schema management system allows you to do the above: > https://sqitch.org/docs/manual/sqitch-add/#templates > > 2) A script that I run over table definition to add the fields. What about INHERITS? test=# create table CCD_template ( test(# Created timestamptz, test(# Changed timestamptz, test(# Deleted timestamptz ); CREATE TABLE test=# create table foobar ( field1 integer, field2 bytea ) inherits (CCD_template); CREATE TABLE test=# test=# \d foobar Table "public.foobar" Column | Type | Modifiers ---------+--------------------------+----------- created | timestamp with time zone | changed | timestamp with time zone | deleted | timestamp with time zone | field1 | integer | field2 | bytea | Inherits: ccd_template > >> >> Ken LaCrosse >> >> Senior IT InfoSec and Infrastructure Monitoring Specialist >> >> I.T. | American Recovery Service & Skipbusters >> >> Phone: (800)398-6480 x3758 >> >> Email: KLacrosse@pkwillis.com <mailto:KLacrosse@pkwillis.com> >> >> www.pkwillis.com <http://www.pkwillis.com/> >> >> * Visibility is key. * >> >> Without visibility you can't see. >> >> What you can't see you can't measure. >> >> What you can't measure you can’t planfully change. >> >> What you can’t planfully change you can't manage. >> >> And if we can’t manage then why are we here? >> >> NOTICE: The information contained in this transmission, including >> attachments, may contain confidential information that is privileged, >> confidential and/or exempt from disclosure by applicable law. It is >> intended only for the use of the person(s) or entity to which it is >> addressed. If the reader of this transmission is not the intended >> recipient, the reader is hereby notified that any review, use, >> dissemination, distribution or duplication of this communication >> (including any reliance thereon) is strictly prohibited. If you have >> received this transmission in error, please contact the sender by reply >> email, then delete and destroy the material in its entirety, whether in >> electronic or hard copy format. WE SPECIFICALLY DISCLAIM RESPONSIBILITY >> FOR ANY UNAUTHORIZED USE OF THIS COMMUNICATION OR ANY ATTACHMENTS TO IT. >> Thank you. > > -- Angular momentum makes the world go 'round.
On 5/24/19 12:24 PM, Ron wrote: > On 5/24/19 1:27 PM, Adrian Klaver wrote: >> On 5/24/19 11:15 AM, Ken Lacrosse wrote: >>> Is there any way in postgresql to have a "snippet" of SQL code which >>> you could apply to all tables. Something you could add which would >>> ensure that every table always has a Created, Changed and Deleted >>> column for example. Sort of like a C include I suppose. Of course if >>> I’m building a DB schema by hand I could just copy and paste those >>> columns but it still seems a bit too, darn humans!, error prone. >> >> Not that I know of. >> >> Things I have done: >> 1) Template table that I includes those fields that I then add to. >> >> FYI the Sqitch schema management system allows you to do the above: >> https://sqitch.org/docs/manual/sqitch-add/#templates >> >> 2) A script that I run over table definition to add the fields. > > What about INHERITS? It is an option, though it does have baggage: https://www.postgresql.org/docs/11/sql-createtable.html "INHERITS ( parent_table [, ... ] ) The optional INHERITS clause specifies a list of tables from which the new table automatically inherits all columns. Parent tables can be plain tables or foreign tables. Use of INHERITS creates a persistent relationship between the new child table and its parent table(s). Schema modifications to the parent(s) normally propagate to children as well, and by default the data of the child table is included in scans of the parent(s). ...""" There is more. If I was to go that route I would use LIKE: "LIKE source_table [ like_option ... ] The LIKE clause specifies a table from which the new table automatically copies all column names, their data types, and their not-null constraints. Unlike INHERITS, the new table and original table are completely decoupled after creation is complete. Changes to the original table will not be applied to the new table, and it is not possible to include data of the new table in scans of the original table. ..." > > test=# create table CCD_template ( > test(# Created timestamptz, > test(# Changed timestamptz, > test(# Deleted timestamptz ); > CREATE TABLE > > test=# create table foobar ( > field1 integer, > field2 bytea > ) inherits (CCD_template); > CREATE TABLE > test=# > test=# \d foobar > Table "public.foobar" > Column | Type | Modifiers > ---------+--------------------------+----------- > created | timestamp with time zone | > changed | timestamp with time zone | > deleted | timestamp with time zone | > field1 | integer | > field2 | bytea | > Inherits: ccd_template > > >> >>> >>> Ken LaCrosse >>> >>> Senior IT InfoSec and Infrastructure Monitoring Specialist >>> >>> I.T. | American Recovery Service & Skipbusters >>> >>> Phone: (800)398-6480 x3758 >>> >>> Email: KLacrosse@pkwillis.com <mailto:KLacrosse@pkwillis.com> >>> >>> www.pkwillis.com <http://www.pkwillis.com/> >>> >>> * Visibility is key. * >>> >>> Without visibility you can't see. >>> >>> What you can't see you can't measure. >>> >>> What you can't measure you can’t planfully change. >>> >>> What you can’t planfully change you can't manage. >>> >>> And if we can’t manage then why are we here? >>> >>> NOTICE: The information contained in this transmission, including >>> attachments, may contain confidential information that is privileged, >>> confidential and/or exempt from disclosure by applicable law. It is >>> intended only for the use of the person(s) or entity to which it is >>> addressed. If the reader of this transmission is not the intended >>> recipient, the reader is hereby notified that any review, use, >>> dissemination, distribution or duplication of this communication >>> (including any reliance thereon) is strictly prohibited. If you have >>> received this transmission in error, please contact the sender by >>> reply email, then delete and destroy the material in its entirety, >>> whether in electronic or hard copy format. WE SPECIFICALLY DISCLAIM >>> RESPONSIBILITY FOR ANY UNAUTHORIZED USE OF THIS COMMUNICATION OR ANY >>> ATTACHMENTS TO IT. Thank you. >> >> > -- Adrian Klaver adrian.klaver@aklaver.com
Thanks guys. VERY informative. I'll be looking at all 3 approaches: INHERITS, LIKE and Sqitch. Ken LaCrosse Senior IT InfoSec and Infrastructure Monitoring Specialist I.T. | American Recovery Service & Skipbusters Phone: (800)398-6480 x3758 Email: KLacrosse@pkwillis.com www.pkwillis.com * Visibility is key. * Without visibility you can't see. What you can't see you can't measure. What you can't measure you can’t planfully change. What you can’t planfully change you can't manage. And if we can’t manage then why are we here? -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] Sent: Friday, May 24, 2019 12:30 To: Ron; pgsql-general@lists.postgresql.org Subject: Re: Snippets? On 5/24/19 12:24 PM, Ron wrote: > On 5/24/19 1:27 PM, Adrian Klaver wrote: >> On 5/24/19 11:15 AM, Ken Lacrosse wrote: >>> Is there any way in postgresql to have a "snippet" of SQL code which >>> you could apply to all tables. Something you could add which would >>> ensure that every table always has a Created, Changed and Deleted >>> column for example. Sort of like a C include I suppose. Of course if >>> I’m building a DB schema by hand I could just copy and paste those >>> columns but it still seems a bit too, darn humans!, error prone. >> >> Not that I know of. >> >> Things I have done: >> 1) Template table that I includes those fields that I then add to. >> >> FYI the Sqitch schema management system allows you to do the above: >> https://sqitch.org/docs/manual/sqitch-add/#templates >> >> 2) A script that I run over table definition to add the fields. > > What about INHERITS? It is an option, though it does have baggage: https://www.postgresql.org/docs/11/sql-createtable.html "INHERITS ( parent_table [, ... ] ) The optional INHERITS clause specifies a list of tables from which the new table automatically inherits all columns. Parent tables can be plain tables or foreign tables. Use of INHERITS creates a persistent relationship between the new child table and its parent table(s). Schema modifications to the parent(s) normally propagate to children as well, and by default the data of the child table is included in scans of the parent(s). ...""" There is more. If I was to go that route I would use LIKE: "LIKE source_table [ like_option ... ] The LIKE clause specifies a table from which the new table automatically copies all column names, their data types, and their not-null constraints. Unlike INHERITS, the new table and original table are completely decoupled after creation is complete. Changes to the original table will not be applied to the new table, and it is not possible to include data of the new table in scans of the original table. ..." > > test=# create table CCD_template ( > test(# Created timestamptz, > test(# Changed timestamptz, > test(# Deleted timestamptz ); > CREATE TABLE > > test=# create table foobar ( > field1 integer, > field2 bytea > ) inherits (CCD_template); > CREATE TABLE > test=# > test=# \d foobar > Table "public.foobar" > Column | Type | Modifiers > ---------+--------------------------+----------- > created | timestamp with time zone | > changed | timestamp with time zone | > deleted | timestamp with time zone | > field1 | integer | > field2 | bytea | > Inherits: ccd_template > > >> >>> >>> Ken LaCrosse >>> >>> Senior IT InfoSec and Infrastructure Monitoring Specialist >>> >>> I.T. | American Recovery Service & Skipbusters >>> >>> Phone: (800)398-6480 x3758 >>> >>> Email: KLacrosse@pkwillis.com <mailto:KLacrosse@pkwillis.com> >>> >>> www.pkwillis.com <http://www.pkwillis.com/> >>> >>> * Visibility is key. * >>> >>> Without visibility you can't see. >>> >>> What you can't see you can't measure. >>> >>> What you can't measure you can’t planfully change. >>> >>> What you can’t planfully change you can't manage. >>> >>> And if we can’t manage then why are we here? >>> >>> NOTICE: The information contained in this transmission, including >>> attachments, may contain confidential information that is privileged, >>> confidential and/or exempt from disclosure by applicable law. It is >>> intended only for the use of the person(s) or entity to which it is >>> addressed. If the reader of this transmission is not the intended >>> recipient, the reader is hereby notified that any review, use, >>> dissemination, distribution or duplication of this communication >>> (including any reliance thereon) is strictly prohibited. If you have >>> received this transmission in error, please contact the sender by >>> reply email, then delete and destroy the material in its entirety, >>> whether in electronic or hard copy format. WE SPECIFICALLY DISCLAIM >>> RESPONSIBILITY FOR ANY UNAUTHORIZED USE OF THIS COMMUNICATION OR ANY >>> ATTACHMENTS TO IT. Thank you. >> >> > -- Adrian Klaver adrian.klaver@aklaver.com NOTICE: The information contained in this transmission, including attachments, may contain confidential information thatis privileged, confidential and/or exempt from disclosure by applicable law. It is intended only for the use of the person(s)or entity to which it is addressed. If the reader of this transmission is not the intended recipient, the readeris hereby notified that any review, use, dissemination, distribution or duplication of this communication (includingany reliance thereon) is strictly prohibited. If you have received this transmission in error, please contact thesender by reply email, then delete and destroy the material in its entirety, whether in electronic or hard copy format.WE SPECIFICALLY DISCLAIM RESPONSIBILITY FOR ANY UNAUTHORIZED USE OF THIS COMMUNICATION OR ANY ATTACHMENTS TO IT. Thankyou.