Обсуждение: 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