pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: Laurenz Albe <laurenz.albe@cybertec.at>
To: dengkai <784523565@qq.com>
To: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: [bug report] About create table like feature's bug
Date: Fri, 31 Oct 2025 09:50:07 +0100
Message-ID: <79fa0412dfd14c2518b1648e2c81817efe65d15d.camel@cybertec.at> (raw )
In-Reply-To: <tencent_83CB623256F68B1CBE13201DE8C51BFB8006@qq.com >
References: <tencent_83CB623256F68B1CBE13201DE8C51BFB8006@qq.com >
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
view thread (2+ messages)
Message-ID: <79fa0412dfd14c2518b1648e2c81817efe65d15d.camel@cybertec.at>
Permalink: ../79fa0412dfd14c2518b1648e2c81817efe65d15d.camel@cybertec.at/
Also on: postgresql.org/message-id/79fa0412dfd14c2518b1648e2c81817efe65d15d.camel@cybertec.at
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: laurenz.albe@cybertec.at, 784523565@qq.com, pgsql-bugs@lists.postgresql.org
Subject: Re: [bug report] About create table like feature's bug
In-Reply-To: <79fa0412dfd14c2518b1648e2c81817efe65d15d.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox