Horton

Thread

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

    dengkai <784523565@qq.com> — 2025-10-31T07:14:57Z

    hi, all

&nbsp; 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
 
&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"
 
&nbsp;Column | &nbsp; &nbsp; Type&nbsp; &nbsp; &nbsp; | Collation | Nullable | Default | Storage&nbsp; | Compression | Stats target | Description 
 
--------+---------------+-----------+----------+---------+----------+-------------+--------------+-------------
 
&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; | 
 
&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; | 
 
Indexes:
 
&nbsp; &nbsp; "idx1" btree (c1)
 
Access method: heap
 


 
postgres=# create table t2(like t1 including indexes);
 
CREATE TABLE
 
postgres=# \d+ t2
 
&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"
 
&nbsp;Column | &nbsp; &nbsp; Type&nbsp; &nbsp; &nbsp; | Collation | Nullable | Default | Storage&nbsp; | Compression | Stats target | Description 
 
--------+---------------+-----------+----------+---------+----------+-------------+--------------+-------------
 
&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; | 
 
&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; | 
 
Indexes:
 
&nbsp; &nbsp; "t2_c1_idx" btree (c1)
 
Access method: heap




postgres=# select 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; 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;

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

&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

 

(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


dengkai
784523565@qq.com



&nbsp;

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

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-10-31T08:50:07Z

    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

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

    dengkai <784523565@qq.com> — 2025-10-31T09:45:59Z

    ok， thanks for your reply.




dengkai
784523565@qq.com



&nbsp;




------------------&nbsp;原始邮件&nbsp;------------------
发件人:                                                                                                                        "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