Re: About the CREATE TABLE LIKE indexes vs constraints issue

On Wed, 2009-12-23 at 14:55 -0500, Tom Lane wrote:
> I think the most natural reading of the syntax
> would be "INCLUDING INDEXES means to include everything you made
> with CREATE INDEX syntax, while INCLUDING CONSTRAINTS means to
> include everything you made with CONSTRAINT syntax".

Agreed.

> However, it's
> unclear whether that's so much better or more useful as to justify
> a compatibility break. Arguably, lumping all indexes together is
> the most useful behavior in practice.

Probably so. LIKE is shorthand anyway, and I think the most useful thing
in practice would be to get everything (indexes and constraints).

> Another spanner in the works is that foreign-key constraints aren't
> copied.

I see why FKs aren't always copied:

Let's say you have two tables, one with a FK referencing the other. If
you want to create two parallel tables that are like the two originals,
you might want one new table to reference the other new table, rather
than the original.

But if you say "including constraints", the POLA would probably require
copying the FKs, as well.

> Do we want to try to clean this up, or should we leave it alone on
> backwards-compatibility grounds?

I don't have a strong opinion.

It's DDL time only, so the only breakage I can imagine is someone's
version-controlled DDL. I would think that most of the people who use
LIKE are probably doing it as a one-off. So it wouldn't be the end of
the world to break compatibility here.

Honestly, I've never used LIKE in a table definition aside from one-off
design experiments. For that kind of thing, what I want is to just get
everything (except perhaps FKs if the above situation applies), and I
adjust it from there. Are there people out there who use LIKE in their
production schema files?

Regards,
Jeff Davis