Обсуждение: Relation wide 'LIKE' clause
Hi, Postgres create table statement supports `LIKE source_table [like_option... ]` to specify `a table from which the new table automatically copies all column names, their data types, and their not-null constraints.` according to documentation [1]. I am wondering if a similar clause would make sense to copy relation wide settings. For example consider a relation created like this: `CREATE TABLE source_table ([column, ...]) USING customam WITH (storage_parameter1 = value1, ... )` Maybe a statement similar to: `CREATE TABLE target LIKE source_table` which should be equivalent to: `CREATE TABLE target (LIKE source_table INCLUDING ALL) USING customam WITH (storage_parameter1 = value1, ...)` can be usefull as a syntactic shortcut. Maybe the usefulness of such sortcut becomes a bit more apparent if one considers that custom access methods can offer a diversity of storage parameters that interact both at relation and column level, especially when the source relation is column oriented. If the possibility for such a statment is not discarded, a patch can be readily provided. Cheers, //Georgios [1] https://www.postgresql.org/docs/13/sql-createtable.html
On 2020-06-10 11:42, Georgios wrote: > Postgres create table statement supports `LIKE source_table [like_option... ]` > to specify `a table from which the new table automatically copies all column > names, their data types, and their not-null constraints.` according to > documentation [1]. > > I am wondering if a similar clause would make sense to copy relation wide > settings. For example consider a relation created like this: > > `CREATE TABLE source_table ([column, ...]) USING customam WITH (storage_parameter1 = value1, ... )` We already have LIKE INCLUDING STORAGE. Maybe that should just be updated to work like what you are asking for. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Wednesday, June 10, 2020 12:08 PM, Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote: > On 2020-06-10 11:42, Georgios wrote: > > > Postgres create table statement supports `LIKE source_table [like_option... ]` > > to specify `a table from which the new table automatically copies all column names, their data types, and their not-nullconstraints.` according to > > documentation [1]. > > I am wondering if a similar clause would make sense to copy relation wide > > settings. For example consider a relation created like this: > > `CREATE TABLE source_table ([column, ...]) USING customam WITH (storage_parameter1 = value1, ... )` > > We already have LIKE INCLUDING STORAGE. Maybe that should just be > updated to work like what you are asking for. This is correct. However I do see some limitations there. Consider the valid scenario: `CREATE TABLE target (LIKE source_am1 INCLUDING ALL, LIKE source_am2 INCLUDING ALL)` Which source relation should be used for the access method and storage parameters? Also I _think_ that the current `LIKE` clause specifically targets column definitions in the SQL standard. I am a bit hesitant on the last part, yet this is my current understanding. Please, let me know what you think. > > ------------------------------------------------------------------------------------------------------------------ > > Peter Eisentraut http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services