Re: CREATE TABLE LIKE including all not including storage parameters?

On 05/09/2017 11:56, Milen Blagojevic wrote:
> Hi all,
>
> I am using CREATE TABLE LIKE for creating partitions :
>
> Lets say this is my main table:
>
> \d+ test_tabl
> Table "public.test_tabl"
> Column | Type | Modifiers | Storage | Stats target | Description
> --------------+-----------------------------+-----------+----------+--------------+-------------
> id | integer | not null | plain | |
> test_name | character varying(10) | | extended | |
> test_value | numeric(19,3) | | main | |
> time_created | timestamp without time zone | | plain | |
> Indexes:
> "test_tabl_pkey" PRIMARY KEY, btree (id)
> "ix_test_tabl_time_created" btree (time_created)
> Child tables: test_tabl_20170905
> Options: fillfactor=75
>
>
> I am creating new partitions with following query:
>
> create table test_tabl_20170906 (like test_tabl INCLUDING ALL) inherits (test_tabl);
>
> \d+ test_tabl_20170906
> Table "public.test_tabl_20170906"
> Column | Type | Modifiers | Storage | Stats target | Description
> --------------+-----------------------------+-----------+----------+--------------+-------------
> id | integer | not null | plain | |
> test_name | character varying(10) | | extended | |
> test_value | numeric(19,3) | | main | |
> time_created | timestamp without time zone | | plain | |
> Indexes:
> "test_tabl_20170906_pkey" PRIMARY KEY, btree (id)
> "test_tabl_20170906_time_created_idx" btree (time_created)
> Inherits: test_tabl
>
> According to PostgreSQL documentation:
> INCLUDING ALL is an abbreviated form of INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS.
>
> But in this case child table didn't inherit filfactor (behaviour is the same for autovacuum parameters)
>
> Version is 9.4.13:
> version
> ----------------------------------------------------------------------------------------------------------------
> PostgreSQL 9.4.13 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
>
>

Same in PostgreSQL 10beta3 on x86_64-pc-linux-gnu.
testdb=# create table dad(foo text) WITH (fillfactor=99, autovacuum_freeze_max_age=20000000);
CREATE TABLE
testdb=# create table dadkid1 (like dad INCLUDING STORAGE);
CREATE TABLE
testdb=# \d+ dadkid1
Table "public.dadkid1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+------+-----------+----------+---------+----------+--------------+-------------
foo | text | | | | extended | |

> Am I doing something wrong here?
>
> Thanks in advance.
>
> Regards,
> Milen Blagojevic

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt