Обсуждение: alter table docs
I was just looking up alter table add constraint syntax under "current(10)" and we get
ADD table_constraint [ NOT VALID ]
ADD table_constraint_using_index
There is a description below for the using_index version but none for the plain version. There is a block for the plain version on the CREATE TABLE page. Should it not also appear in the ALTER TABLE page?
On 07/30/2018 09:57 AM, Rob Sargent wrote: > I was just looking up alter table add constraint syntax under > "current(10)" and we get > > ADD /table_constraint/ [ NOT VALID ] > ADD /table_constraint_using_index/ > > There is a description below for the using_index version but none for > the plain version. There is a block for the plain version on the CREATE I see one: https://www.postgresql.org/docs/10/static/sql-altertable.html "ADD table_constraint [ NOT VALID ] This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key and CHECK constraints. If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table, in the case of foreign keys; and they'll fail unless the new row matches the specified check constraints). But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option. " > TABLE page. Should it not also appear in the ALTER TABLE page? > > -- Adrian Klaver adrian.klaver@aklaver.com
On 07/30/2018 03:07 PM, Adrian Klaver wrote:
On 07/30/2018 09:57 AM, Rob Sargent wrote:I was just looking up alter table add constraint syntax under "current(10)" and we get
ADD /table_constraint/ [ NOT VALID ]
ADD /table_constraint_using_index/
There is a description below for the using_index version but none for the plain version. There is a block for the plain version on the CREATE
I see one:
https://www.postgresql.org/docs/10/static/sql-altertable.html
"ADD table_constraint [ NOT VALID ]
This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key and CHECK constraints. If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table, in the case of foreign keys; and they'll fail unless the new row matches the specified check constraints). But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option.
"TABLE page. Should it not also appear in the ALTER TABLE page?
OK, I was expecting a block in the enclosing text-area for this simple form of the command similar to the one for the using_index form. I suppose the existence of the latter lead me to expect the former. If it's as intended I'm fine with that.
On 07/30/2018 02:24 PM, Rob Sargent wrote: > > On 07/30/2018 03:07 PM, Adrian Klaver wrote: >> On 07/30/2018 09:57 AM, Rob Sargent wrote: >>> I was just looking up alter table add constraint syntax under >>> "current(10)" and we get >>> >>> ADD /table_constraint/ [ NOT VALID ] >>> ADD /table_constraint_using_index/ >>> >>> There is a description below for the using_index version but none for >>> the plain version. There is a block for the plain version on the CREATE >> >> I see one: >> >> https://www.postgresql.org/docs/10/static/sql-altertable.html >> >> "ADD table_constraint [ NOT VALID ] >> >> This form adds a new constraint to a table using the same syntax >> as CREATE TABLE, plus the option NOT VALID, which is currently only >> allowed for foreign key and CHECK constraints. If the constraint is >> marked NOT VALID, the potentially-lengthy initial check to verify that >> all rows in the table satisfy the constraint is skipped. The >> constraint will still be enforced against subsequent inserts or >> updates (that is, they'll fail unless there is a matching row in the >> referenced table, in the case of foreign keys; and they'll fail unless >> the new row matches the specified check constraints). But the database >> will not assume that the constraint holds for all rows in the table, >> until it is validated by using the VALIDATE CONSTRAINT option. >> " >> >>> TABLE page. Should it not also appear in the ALTER TABLE page? >>> >>> > > OK, I was expecting a block in the enclosing text-area for this simple > form of the command similar to the one for the /using_index/ form. I > suppose the existence of the latter lead me to expect the former. If > it's as intended I'm fine with that. Aah I see, you where referring to: "and table_constraint_using_index is: [ CONSTRAINT constraint_name ] { UNIQUE | PRIMARY KEY } USING INDEX index_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] " > > >> >> > -- Adrian Klaver adrian.klaver@aklaver.com
>> OK, I was expecting a block in the enclosing text-area for this
>> simple form of the command similar to the one for the /using_index/
>> form. I suppose the existence of the latter lead me to expect the
>> former. If it's as intended I'm fine with that.
>
> Aah I see, you where referring to:
>
> "and table_constraint_using_index is:
>
> [ CONSTRAINT constraint_name ]
> { UNIQUE | PRIMARY KEY } USING INDEX index_name
> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY
> IMMEDIATE ]
> "
>
>>
>>
Exactly. That that is in the "box" made me think a similar blurb for
the non-index version should be there also.
Rob Sargent <robjsargent@gmail.com> writes:
> Exactly. That that is in the "box" made me think a similar blurb for
> the non-index version should be there also.
This seems to have been fixed in v11 but not back-patched.
regards, tom lane
I'm not anxious to see it back-patched. On 07/30/2018 04:25 PM, Tom Lane wrote: > Rob Sargent <robjsargent@gmail.com> writes: >> Exactly. That that is in the "box" made me think a similar blurb for >> the non-index version should be there also. > This seems to have been fixed in v11 but not back-patched. > > regards, tom lane