Обсуждение: [bug report] About create table like feature's bug

Поиск
Список
Период
Сортировка

[bug report] About create table like feature's bug

От
"dengkai"
Дата:
hi, all
  When I use 'create table like' command to create a table with 'including indexes' options, database return
an unexpected result. Look at the following sql statement.

postgres=# create table t1(c1 int, c2 char(10));

CREATE TABLE

postgres=# create index idx1 on t1(c1);

CREATE INDEX

postgres=# \d+ t1

                                               Table "public.t1"

 Column |     Type      | Collation | Nullable | Default | Storage  | Compression | Stats target | Description

--------+---------------+-----------+----------+---------+----------+-------------+--------------+-------------

 c1     | integer       |           |          |         | plain    |             |              |

 c2     | character(10) |           |          |         | extended |             |              |

Indexes:

    "idx1" btree (c1)

Access method: heap


postgres=# create table t2(like t1 including indexes);

CREATE TABLE

postgres=# \d+ t2

                                               Table "public.t2"

 Column |     Type      | Collation | Nullable | Default | Storage  | Compression | Stats target | Description

--------+---------------+-----------+----------+---------+----------+-------------+--------------+-------------

 c1     | integer       |           |          |         | plain    |             |              |

 c2     | character(10) |           |          |         | extended |             |              |

Indexes:

    "t2_c1_idx" btree (c1)

Access method: heap


postgres=# select version();

                                                  version                                                   

------------------------------------------------------------------------------------------------------------

 PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit

(1 row)


I did not use 'including storage' option in 'create table like' statement, but there is an extended attribute on t2.c2.
It seems that the 'including storage' option is not effective, the default behavior of database is to bring the storage
attribute from original table columns to the new table.

I found this phenomenon on pg15.5 version. And it should also be present on pg18. Is this a bug？
I look forward to your reply.

best regards
dengkai
 

Re: [bug report] About create table like feature's bug

От
Laurenz Albe
Дата:
On Fri, 2025-10-31 at 15:14 +0800, dengkai wrote:
> When I use 'create table like' command to create a table with 'including indexes' options, database return
> an unexpected result. Look at the following sql statement.
>
> postgres=# create table t1(c1 int, c2 char(10));
> CREATE TABLE
> postgres=# create index idx1 on t1(c1);
> CREATE INDEX
> postgres=# \d+ t1
>                                                Table "public.t1"
>  Column |     Type      | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
> --------+---------------+-----------+----------+---------+----------+-------------+--------------+-------------
>  c1     | integer       |           |          |         | plain    |             |              |
>  c2     | character(10) |           |          |         | extended |             |              |
> Indexes:
>     "idx1" btree (c1)
> Access method: heap
>
> postgres=# create table t2(like t1 including indexes);
> CREATE TABLE
> postgres=# \d+ t2
>                                                Table "public.t2"
>  Column |     Type      | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
> --------+---------------+-----------+----------+---------+----------+-------------+--------------+-------------
>  c1     | integer       |           |          |         | plain    |             |              |
>  c2     | character(10) |           |          |         | extended |             |              |
> Indexes:
>     "t2_c1_idx" btree (c1)
> Access method: heap
>
> postgres=# select version();
>                                                   version                                                   
> ------------------------------------------------------------------------------------------------------------
>  PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
> (1 row)

That's an alpha or beta version of an old release.  Bad choice.

> I did not use 'including storage' option in 'create table like' statement, but there is an extended attribute on
t2.c2.
> It seems that the 'including storage' option is not effective, the default behavior of database is to bring the
storage 
> attribute from original table columns to the new table.
>
> I found this phenomenon on pg15.5 version. And it should also be present on pg18. Is this a bug？

No, that is not a bug.  EXTENDED is the default storage method.
See this example:

test=> CREATE TABLE t (c text STORAGE EXTERNAL);
CREATE TABLE
test=> \d+ t
                                          Table "laurenz.t"
 Column │ Type │ Collation │ Nullable │ Default │ Storage  │ Compression │ Stats target │ Description
