public inbox for [email protected]help / color / mirror / Atom feed
Re: Plans for partitioning of inheriting tables 13+ messages / 4 participants [nested] [flat]
* Re: Plans for partitioning of inheriting tables @ 2024-11-01 08:41 [email protected] 0 siblings, 1 reply; 13+ messages in thread From: [email protected] @ 2024-11-01 08:41 UTC (permalink / raw) To: PostgreSQL General <[email protected]> Adrian Klaver <[email protected]> escribió: > It is just not the way you want to do it, see: > > https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-USING-INHERITANCE Thanks for your patience. Maybe I am not clever enough to understand you. I shall try to explain what I try to do. In my project, I have several tables. Each table has some basic technical attributes. For the time being, those are the surrogate key (ID) and a timestamp (ENTRY_PIT) to track the point in time when a record was inserted into the table. To improve consistency and reduce effort, I created a template table those attributes get inherited from by all other tables, e.g. TOPO_FILES. TOPO_FILES can contain GeoTIFF/raster data from different sources. For ease of data management, e.g. wipe all the data of one source, I tried to partition it by SOURCE_ID. And there the error rises that it is not possible to partition a table that is an heir of another table. I feel, you are trying to make me partition TOPO_SOURCES by using inheritance, but I cannot see... now I do see how I could achieve my desires. However, there pop up questions in my mind. To me, it seems, that partitioning using inheritance will not reduce maintenance but greatly increase it. It feels to me very much that I build manually with inheritance, what is done with the partitioning clause. Am I mistaken? In the description, there is the statement that instead of triggers, one could use rules. I am quite sure that, quite a while ago, I was advised in one of the mailing lists against the use of rules other than for inserts as the workings of update and delete rules are almost impenetrable. For me, at least, they were. Are my memories wrong about that? Is there experience on the efficiency/speed comparing partitioning with inheritance using triggers/rules and using the declarative way? I don't think that partition speed is an issue in my case, as I have fairly few records that are in themselves rather big. Remarks to the documentation: - There are examples for the insert path. However, not for the update or delete path. I feel, that those tend to be the more complex ones, especially if my memory is correct about the advice to avoid update and delete rules. - https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-INHERITANCE-MAINTENAN... misses out on a sentence not to forget to adapt the triggers/rules. Kind regards Thiemo ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Plans for partitioning of inheriting tables @ 2024-11-01 16:47 Adrian Klaver <[email protected]> parent: [email protected] 0 siblings, 1 reply; 13+ messages in thread From: Adrian Klaver @ 2024-11-01 16:47 UTC (permalink / raw) To: [email protected]; PostgreSQL General <[email protected]> On 11/1/24 01:41, [email protected] wrote: > > Adrian Klaver <[email protected]> escribió: > > >> It is just not the way you want to do it, see: >> >> https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-USING-INHERITANCE > > Thanks for your patience. Maybe I am not clever enough to understand > you. I shall try to explain what I try to do. > > In my project, I have several tables. Each table has some basic > technical attributes. For the time being, those are the surrogate key > (ID) and a timestamp (ENTRY_PIT) to track the point in time when a > record was inserted into the table. To improve consistency and reduce > effort, I created a template table those attributes get inherited from > by all other tables, e.g. TOPO_FILES. TOPO_FILES can contain > GeoTIFF/raster data from different sources. For ease of data management, > e.g. wipe all the data of one source, I tried to partition it by > SOURCE_ID. And there the error rises that it is not possible to > partition a table that is an heir of another table. > > I feel, you are trying to make me partition TOPO_SOURCES by using > inheritance, but I cannot see... now I do see how I could achieve my > desires. However, there pop up questions in my mind. > > To me, it seems, that partitioning using inheritance will not reduce > maintenance but greatly increase it. It feels to me very much that I > build manually with inheritance, what is done with the partitioning > clause. Am I mistaken? From here: https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE 5.12.2.3. Limitations "Individual partitions are linked to their partitioned table using inheritance behind-the-scenes. However, it is not possible to use all of the generic features of inheritance with declaratively partitioned tables or their partitions, as discussed below. Notably, a partition cannot have any parents other than the partitioned table it is a partition of, nor can a table inherit from both a partitioned table and a regular table. That means partitioned tables and their partitions never share an inheritance hierarchy with regular tables." Changing that would count as a major change. Even if you where to convince the developers to make the change the earliest it would released would be with the next major release in Fall of 2025. That assumes you can convince then early enough or at all. What I getting at is that you need to start thinking of another way of doing this if this is a current project. The choices are: 1) Declarative partitioning, where you cannot have your partition parent inherit from another table. 2) Partition by inheritance where you build the structure manually. > > In the description, there is the statement that instead of triggers, one > could use rules. I am quite sure that, quite a while ago, I was advised > in one of the mailing lists against the use of rules other than for > inserts as the workings of update and delete rules are almost > impenetrable. For me, at least, they were. Are my memories wrong about > that? Yes, I would stay away from rules. They are included in the documentation for completeness. You have enough on your plate without trying to figure out what rules do. > > Is there experience on the efficiency/speed comparing partitioning with > inheritance using triggers/rules and using the declarative way? I don't > think that partition speed is an issue in my case, as I have fairly few > records that are in themselves rather big. Hard to say without some firm numbers and/or testing. Also this "... I have fairly few records that are in themselves rather big" could use some explanation. In other words what makes you think that partitioning is the answer to this issue? > > Remarks to the documentation: > - There are examples for the insert path. However, not for the update or > delete path. I feel, that those tend to be the more complex ones, > especially if my memory is correct about the advice to avoid update and > delete rules. From the docs: "The schemes shown here assume that the values of a row's key column(s) never change, or at least do not change enough to require it to move to another partition. An UPDATE that attempts to do that will fail because of the CHECK constraints. If you need to handle such cases, you can put suitable update triggers on the child tables, but it makes management of the structure much more complicated." So yes, they would be more complicated as you are looking at possibly changing tables. Personally, I think you are heading to declarative partitioning. Either via your own scripts or something like pg_partman(https://github.com/pgpartman/pg_partman). > - > https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-INHERITANCE-MAINTENAN... misses out on a sentence not to forget to adapt the triggers/rules. > > Kind regards > > Thiemo > > > -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Plans for partitioning of inheriting tables @ 2024-11-01 17:21 [email protected] parent: Adrian Klaver <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: [email protected] @ 2024-11-01 17:21 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: PostgreSQL General <[email protected]> Adrian Klaver <[email protected]> escribió: > Changing that would count as a major change. Even if you where to > convince the developers to make the change the earliest it would > released would be with the next major release in Fall of 2025. That > assumes you can convince then early enough or at all. I was not trying to convince anyone to do anything about the implementation of declarative partitioning. I have been just curious if there were plans. If I have raised the impression of the former, I am sorry. > What I getting at is that you need to start thinking of another way > of doing this if this is a current project. The choices are: > > 1) Declarative partitioning, where you cannot have your partition > parent inherit from another table. > > 2) Partition by inheritance where you build the structure manually. I very much agree. Shying the effort involved for 2), I still tend to 1). I could break the inheritance pattern by explicitly putting the technical attributes into partitioned tables. My self, I probably won't use more than one source, but others might have several source for comparison or whatever. >> Is there experience on the efficiency/speed comparing partitioning >> with inheritance using triggers/rules and using the declarative >> way? I don't think that partition speed is an issue in my case, as >> I have fairly few records that are in themselves rather big. > > Hard to say without some firm numbers and/or testing. Sure, I was hoping those test would have been done some day. But in the end, to me, it is not important. > Also this "... I have fairly few records that are in themselves > rather big" could use some explanation. In other words what makes > you think that partitioning is the answer to this issue? I was not thinking that partitioning was the answer to a performance problem. Partitioning might be an answer to the maintenance of records, specifically if entire sources are affected. The size of the tif files to get loaded into the raster attribute TILE range from 112 kB to 32 MB. I am complete unaware of the inner storing mechanisms of raster in PostGIS, but on first sight, it seems that the rest of a records of TOPO_FILES is negligible compared to the TILE. The total number of files to be loaded in my case are 3273, even though that only encompasses a small part of the world, I do not think, the latter would surpass 100000 records. Not much for a database table, afaik. I mean to say that I believe that loading that much data into one field will take much more time than runtime difference of trigger/rules/declarative partitioning solutions would to sort the data into the correct partition. ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Plans for partitioning of inheriting tables @ 2024-11-01 18:01 Adrian Klaver <[email protected]> parent: [email protected] 0 siblings, 2 replies; 13+ messages in thread From: Adrian Klaver @ 2024-11-01 18:01 UTC (permalink / raw) To: [email protected]; +Cc: PostgreSQL General <[email protected]> On 11/1/24 10:21 AM, [email protected] wrote: > > Adrian Klaver <[email protected]> escribió: > >> Changing that would count as a major change. Even if you where to >> convince the developers to make the change the earliest it would >> released would be with the next major release in Fall of 2025. That >> assumes you can convince then early enough or at all. > > I was not trying to convince anyone to do anything about the > implementation of declarative partitioning. I have been just curious if > there were plans. If I have raised the impression of the former, I am > sorry. Even if there where plans, any changes would happen in the future and would not be help the now problem. >>> Is there experience on the efficiency/speed comparing partitioning >>> with inheritance using triggers/rules and using the declarative way? >>> I don't think that partition speed is an issue in my case, as I have >>> fairly few records that are in themselves rather big. >> >> Hard to say without some firm numbers and/or testing. > > Sure, I was hoping those test would have been done some day. But in the > end, to me, it is not important. That is contradicted by your statement below: "I mean to say that I believe that loading that much data into one field will take much more time than runtime difference of trigger/rules/declarative partitioning solutions would to sort the data into the correct partition." Either performance is important or it is not. If TILE is referring to the same thing you are dealing with in related question on psycopg list then you are talking about bytea storage. You should take a look at: https://www.postgresql.org/docs/current/storage-toast.html In any case assuming you are not entering/reading/updating all the bytea data at one time then you are looking at fetching only that bytea data that are filtered by other attributes of the rows. I would strongly suggest running some tests on a single table with the data and see if you can live with the performance results before complicating things with partitioning. > >> Also this "... I have fairly few records that are in themselves rather >> big" could use some explanation. In other words what makes you think >> that partitioning is the answer to this issue? > > I was not thinking that partitioning was the answer to a performance > problem. Partitioning might be an answer to the maintenance of records, > specifically if entire sources are affected. The size of the tif files > to get loaded into the raster attribute TILE range from 112 kB to 32 MB. > I am complete unaware of the inner storing mechanisms of raster in > PostGIS, but on first sight, it seems that the rest of a records of > TOPO_FILES is negligible compared to the TILE. The total number of files > to be loaded in my case are 3273, even though that only encompasses a > small part of the world, I do not think, the latter would surpass 100000 > records. Not much for a database table, afaik. I mean to say that I > believe that loading that much data into one field will take much more > time than runtime difference of trigger/rules/declarative partitioning > solutions would to sort the data into the correct partition. > -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Plans for partitioning of inheriting tables @ 2024-11-01 18:43 Torsten Förtsch <[email protected]> parent: Adrian Klaver <[email protected]> 1 sibling, 1 reply; 13+ messages in thread From: Torsten Förtsch @ 2024-11-01 18:43 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: [email protected]; PostgreSQL General <[email protected]> Thiemo, it looks to me like you are using inheritance just to make sure your SOURCES and TOPO_FILES tables have some common columns. If you are not actually querying the TEMPLATE_TECH table and expect to see all the rows from the other 2 tables in that one table combined, then you could use CREATE TABLE (LIKE ...) instead of inheritance. That way your "child" tables would become normal tables and you could use declarative partitioning on them. Even if you are querying the TEMPLATE_TECH table, you could still do that by turning the TEMPLATE_TECH table into a view which performs a UNION ALL over the other tables. Just my 2 cents -- Torsten On Fri, Nov 1, 2024 at 7:01 PM Adrian Klaver <[email protected]> wrote: > > > On 11/1/24 10:21 AM, [email protected] wrote: > > > > Adrian Klaver <[email protected]> escribió: > > > >> Changing that would count as a major change. Even if you where to > >> convince the developers to make the change the earliest it would > >> released would be with the next major release in Fall of 2025. That > >> assumes you can convince then early enough or at all. > > > > I was not trying to convince anyone to do anything about the > > implementation of declarative partitioning. I have been just curious if > > there were plans. If I have raised the impression of the former, I am > > sorry. > Even if there where plans, any changes would happen in the future and > would not be help the now problem. > > > >>> Is there experience on the efficiency/speed comparing partitioning > >>> with inheritance using triggers/rules and using the declarative way? > >>> I don't think that partition speed is an issue in my case, as I have > >>> fairly few records that are in themselves rather big. > >> > >> Hard to say without some firm numbers and/or testing. > > > > Sure, I was hoping those test would have been done some day. But in the > > end, to me, it is not important. > > That is contradicted by your statement below: > > "I mean to say that I believe that loading that much data into one field > will take much more time than runtime difference of > trigger/rules/declarative partitioning solutions would to sort the data > into the correct partition." > > Either performance is important or it is not. > > If TILE is referring to the same thing you are dealing with in related > question on psycopg list then you are talking about bytea storage. You > should take a look at: > > https://www.postgresql.org/docs/current/storage-toast.html > > In any case assuming you are not entering/reading/updating all the bytea > data at one time then you are looking at fetching only that bytea data > that are filtered by other attributes of the rows. I would strongly > suggest running some tests on a single table with the data and see if > you can live with the performance results before complicating things > with partitioning. > > > > >> Also this "... I have fairly few records that are in themselves rather > >> big" could use some explanation. In other words what makes you think > >> that partitioning is the answer to this issue? > > > > I was not thinking that partitioning was the answer to a performance > > problem. Partitioning might be an answer to the maintenance of records, > > specifically if entire sources are affected. The size of the tif files > > to get loaded into the raster attribute TILE range from 112 kB to 32 MB. > > I am complete unaware of the inner storing mechanisms of raster in > > PostGIS, but on first sight, it seems that the rest of a records of > > TOPO_FILES is negligible compared to the TILE. The total number of files > > to be loaded in my case are 3273, even though that only encompasses a > > small part of the world, I do not think, the latter would surpass 100000 > > records. Not much for a database table, afaik. I mean to say that I > > believe that loading that much data into one field will take much more > > time than runtime difference of trigger/rules/declarative partitioning > > solutions would to sort the data into the correct partition. > > > > -- > Adrian Klaver > [email protected] > > > ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Plans for partitioning of inheriting tables @ 2024-11-01 19:14 [email protected] parent: Adrian Klaver <[email protected]> 1 sibling, 0 replies; 13+ messages in thread From: [email protected] @ 2024-11-01 19:14 UTC (permalink / raw) To: PostgreSQL General <[email protected]> Adrian Klaver <[email protected]> escribió: > Even if there where plans, any changes would happen in the future > and would not be help the now problem. Yes and no. I can live without the partitioning, as I do not intend to load data from more than one source. Other might. But until others want to load data from different sources, a comment in the source might do that partitioning of inheriting tables will be supported in the future. But, that is an academic point now. > That is contradicted by your statement below: > > Either performance is important or it is not. Not quite. If the performance penalty by suboptimal choice in partitioning does not matter in the current project because the raster/bytea stuff does affect performance much more, it does not mean that I cannot work on other project where it can matter. And even if the latter is not the case, I can be just curious about it. > If TILE is referring to the same thing you are dealing with in > related question on psycopg list then you are talking about bytea > storage. You should take a look at: > > https://www.postgresql.org/docs/current/storage-toast.html Indeed, it does. Thanks for the hint. ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Plans for partitioning of inheriting tables @ 2024-11-01 19:16 [email protected] parent: Torsten Förtsch <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: [email protected] @ 2024-11-01 19:16 UTC (permalink / raw) To: PostgreSQL General <[email protected]> Thanks, I shall have a look into it. I was under the assumption the the create table like would create no more than a structural copy. Torsten F��rtsch <[email protected]> escribi��: > Thiemo, �� > it looks to me like you are using inheritance just to make sure > your SOURCES and TOPO_FILES tables have some common columns. If you > are not actually querying the TEMPLATE_TECH table and expect to see > all the rows from the other 2 tables in that one table combined, > then you could use CREATE TABLE (LIKE ...) instead of inheritance. > That way your "child" tables would become normal tables and you > could use declarative partitioning on them. > �� > Even if you are querying the TEMPLATE_TECH table, you could still > do that by turning the TEMPLATE_TECH table into a view which > performs a UNION ALL over the other tables. ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Plans for partitioning of inheriting tables @ 2024-11-01 19:38 Adrian Klaver <[email protected]> parent: [email protected] 0 siblings, 1 reply; 13+ messages in thread From: Adrian Klaver @ 2024-11-01 19:38 UTC (permalink / raw) To: [email protected]; PostgreSQL General <[email protected]> On 11/1/24 12:16, [email protected] wrote: > Thanks, I shall have a look into it. I was under the assumption the the > create table like would create no more than a structural copy. Not sure what you mean by structural copy, but the table created by CREATE TABLE LIKE will not have any association with the table it was created from. https://www.postgresql.org/docs/current/sql-createtable.html "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." > > Torsten Förtsch <[email protected] <mailto:[email protected]>> > escribió: > >> Thiemo, >> it looks to me like you are using inheritance just to make sure your >> SOURCES and TOPO_FILES tables have some common columns. If you are not >> actually querying the TEMPLATE_TECH table and expect to see all the >> rows from the other 2 tables in that one table combined, then you >> could use CREATE TABLE (LIKE ...) instead of inheritance. That way >> your "child" tables would become normal tables and you could use >> declarative partitioning on them. >> Even if you are querying the TEMPLATE_TECH table, you could still do >> that by turning the TEMPLATE_TECH table into a view which performs a >> UNION ALL over the other tables. > > -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Plans for partitioning of inheriting tables @ 2024-11-01 20:47 Thiemo Kellner <[email protected]> parent: Adrian Klaver <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Thiemo Kellner @ 2024-11-01 20:47 UTC (permalink / raw) To: PostgreSQL General <[email protected]> It looks to me basically to be a "create table A as select * from B where false". 01.11.2024 20:38:15 Adrian Klaver <[email protected]>: > On 11/1/24 12:16, [email protected] wrote: >> Thanks, I shall have a look into it. I was under the assumption the the create table like would create no more than a structural copy. > > Not sure what you mean by structural copy, but the table created by CREATE TABLE LIKE will not have any association with the table it was created from. > > https://www.postgresql.org/docs/current/sql-createtable.html > > "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." > >> Torsten Förtsch <[email protected] <mailto:[email protected]>> escribió: >> Thiemo, >>> it looks to me like you are using inheritance just to make sure your SOURCES and TOPO_FILES tables have some common columns. If you are not actually querying the TEMPLATE_TECH table and expect to see all the rows from the other 2 tables in that one table combined, then you could use CREATE TABLE (LIKE ...) instead of inheritance. That way your "child" tables would become normal tables and you could use declarative partitioning on them. >>> Even if you are querying the TEMPLATE_TECH table, you could still do that by turning the TEMPLATE_TECH table into a view which performs a UNION ALL over the other tables. >> > > -- > Adrian Klaver > [email protected] ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Plans for partitioning of inheriting tables @ 2024-11-01 20:57 Adrian Klaver <[email protected]> parent: Thiemo Kellner <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Adrian Klaver @ 2024-11-01 20:57 UTC (permalink / raw) To: Thiemo Kellner <[email protected]>; PostgreSQL General <[email protected]> On 11/1/24 13:47, Thiemo Kellner wrote: > It looks to me basically to be a "create table A as select * from B where false". No it more capable then that. CREATE TABLE <some_tbl> AS <some_other_tbl> is bare bones, you get the column names, types and data(or not) and that is it. CREATE TABLE <some_tbl> LIKE <some_other_tbl> has like_option which allows to transfer over more attributes of the table, for example defaults, constraints, indexes, etc. See https://www.postgresql.org/docs/current/sql-createtable.html LIKE source_table [ like_option ... ] > > 01.11.2024 20:38:15 Adrian Klaver <[email protected]>: > >> On 11/1/24 12:16, [email protected] wrote: >>> Thanks, I shall have a look into it. I was under the assumption the the create table like would create no more than a structural copy. >> >> Not sure what you mean by structural copy, but the table created by CREATE TABLE LIKE will not have any association with the table it was created from. >> >> https://www.postgresql.org/docs/current/sql-createtable.html >> >> "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." >> >>> Torsten Förtsch <[email protected] <mailto:[email protected]>> escribió: >>> Thiemo, >>>> it looks to me like you are using inheritance just to make sure your SOURCES and TOPO_FILES tables have some common columns. If you are not actually querying the TEMPLATE_TECH table and expect to see all the rows from the other 2 tables in that one table combined, then you could use CREATE TABLE (LIKE ...) instead of inheritance. That way your "child" tables would become normal tables and you could use declarative partitioning on them. >>>> Even if you are querying the TEMPLATE_TECH table, you could still do that by turning the TEMPLATE_TECH table into a view which performs a UNION ALL over the other tables. >>> >> >> -- >> Adrian Klaver >> [email protected] > > -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Plans for partitioning of inheriting tables @ 2024-11-01 23:10 [email protected] parent: Adrian Klaver <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: [email protected] @ 2024-11-01 23:10 UTC (permalink / raw) To: PostgreSQL General <[email protected]> Adrian Klaver <[email protected]> escribió: > On 11/1/24 13:47, Thiemo Kellner wrote: >> It looks to me basically to be a "create table A as select * from B >> where false". > > No it more capable then that. Yes, I wrote basically, not exactly. > CREATE TABLE <some_tbl> LIKE <some_other_tbl> has like_option which > allows to transfer over more attributes of the table, for example > defaults, constraints, indexes, etc. But, to my understanding, no primary nor unique nor foreign constraint. ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Plans for partitioning of inheriting tables @ 2024-11-01 23:53 Adrian Klaver <[email protected]> parent: [email protected] 0 siblings, 1 reply; 13+ messages in thread From: Adrian Klaver @ 2024-11-01 23:53 UTC (permalink / raw) To: [email protected]; PostgreSQL General <[email protected]> On 11/1/24 16:10, [email protected] wrote: > > Adrian Klaver <[email protected]> escribió: > >> On 11/1/24 13:47, Thiemo Kellner wrote: >>> It looks to me basically to be a "create table A as select * from B >>> where false". >> >> No it more capable then that. > > Yes, I wrote basically, not exactly. > >> CREATE TABLE <some_tbl> LIKE <some_other_tbl> has like_option which >> allows to transfer over more attributes of the table, for example >> defaults, constraints, indexes, etc. > > But, to my understanding, no primary nor unique nor foreign constraint. > "INCLUDING INDEXES Indexes, PRIMARY KEY, UNIQUE, and EXCLUDE constraints on the original table will be created on the new table. Names for the new indexes and constraints are chosen according to the default rules, regardless of how the originals were named. (This behavior avoids possible duplicate-name failures for the new indexes.) " FK's are not in the the INCLUDINGs, nor triggers. -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Plans for partitioning of inheriting tables @ 2024-11-02 07:13 Thiemo Kellner <[email protected]> parent: Adrian Klaver <[email protected]> 0 siblings, 0 replies; 13+ messages in thread From: Thiemo Kellner @ 2024-11-02 07:13 UTC (permalink / raw) To: pgsql-generallists.postgresql.org <[email protected]> My bad. I was expecting primary and unique to be mentioned here, so I did not read on. **INCLUDING CONSTRAINTS*** **#[https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-PARMS-LIKE-OPT-CONSTRAI...]* /*CHECK*// constraints will be copied. No distinction is made between column constraints and table constraints. Not-null constraints are always copied to the new table./ 02.11.2024 00:53:53 Adrian Klaver <[email protected]>: > On 11/1/24 16:10, [email protected] wrote: >> Adrian Klaver <[email protected]> escribió: >> On 11/1/24 13:47, Thiemo Kellner wrote: >>>> It looks to me basically to be a "create table A as select * from B where false". >>> >>> No it more capable then that. >> Yes, I wrote basically, not exactly. >> CREATE TABLE <some_tbl> LIKE <some_other_tbl> has like_option which allows to transfer over more attributes of the table, for example defaults, constraints, indexes, etc. >> But, to my understanding, no primary nor unique nor foreign constraint. >> > > > "INCLUDING INDEXES > > Indexes, PRIMARY KEY, UNIQUE, and EXCLUDE constraints on the original table will be created on the new table. Names for the new indexes and constraints are chosen according to the default rules, regardless of how the originals were named. (This behavior avoids possible duplicate-name failures for the new indexes.) > " > > FK's are not in the the INCLUDINGs, nor triggers. > > > -- > Adrian Klaver > [email protected] ^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2024-11-02 07:13 UTC | newest] Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-11-01 08:41 Re: Plans for partitioning of inheriting tables [email protected] 2024-11-01 16:47 ` Adrian Klaver <[email protected]> 2024-11-01 17:21 ` [email protected] 2024-11-01 18:01 ` Adrian Klaver <[email protected]> 2024-11-01 18:43 ` Torsten Förtsch <[email protected]> 2024-11-01 19:16 ` [email protected] 2024-11-01 19:38 ` Adrian Klaver <[email protected]> 2024-11-01 20:47 ` Thiemo Kellner <[email protected]> 2024-11-01 20:57 ` Adrian Klaver <[email protected]> 2024-11-01 23:10 ` [email protected] 2024-11-01 23:53 ` Adrian Klaver <[email protected]> 2024-11-02 07:13 ` Thiemo Kellner <[email protected]> 2024-11-01 19:14 ` [email protected]
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox