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

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