[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

ok， thanks for your reply.

dengkai
784523565@qq.com

 

------------------ 原始邮件 ------------------
发件人: "Laurenz Albe" <laurenz.albe@cybertec.at&gt;;
发送时间:&nbsp;2025年10月31日(星期五) 下午4:50
收件人:&nbsp;"dengkai"<784523565@qq.com&gt;;"pgsql-bugs"<pgsql-bugs@lists.postgresql.org&gt;;

主题:&nbsp;Re: [bug report] About create table like feature's bug

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

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

&gt; I did not use 'including storage' option in 'create table like' statement, but there is an extended attribute on t2.c2.
&gt; It seems that the 'including storage' option is not effective, the default behavior of database is to bring the storage
&gt; attribute from original table columns to the new table.
&gt;
&gt; 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.&nbsp; EXTENDED is the default storage method.
See this example:

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

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

Yours,
Laurenz Albe