════════╪══════╪═══════════╪══════════╪═════════╪══════════╪═════════════╪══════════════╪═════════════
 c      │ text │           │          │         │ external │             │              │
Access method: heap

test=> CREATE TABLE t1 (LIKE t);
CREATE TABLE
test=> \d+ t1
                                          Table "laurenz.t1"
 Column │ Type │ Collation │ Nullable │ Default │ Storage  │ Compression │ Stats target │ Description
════════╪══════╪═══════════╪══════════╪═════════╪══════════╪═════════════╪══════════════╪═════════════
 c      │ text │           │          │         │ extended │             │              │
Access method: heap

Yours,
Laurenz Albe

回复： [bug report] About create table like feature's bug

От
"dengkai"
Дата:
ok， thanks for your reply.

 


------------------ 原始邮件 ------------------
发件人: "Laurenz Albe" <laurenz.albe@cybertec.at>;
发送时间: 2025年10月31日(星期五) 下午4:50
收件人: "dengkai"<784523565@qq.com>;"pgsql-bugs"<pgsql-bugs@lists.postgresql.org>;
主题: Re: [bug report] About create table like feature's bug

On Fri, 2025-10-31 at 15:14 +0800, dengkai wrote:
> When I use 'create table like' command to create a table with 'including indexes' options, database return
> an unexpected result. Look at the following sql statement.
>
> postgres=# create table t1(c1 int, c2 char(10));
> CREATE TABLE
> postgres=# create index idx1 on t1(c1);
> CREATE INDEX
> postgres=# \d+ t1
>                                                Table "public.t1"
>  Column |     Type      | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
> --------+---------------+-----------+----------+---------+----------+-------------+--------------+-------------
>  c1     | integer       |           |          |         | plain    |             |              |
>  c2     | character(10) |           |          |         | extended |             |              |
> Indexes:
>     "idx1" btree (c1)
> Access method: heap
>
> postgres=# create table t2(like t1 including indexes);
> CREATE TABLE
> postgres=# \d+ t2
>                                                Table "public.t2"
>  Column |     Type      | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
> --------+---------------+-----------+----------+---------+----------+-------------+--------------+-------------
>  c1     | integer       |           |          |         | plain    |             |              |
>  c2     | character(10) |           |          |         | extended |             |              |
> Indexes:
>     "t2_c1_idx" btree (c1)
> Access method: heap
>
> postgres=# select version();
>                                                   version                                                   
> ------------------------------------------------------------------------------------------------------------
>  PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
> (1 row)

That's an alpha or beta version of an old release.  Bad choice.

> I did not use 'including storage' option in 'create table like' statement, but there is an extended attribute on t2.c2.
> It seems that the 'including storage' option is not effective, the default behavior of database is to bring the storage
> attribute from original table columns to the new table.
>
> I found this phenomenon on pg15.5 version. And it should also be present on pg18. Is this a bug？

No, that is not a bug.  EXTENDED is the default storage method.
See this example:

test=> CREATE TABLE t (c text STORAGE EXTERNAL);
CREATE TABLE
test=> \d+ t
                                          Table "laurenz.t"
 Column │ Type │ Collation │ Nullable │ Default │ Storage  │ Compression │ Stats target │ Description
════════╪══════╪═══════════╪══════════╪═════════╪══════════╪═════════════╪══════════════╪═════════════
 c      │ text │           │          │         │ external │             │              │
Access method: heap

test=> CREATE TABLE t1 (LIKE t);
CREATE TABLE
test=> \d+ t1
                                          Table "laurenz.t1"
 Column │ Type │ Collation │ Nullable │ Default │ Storage  │ Compression │ Stats target │ Description
════════╪══════╪═══════════╪══════════╪═════════╪══════════╪═════════════╪══════════════╪═════════════
 c      │ text │           │          │         │ extended │             │              │
Access method: heap

Yours,
Laurenz Albe