Re: Dropping a column on parent table doesn't propagate to children?

I have a skeleton table tb_audit_event that has a bunch of descendant
tables.
I want to remove the pk column from this table and all of the descendant
tables, however issuing an ALTER TABLE tb_audit_event DROP COLUMN
audit_event drops the column only from the parent table.

The docs said that the descendant tables' columns would be removed unless
they had had their own definition for that column. I'm not sure what that
means, but the descendant tables were created using "like tb_audit_event"
to inherit the columns.
Any idea why the descendant columns are not dropping along with the parent?

Here are the table descriptions followed by my table alter and check:

postgres(at)zeus=>hera:ises=# \d tb_audit_event

Table "public.tb_audit_event"
Column | Type |
Modifiers
----------------+-----------------------------+---------------------------------------------------------
audit_event | integer | not null default
nextval('sq_pk_audit_event'::regclass)
audit_field | integer | not null
row_pk_val | integer | not null
recorded | timestamp without time zone | not null default now()
entity | integer | not null
row_op | character(1) | not null
op_sequence | integer | not null
transaction_id | bigint | not null
process_id | integer | not null
old_value | text |
new_value | text |
Indexes:
"tb_audit_event_pkey" PRIMARY KEY, btree (audit_event)
"tb_audit_event_recorded_key" btree (recorded)
"tb_audit_event_transaction_id_key" btree (transaction_id)
Check constraints:
"tb_audit_event_row_op_check" CHECK (row_op = ANY (ARRAY['I'::bpchar,
'U'::bpchar, 'D'::bpchar]))
Foreign-key constraints:
"tb_audit_event_audit_field_fkey" FOREIGN KEY (audit_field) REFERENCES
tb_audit_field(audit_field)
"tb_audit_event_entity_fkey" FOREIGN KEY (entity) REFERENCES
tb_entity(entity)
Triggers:
tr_redirect_audit_events BEFORE INSERT ON tb_audit_event FOR EACH ROW
EXECUTE PROCEDURE fn_redirect_audit_events()
Number of child tables: 17 (Use \d+ to list them.)

postgres(at)zeus=>hera:ises=# \d audit_log.tb_audit_event_20120826_0208
Table "audit_log.tb_audit_event_20120826_0208"
Column | Type |
Modifiers
----------------+-----------------------------+---------------------------------------------------------
audit_event | integer | not null default
nextval('sq_pk_audit_event'::regclass)
audit_field | integer | not null
row_pk_val | integer | not null
recorded | timestamp without time zone | not null default now()
entity | integer | not null
row_op | character(1) | not null
op_sequence | integer | not null
transaction_id | bigint | not null
process_id | integer | not null
old_value | text |
new_value | text |
Indexes:
"tb_audit_event_20120826_0208_pkey" PRIMARY KEY, btree (audit_event)
"tb_audit_event_20120826_0208_recorded_idx" btree (recorded)
"tb_audit_event_20120826_0208_transaction_id_idx" btree (transaction_id)
Check constraints:
"tb_audit_event_20120826_0208_recorded_check" CHECK (recorded >=
'2012-08-19 14:57:49.315938'::timestamp without time zone AND recorded <=
'2012-08-26 14:13:04.133753'::timestamp without time zone)
"tb_audit_event_row_op_check" CHECK (row_op = ANY (ARRAY['I'::bpchar,
'U'::bpchar, 'D'::bpchar]))
Inherits: tb_audit_event

