Thread

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