Re: CREATE TABLE LIKE INCLUDING INDEXES support

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> It'd be contrary to SQL spec. The UNIQUE constraint takes a list of
>> column names, full stop.

> Does the SQL spec actually specify what happens if you provide an
> non-compliant table definition like this?

It does not. We could accept expressions there, and pray that the SQL
committee never extends the spec syntax in a direction incompatible with
that. That seems like a pretty risky thing to do though.

[ thinks... ] I think actually the prior discussions about this have
suggested importing our CREATE INDEX argument-list syntax into the
UNIQUE/PRIMARY KEY syntax, lock stock and barrel. Between the optional
opclass and sort direction and the slightly klugy requirement to
parenthesize expressions, that seemed clearly at huge risk of putting us
behind the compatibility eight-ball. You could make an argument that
the only plausible way that the committee would extend the syntax would
be to allow plain expressions instead of column names, and thus that
allowing that but not our other extensions would be safe. You might be
right about that ... or not. The fact that the committee still hasn't
made this seemingly-obvious extension makes me wonder if they have plans
we don't know about. There are certainly a ton of bells and whistles
in SQL2003 that're far less useful than this would be.

In any case there will always be features in CREATE INDEX that we
daren't drop into the standard constraint syntax, so the argument
for importing just this one seems a bit weak.

BTW, has anyone checked to see if Oracle and/or DB2 allow expressions
here? I think it's unlikely the committee would do anything to break
the standards compliance of those guys, so we might be safe in following
their lead if there is one.

regards, tom lane