Table inheritance surprise

Folks,

When I do CREATE TABLE foo(LIKE bar INCLUDING CONSTRAINTS), it doesn't
include foreign key constraints (8.3.1). I believe this is surprising
behavior, but maybe not a bug, so I'd like to propose another bit of
syntactic sugar, namely

LIKE [INCLUDING FOREIGN KEYS]

which would do what it looks like it does.

What say?

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On May 7, 2008, at 4:52 PM, David Fetter wrote:
> When I do CREATE TABLE foo(LIKE bar INCLUDING CONSTRAINTS), it doesn't
> include foreign key constraints (8.3.1). I believe this is surprising
> behavior, but maybe not a bug, so I'd like to propose another bit of
> syntactic sugar, namely
>
> LIKE [INCLUDING FOREIGN KEYS]
>
> which would do what it looks like it does.

It's surprising to me that INCLUDING CONSTRAINTS doesn't include FK
constraints... is there a reason not to? Perhaps we should just
change INCLUDING CONSTRAINTS to do that...
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

David Fetter wrote:
> Folks,
>
> When I do CREATE TABLE foo(LIKE bar INCLUDING CONSTRAINTS), it doesn't
> include foreign key constraints (8.3.1). I believe this is surprising
> behavior, but maybe not a bug, so I'd like to propose another bit of
> syntactic sugar, namely
>
> LIKE [INCLUDING FOREIGN KEYS]
>
> which would do what it looks like it does.
>
> What say?

TODO has:

o Allow inherited tables to inherit indexes, UNIQUE constraints,
and primary/foreign keys

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Thu, 2008-06-26 at 18:53 -0400, Bruce Momjian wrote:
> David Fetter wrote:
> > Folks,
> >
> > When I do CREATE TABLE foo(LIKE bar INCLUDING CONSTRAINTS), it doesn't
> > include foreign key constraints (8.3.1). I believe this is surprising
> > behavior, but maybe not a bug, so I'd like to propose another bit of
> > syntactic sugar, namely
> >
> > LIKE [INCLUDING FOREIGN KEYS]
> >
> > which would do what it looks like it does.
> >
> > What say?
>
> TODO has:
>
> o Allow inherited tables to inherit indexes, UNIQUE constraints,
> and primary/foreign keys

INCLUDING CONSTRAINTS is non-standard so maybe we can just make
INCLUDING CONSTRAINTS also include foreign keys.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> On Thu, 2008-06-26 at 18:53 -0400, Bruce Momjian wrote:
>> TODO has:
>>
>> o Allow inherited tables to inherit indexes, UNIQUE constraints,
>> and primary/foreign keys

But that TODO item is about inheritance, which has approximately
zip to do with CREATE TABLE LIKE.

> INCLUDING CONSTRAINTS is non-standard so maybe we can just make
> INCLUDING CONSTRAINTS also include foreign keys.

In a green field that would probably make sense, but it'd create
a significant backward compatibility problem to do it now.
Undoubtedly there are apps depending on the current behavior of
LIKE INCLUDING CONSTRAINTS. (If there are not, then the feature is
useless and we should take it out instead of extending it.)

regards, tom lane