BUG #17009: create temporary table with like option using same name as persistent table does not create indexes

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: misha_nagel(at)mail(dot)ru
Subject: BUG #17009: create temporary table with like option using same name as persistent table does not create indexes
Date: 2021-05-14 07:30:08
Message-ID: 17009-b364ef2e97142a54@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 17009
Logged by: Mikhail Nagel
Email address: misha_nagel(at)mail(dot)ru
PostgreSQL version: 12.5
Operating system: Debian x64
Description:

test:
create table t_tmp (a int);
create index on t_tmp (a);
create temporary table t_tmp (like t_tmp including all);
select *
from pg_catalog.pg_indexes
where tablename like 't_tmp%';
drop table t_tmp;
drop table t_tmp;

12.5 output:
ce2pg2=> create table t_tmp (a int);
CREATE TABLE
ce2pg2=> create index on t_tmp (a);
CREATE INDEX
ce2pg2=> create temporary table t_tmp (like t_tmp including all);
CREATE TABLE
ce2pg2=> select *
ce2pg2-> from pg_catalog.pg_indexes
ce2pg2-> where tablename like 't_tmp%';
schemaname | tablename | indexname | tablespace |
indexdef
------------+-----------+-------------+------------+---------------------------------------------------------
test2 | t_tmp | t_tmp_a_idx | | CREATE INDEX
t_tmp_a_idx ON public.t_tmp USING btree (a)
(1 строка)

index on temporary table "t_tmp" not created

12.3 and 13.3 output:
postgres=# create table t_tmp (a int);
CREATE TABLE
postgres=# create index on t_tmp (a);
CREATE INDEX
postgres=# create temporary table t_tmp (like t_tmp including all);
CREATE TABLE
postgres=# select *
postgres-# from pg_catalog.pg_indexes
postgres-# where tablename like 't_tmp%';
schemaname | tablename | indexname | tablespace |
indexdef
------------+-----------+--------------+------------+---------------------------------------------------------------
public | t_tmp | t_tmp_a_idx | | CREATE INDEX
t_tmp_a_idx ON public.t_tmp USING btree (a)
pg_temp_4 | t_tmp | t_tmp_a_idx | | CREATE INDEX
t_tmp_a_idx ON pg_temp_4.t_tmp USING btree (a)
(2 строки)

as expected index on temporary table "t_tmp" was created (second row)

workaround
1. use different table name
create temporary table t_other_name (like t_tmp including all);
2. use schema in like option
create temporary table t_tmp (like public.t_tmp including all);

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message David Rowley 2021-05-14 09:46:32 Re: BUG #17009: create temporary table with like option using same name as persistent table does not create indexes
Previous Message Swathi P 2021-05-14 07:07:32 Re: Query on postgres_fdw extension