postgres(at)moshe=>devmain:ises=# *alter table tb_audit_event drop column
audit_event;*
ALTER TABLE
postgres(at)moshe=>devmain:ises=# *\d audit_log.tb_audit_event_20120826_0208*
Table "audit_log.tb_audit_event_20120826_0208"
Column | Type |
Modifiers
----------------+-----------------------------+---------------------------------------------------------
audit_event | integer | not null default
nextval('sq_pk_audit_event'::regclass)
audit_field | integer | not null
row_pk_val | integer | not null
recorded | timestamp without time zone | not null default now()
entity | integer | not null
row_op | character(1) | not null
op_sequence | integer | not null
transaction_id | bigint | not null
process_id | integer | not null
old_value | text |
new_value | text |
Indexes:
"tb_audit_event_20120826_0208_pkey" PRIMARY KEY, btree (audit_event)
"tb_audit_event_20120826_0208_recorded_idx" btree (recorded)
"tb_audit_event_20120826_0208_transaction_id_idx" btree (transaction_id)
Check constraints:
"tb_audit_event_20120826_0208_recorded_check" CHECK (recorded >=
'2012-08-26 14:26:55.761958'::timestamp without time zone AND rec

orded <= '2012-08-26
14:45:35.989979'::timestamp without time zone)
"tb_audit_event_row_op_check" CHECK (row_op = ANY (ARRAY['I'::bpchar,
'U'::bpchar, 'D'::bpchar]))
Inherits: tb_audit_event

Also, another question about the docs. The syntax for the ALTER TABLE
command starts as follows:

ALTER TABLE [ ONLY ] name [ * ]

What is the asterisk? It is not explained anywhere on that page.

Thanks,
Moshe

--
Moshe Jacobson
Nead Werx, Inc. | Senior Systems Engineer
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe(at)neadwerx(dot)com | www.neadwerx.com

On Wed, Aug 29, 2012 at 12:26 AM, Moshe Jacobson <moshe(at)neadwerx(dot)com> wrote:
> The docs said that the descendant tables' columns would be removed unless
> they had had their own definition for that column. I'm not sure what that

It means that when you DEFINE columns in the inherited table they will
be independent from the parent table. So anything you do with the
columns in the parent table will not affect such columns in the
inherited one.

> means, but the descendant tables were created using "like tb_audit_event" to
> inherit the columns.

When LIKE table_name is specified in the definition of a table it
means that all the columns will be DEFINED in the new table. You
should not specify LIKE table_name in the definition of the inherited
tables to make the columns to be INHERITED instead of DEFINED.

Here you will find a very good explanation of the inheritance
http://www.postgresql.org/docs/9.1/interactive/ddl-inherit.html

> Any idea why the descendant columns are not dropping along with the parent?
>
> Here are the table descriptions followed by my table alter and check:
>
> postgres(at)zeus=>hera:ises=# \d tb_audit_event
>
> Table "public.tb_audit_event"
> Column | Type |
> Modifiers
> ----------------+-----------------------------+---------------------------------------------------------
> audit_event | integer | not null default
> nextval('sq_pk_audit_event'::regclass)
> audit_field | integer | not null
> row_pk_val | integer | not null
> recorded | timestamp without time zone | not null default now()
> entity | integer | not null
> row_op | character(1) | not null
> op_sequence | integer | not null
> transaction_id | bigint | not null
> process_id | integer | not null
> old_value | text |
> new_value | text |
> Indexes:
> "tb_audit_event_pkey" PRIMARY KEY, btree (audit_event)
> "tb_audit_event_recorded_key" btree (recorded)
> "tb_audit_event_transaction_id_key" btree (transaction_id)
> Check constraints:
> "tb_audit_event_row_op_check" CHECK (row_op = ANY (ARRAY['I'::bpchar,
> 'U'::bpchar, 'D'::bpchar]))
> Foreign-key constraints:
> "tb_audit_event_audit_field_fkey" FOREIGN KEY (audit_field) REFERENCES
> tb_audit_field(audit_field)
> "tb_audit_event_entity_fkey" FOREIGN KEY (entity) REFERENCES
> tb_entity(entity)
> Triggers:
> tr_redirect_audit_events BEFORE INSERT ON tb_audit_event FOR EACH ROW
> EXECUTE PROCEDURE fn_redirect_audit_events()
> Number of child tables: 17 (Use \d+ to list them.)
>
>
>
> postgres(at)zeus=>hera:ises=# \d audit_log.tb_audit_event_20120826_0208
> Table "audit_log.tb_audit_event_20120826_0208"
> Column | Type |
> Modifiers
> ----------------+-----------------------------+---------------------------------------------------------
> audit_event | integer | not null default
> nextval('sq_pk_audit_event'::regclass)
> audit_field | integer | not null
> row_pk_val | integer | not null
> recorded | timestamp without time zone | not null default now()
> entity | integer | not null
> row_op | character(1) | not null
> op_sequence | integer | not null
> transaction_id | bigint | not null
> process_id | integer | not null
> old_value | text |
> new_value | text |
> Indexes:
> "tb_audit_event_20120826_0208_pkey" PRIMARY KEY, btree (audit_event)
> "tb_audit_event_20120826_0208_recorded_idx" btree (recorded)
> "tb_audit_event_20120826_0208_transaction_id_idx" btree (transaction_id)
> Check constraints:
> "tb_audit_event_20120826_0208_recorded_check" CHECK (recorded >=
> '2012-08-19 14:57:49.315938'::timestamp without time zone AND recorded <=
> '2012-08-26 14:13:04.133753'::timestamp without time zone)
> "tb_audit_event_row_op_check" CHECK (row_op = ANY (ARRAY['I'::bpchar,
> 'U'::bpchar, 'D'::bpchar]))
> Inherits: tb_audit_event
>
>
>
> postgres(at)moshe=>devmain:ises=# alter table tb_audit_event drop column
> audit_event;
> ALTER TABLE
> postgres(at)moshe=>devmain:ises=# \d audit_log.tb_audit_event_20120826_0208
> Table "audit_log.tb_audit_event_20120826_0208"
> Column | Type |
> Modifiers
> ----------------+-----------------------------+---------------------------------------------------------
> audit_event | integer | not null default
> nextval('sq_pk_audit_event'::regclass)
> audit_field | integer | not null
> row_pk_val | integer | not null
> recorded | timestamp without time zone | not null default now()
> entity | integer | not null
> row_op | character(1) | not null
> op_sequence | integer | not null
> transaction_id | bigint | not null
> process_id | integer | not null
> old_value | text |
> new_value | text |
> Indexes:
> "tb_audit_event_20120826_0208_pkey" PRIMARY KEY, btree (audit_event)
> "tb_audit_event_20120826_0208_recorded_idx" btree (recorded)
> "tb_audit_event_20120826_0208_transaction_id_idx" btree (transaction_id)
> Check constraints:
> "tb_audit_event_20120826_0208_recorded_check" CHECK (recorded >=
> '2012-08-26 14:26:55.761958'::timestamp without time zone AND rec
> orded <= '2012-08-26 14:45:35.989979'::timestamp without time zone)
> "tb_audit_event_row_op_check" CHECK (row_op = ANY (ARRAY['I'::bpchar,
> 'U'::bpchar, 'D'::bpchar]))
> Inherits: tb_audit_event
>
>
>
>
> Also, another question about the docs. The syntax for the ALTER TABLE
> command starts as follows:
>
> ALTER TABLE [ ONLY ] name [ * ]
>
> What is the asterisk? It is not explained anywhere on that page.
>
> Thanks,
> Moshe
>
> --
> Moshe Jacobson
> Nead Werx, Inc. | Senior Systems Engineer
> 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
> moshe(at)neadwerx(dot)com | www.neadwerx.com
>

--
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com

Jabber: gray(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +79160686204

Thanks Sergey.

I create these inherited tables from the main table, and then move them
into a different schema that is backed up separately from the main schema,
since they are for audit logging, which gets very big.

Questions:
1. If I want the inherited table's columns indexed the same way as the
parent, must I create new indexes on the inherited table?
2. If I move the inherited table to a new schema, will its indexes also be
moved into the new schema?
3. Any difference in behavior regarding check constraints, schemas,
indexes, etc that I should be aware of between inherited tables created
with pure inheritance as opposed to "like <parent_table>"?

Thank you.

On Wed, Aug 29, 2012 at 4:35 AM, Sergey Konoplev <
sergey(dot)konoplev(at)postgresql-consulting(dot)com> wrote:

> On Wed, Aug 29, 2012 at 12:26 AM, Moshe Jacobson <moshe(at)neadwerx(dot)com>
> wrote:
> > The docs said that the descendant tables' columns would be removed unless
> > they had had their own definition for that column. I'm not sure what that
>
> It means that when you DEFINE columns in the inherited table they will
> be independent from the parent table. So anything you do with the
> columns in the parent table will not affect such columns in the
> inherited one.
>
> > means, but the descendant tables were created using "like
> tb_audit_event" to
> > inherit the columns.
>
> When LIKE table_name is specified in the definition of a table it
> means that all the columns will be DEFINED in the new table. You
> should not specify LIKE table_name in the definition of the inherited
> tables to make the columns to be INHERITED instead of DEFINED.
>
> Here you will find a very good explanation of the inheritance
> http://www.postgresql.org/docs/9.1/interactive/ddl-inherit.html
>
> > Any idea why the descendant columns are not dropping along with the
> parent?
> >
> > Here are the table descriptions followed by my table alter and check:
> >
> > postgres(at)zeus=>hera:ises=# \d tb_audit_event
> >
> > Table "public.tb_audit_event"
> > Column | Type |
> > Modifiers
> >
> ----------------+-----------------------------+---------------------------------------------------------
> > audit_event | integer | not null default
> > nextval('sq_pk_audit_event'::regclass)
> > audit_field | integer | not null
> > row_pk_val | integer | not null
> > recorded | timestamp without time zone | not null default now()
> > entity | integer | not null
> > row_op | character(1) | not null
> > op_sequence | integer | not null
> > transaction_id | bigint | not null
> > process_id | integer | not null
> > old_value | text |
> > new_value | text |
> > Indexes:
> > "tb_audit_event_pkey" PRIMARY KEY, btree (audit_event)
> > "tb_audit_event_recorded_key" btree (recorded)
> > "tb_audit_event_transaction_id_key" btree (transaction_id)
> > Check constraints:
> > "tb_audit_event_row_op_check" CHECK (row_op = ANY (ARRAY['I'::bpchar,
> > 'U'::bpchar, 'D'::bpchar]))
> > Foreign-key constraints:
> > "tb_audit_event_audit_field_fkey" FOREIGN KEY (audit_field)
> REFERENCES
> > tb_audit_field(audit_field)
> > "tb_audit_event_entity_fkey" FOREIGN KEY (entity) REFERENCES
> > tb_entity(entity)
> > Triggers:
> > tr_redirect_audit_events BEFORE INSERT ON tb_audit_event FOR EACH ROW
> > EXECUTE PROCEDURE fn_redirect_audit_events()
> > Number of child tables: 17 (Use \d+ to list them.)
> >
> >
> >
> > postgres(at)zeus=>hera:ises=# \d audit_log.tb_audit_event_20120826_0208
> > Table
> "audit_log.tb_audit_event_20120826_0208"
> > Column | Type |
> > Modifiers
> >
> ----------------+-----------------------------+---------------------------------------------------------
> > audit_event | integer | not null default
> > nextval('sq_pk_audit_event'::regclass)
> > audit_field | integer | not null
> > row_pk_val | integer | not null
> > recorded | timestamp without time zone | not null default now()
> > entity | integer | not null
> > row_op | character(1) | not null
> > op_sequence | integer | not null
> > transaction_id | bigint | not null
> > process_id | integer | not null
> > old_value | text |
> > new_value | text |
> > Indexes:
> > "tb_audit_event_20120826_0208_pkey" PRIMARY KEY, btree (audit_event)
> > "tb_audit_event_20120826_0208_recorded_idx" btree (recorded)
> > "tb_audit_event_20120826_0208_transaction_id_idx" btree
> (transaction_id)
> > Check constraints:
> > "tb_audit_event_20120826_0208_recorded_check" CHECK (recorded >=
> > '2012-08-19 14:57:49.315938'::timestamp without time zone AND recorded <=
> > '2012-08-26 14:13:04.133753'::timestamp without time zone)
> > "tb_audit_event_row_op_check" CHECK (row_op = ANY (ARRAY['I'::bpchar,
> > 'U'::bpchar, 'D'::bpchar]))
> > Inherits: tb_audit_event
> >
> >
> >
> > postgres(at)moshe=>devmain:ises=# alter table tb_audit_event drop column
> > audit_event;
> > ALTER TABLE
> > postgres(at)moshe=>devmain:ises=# \d audit_log.tb_audit_event_20120826_0208
> > Table
> "audit_log.tb_audit_event_20120826_0208"
> > Column | Type |
> > Modifiers
> >
> ----------------+-----------------------------+---------------------------------------------------------
> > audit_event | integer | not null default
> > nextval('sq_pk_audit_event'::regclass)
> > audit_field | integer | not null
> > row_pk_val | integer | not null
> > recorded | timestamp without time zone | not null default now()
> > entity | integer | not null
> > row_op | character(1) | not null
> > op_sequence | integer | not null
> > transaction_id | bigint | not null
> > process_id | integer | not null
> > old_value | text |
> > new_value | text |
> > Indexes:
> > "tb_audit_event_20120826_0208_pkey" PRIMARY KEY, btree (audit_event)
> > "tb_audit_event_20120826_0208_recorded_idx" btree (recorded)
> > "tb_audit_event_20120826_0208_transaction_id_idx" btree
> (transaction_id)
> > Check constraints:
> > "tb_audit_event_20120826_0208_recorded_check" CHECK (recorded >=
> > '2012-08-26 14:26:55.761958'::timestamp without time zone AND rec
> > orded <= '2012-08-26 14:45:35.989979'::timestamp without time zone)
> > "tb_audit_event_row_op_check" CHECK (row_op = ANY (ARRAY['I'::bpchar,
> > 'U'::bpchar, 'D'::bpchar]))
> > Inherits: tb_audit_event
> >
> >
> >
> >
> > Also, another question about the docs. The syntax for the ALTER TABLE
> > command starts as follows:
> >
> > ALTER TABLE [ ONLY ] name [ * ]
> >
> > What is the asterisk? It is not explained anywhere on that page.
> >
> > Thanks,
> > Moshe
> >
> > --
> > Moshe Jacobson
> > Nead Werx, Inc. | Senior Systems Engineer
> > 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
> > moshe(at)neadwerx(dot)com | www.neadwerx.com
> >
>
> --
> Sergey Konoplev
>
> a database architect, software developer at PostgreSQL-Consulting.com
> http://www.postgresql-consulting.com
>
> Jabber: gray(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +79160686204
>

--
Moshe Jacobson
Nead Werx, Inc. | Senior Systems Engineer
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe(at)neadwerx(dot)com | www.neadwerx.com

Le mercredi 29 août 2012 à 09:48 -0400, Moshe Jacobson a écrit :

>
>
> Questions:
> 1. If I want the inherited table's columns indexed the same way as the
> parent, must I create new indexes on the inherited table?
> 2. If I move the inherited table to a new schema, will its indexes
> also be moved into the new schema?
> 3. Any difference in behavior regarding check constraints, schemas,
> indexes, etc that I should be aware of between inherited tables
> created with pure inheritance as opposed to "like <parent_table>"?
>

The 'LIKE' section of the documentation for CREATE TABLE explains it :

http://www.postgresql.org/docs/9.1/static/sql-createtable.html

--
Vincent Veyron
http://marica.fr/
Gestion informatique des sinistres d'assurances et des dossiers contentieux pour le service juridique

On Wed, Aug 29, 2012 at 12:11 PM, Vincent Veyron <vv(dot)lists(at)wanadoo(dot)fr>wrote:

> Le mercredi 29 août 2012 à 09:48 -0400, Moshe Jacobson a écrit :
>
> > Questions:
> > 1. If I want the inherited table's columns indexed the same way as the
> > parent, must I create new indexes on the inherited table?
> > 2. If I move the inherited table to a new schema, will its indexes
> > also be moved into the new schema?
> > 3. Any difference in behavior regarding check constraints, schemas,
> > indexes, etc that I should be aware of between inherited tables
> > created with pure inheritance as opposed to "like <parent_table>"?
> >
>
> The 'LIKE' section of the documentation for CREATE TABLE explains it :
>
> http://www.postgresql.org/docs/9.1/static/sql-createtable.html
>

I don't see the answers to #1 and #2 there...

--
Moshe Jacobson
Nead Werx, Inc. | Senior Systems Engineer
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe(at)neadwerx(dot)com | www.neadwerx.com

Le mercredi 29 août 2012 à 13:05 -0400, Moshe Jacobson a écrit :
> On Wed, Aug 29, 2012 at 12:11 PM, Vincent Veyron <vv(dot)lists(at)wanadoo(dot)fr>
> wrote:
> Le mercredi 29 août 2012 à 09:48 -0400, Moshe Jacobson a
> écrit :
>
> > Questions:
> > 1. If I want the inherited table's columns indexed the same
> way as the
> > parent, must I create new indexes on the inherited table?
> > 2. If I move the inherited table to a new schema, will its
> indexes
> > also be moved into the new schema?
> > 3. Any difference in behavior regarding check constraints,
> schemas,
> > indexes, etc that I should be aware of between inherited
> tables
> > created with pure inheritance as opposed to "like
> <parent_table>"?
> >
>
>
> The 'LIKE' section of the documentation for CREATE TABLE
> explains it :
>
> http://www.postgresql.org/docs/9.1/static/sql-createtable.html
>
>
> I don't see the answers to #1 and #2 there...
>

#1
Quote :

Any indexes on the original table will not be created on the new table,
unless the INCLUDING INDEXES clause is specified.

#2
I don't use schemas personnally, but if you do, I suggest you test and
see what happens

> --
> Moshe Jacobson
> Nead Werx, Inc. | Senior Systems Engineer
> 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
> moshe(at)neadwerx(dot)com | www.neadwerx.com
>

--
Vincent Veyron
http://marica.fr/
Gestion informatique des sinistres d'assurances et des dossiers contentieux pour le service juridique

On Wed, Aug 29, 2012 at 3:06 PM, Vincent Veyron <vv(dot)lists(at)wanadoo(dot)fr> wrote:

> #1
> Quote :
>
> Any indexes on the original table will not be created on the new table,
> unless the INCLUDING INDEXES clause is specified.
>

This is referring to the behavior on creating a table LIKE another table. I
am specifically asking about NOT using LIKE but just INHERITS.

> #2
>
I don't use schemas personnally, but if you do, I suggest you test and
> see what happens
>

I will.

Also, do you or does anyone know what the asterisk is for, that I asked
about at the end of my original post?

--
Moshe Jacobson
Nead Werx, Inc. | Senior Systems Engineer
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe(at)neadwerx(dot)com | www.neadwerx.com

On Wed, Aug 29, 2012 at 5:48 PM, Moshe Jacobson <moshe(at)neadwerx(dot)com> wrote:
> 1. If I want the inherited table's columns indexed the same way as the
> parent, must I create new indexes on the inherited table?

You must. Indexes are not inheritable.

> 2. If I move the inherited table to a new schema, will its indexes also be
> moved into the new schema?

They will be.

> 3. Any difference in behavior regarding check constraints, schemas, indexes,
> etc that I should be aware of between inherited tables created with pure
> inheritance as opposed to "like <parent_table>"?

AIU you mean pure inheritance vs inheritance + pure LIKE. In this case
I do not see any.

>> > Also, another question about the docs. The syntax for the ALTER TABLE
>> > command starts as follows:
>> >
>> > ALTER TABLE [ ONLY ] name [ * ]
>> >
>> > What is the asterisk? It is not explained anywhere on that page.

As I was told in IRC:

RhodiumToad: grayhemp: opposite of ONLY, only present for historical
reasons since it's the default and has been for decades

so nothing important.

>> >
>> > Thanks,
>> > Moshe
>> >
>> > --
>> > Moshe Jacobson
>> > Nead Werx, Inc. | Senior Systems Engineer
>> > 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
>> > moshe(at)neadwerx(dot)com | www.neadwerx.com
>> >
>>
>> --
>> Sergey Konoplev
>>
>> a database architect, software developer at PostgreSQL-Consulting.com
>> http://www.postgresql-consulting.com
>>
>> Jabber: gray(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +79160686204
>
>
>
>
> --
> Moshe Jacobson
> Nead Werx, Inc. | Senior Systems Engineer
> 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
> moshe(at)neadwerx(dot)com | www.neadwerx.com
>

--
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com

Jabber: gray(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +79160686204

Perfect response, thank you Sergey.

On Thu, Aug 30, 2012 at 7:29 AM, Sergey Konoplev <
sergey(dot)konoplev(at)postgresql-consulting(dot)com> wrote:

> On Wed, Aug 29, 2012 at 5:48 PM, Moshe Jacobson <moshe(at)neadwerx(dot)com>
> wrote:
> > 1. If I want the inherited table's columns indexed the same way as the
> > parent, must I create new indexes on the inherited table?
>
> You must. Indexes are not inheritable.
>
> > 2. If I move the inherited table to a new schema, will its indexes also
> be
> > moved into the new schema?
>
> They will be.
>
> > 3. Any difference in behavior regarding check constraints, schemas,
> indexes,
> > etc that I should be aware of between inherited tables created with pure
> > inheritance as opposed to "like <parent_table>"?
>
> AIU you mean pure inheritance vs inheritance + pure LIKE. In this case
> I do not see any.
>
> >> > Also, another question about the docs. The syntax for the ALTER TABLE
> >> > command starts as follows:
> >> >
> >> > ALTER TABLE [ ONLY ] name [ * ]
> >> >
> >> > What is the asterisk? It is not explained anywhere on that page.
>
> As I was told in IRC:
>
> RhodiumToad: grayhemp: opposite of ONLY, only present for historical
> reasons since it's the default and has been for decades
>
> so nothing important.
>
> >> >
> >> > Thanks,
> >> > Moshe
> >> >
> >> > --
> >> > Moshe Jacobson
> >> > Nead Werx, Inc. | Senior Systems Engineer
> >> > 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
> >> > moshe(at)neadwerx(dot)com | www.neadwerx.com
> >> >
> >>
> >> --
> >> Sergey Konoplev
> >>
> >> a database architect, software developer at PostgreSQL-Consulting.com
> >> http://www.postgresql-consulting.com
> >>
> >> Jabber: gray(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +79160686204
> >
> >
> >
> >
> > --
> > Moshe Jacobson
> > Nead Werx, Inc. | Senior Systems Engineer
> > 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
> > moshe(at)neadwerx(dot)com | www.neadwerx.com
> >
>
>
>
> --
> Sergey Konoplev
>
> a database architect, software developer at PostgreSQL-Consulting.com
> http://www.postgresql-consulting.com
>
> Jabber: gray(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +79160686204
>

--
Moshe Jacobson
Nead Werx, Inc. | Senior Systems Engineer
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe(at)neadwerx(dot)com | www.neadwerx.com