public inbox for [email protected]
help / color / mirror / Atom feedFrom: Chao Li <[email protected]>
To: Fujii Masao <[email protected]>
Cc: Robert Treat <[email protected]>
Cc: Postgres hackers <[email protected]>
Subject: Re: DOC: fixes multiple errors in alter table doc
Date: Tue, 3 Mar 2026 10:09:11 +0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CAEoWx2n6ShLMOnjOtf63TjjgGbgiTVT5OMsSOFmbjGb6Xue1Bw@mail.gmail.com>
<CABV9wwMPmo-dg=oV+NaJGa_==uo5+TTvk58aNtjVQZBtMcRVBw@mail.gmail.com>
<CAEoWx2kkh5kCZwYOYaOVShjh3un2mJTGrcFZyhJXd320PHYfJw@mail.gmail.com>
<CAJSLCQ10_gia2-GzQndwoM-n-v2DWz4LnAY5jyO2ZmNiE+56tA@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAEoWx2mOW=JMjSNrNsc3b-+8w0wbT2f6FSsyudD_Q-0pe_fkfw@mail.gmail.com>
<CAHGQGwFk=rrhrwGwPtQxBesbT4DzSZ86Q3ftcwCu3AR5bOiXLw@mail.gmail.com>
<[email protected]>
> On Mar 2, 2026, at 17:22, Chao Li <[email protected]> wrote:
>
>
>
>> On Mar 2, 2026, at 17:04, Fujii Masao <[email protected]> wrote:
>>
>> On Thu, Jan 22, 2026 at 6:38 PM Chao Li <[email protected]> wrote:
>>> PFA v3: Rebased, and added reviewer and discussion information.
>>
>> LGTM.
>>
>> Should we apply the same change to the ALTER FOREIGN TABLE docs as well?
>
> Sure, I can make the change.
>
>>
>> While reviewing that section, I noticed that ADD COLUMN IF NOT EXISTS appears
>> to work for ALTER FOREIGN TABLE, but it isn't documented. I'm not sure why
>> it was left out, but perhaps we should document it and add a regression test in
>> a separate patch?
>>
>
> I will verify that, then update the doc and regression test accordingly.
>
> Best regards,
> --
> Chao Li (Evan)
> HighGo Software Co., Ltd.
> https://www.highgo.com/
>
PFA v4:
0001 - alter table related changes
* Add test cases for omitting COLUMN of ALTER TABLE ADD/DROP [COLUMN]
* Add a test case for ADD COLUMN IF NOT EXIST (already had DROP COLUMN IF EXIST)
0002 - alter foreign table related changes
* doc: Add IF NOT EXSTS for ADD COLUMN
* doc: Mark COLUMN as optional for ADD/DROP COLUMN in the same way as 0001
* Add test cases for omitting COLUMN of ALTER FOREIGN TABLE ADD/DROP [COLUMN]
* Add a test cases for for ADD COLUMN IF NOT EXIST
* Add a test cases for for DROP COLUMN IF EXIST
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Attachments:
[application/octet-stream] v4-0001-doc-clarify-optional-COLUMN-in-ALTER-TABLE-ADD-DR.patch (3.6K, ../[email protected]/2-v4-0001-doc-clarify-optional-COLUMN-in-ALTER-TABLE-ADD-DR.patch)
download | inline diff:
From 5fa11495a4e7b10755a4017d8e1498435ae3e507 Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Tue, 3 Mar 2026 09:50:54 +0800
Subject: [PATCH v4 1/2] doc: clarify optional COLUMN in ALTER TABLE ADD/DROP
Adjust the ALTER TABLE reference page to reflect that the COLUMN keyword
is optional for ADD and DROP. The syntax was already accepted by the
parser, but the documentation only showed the COLUMN form.
Add regression tests covering:
- ALTER TABLE ADD without COLUMN
- ALTER TABLE ADD IF NOT EXISTS
- ALTER TABLE DROP without COLUMN
Author: Chao Li <[email protected]>
Reviewed-by: Fujii Masao <[email protected]>
Reviewed-by: Robert Treat <[email protected]>
Discussion: https://postgr.es/m/CAEoWx2n6ShLMOnjOtf63TjjgGbgiTVT5OMsSOFmbjGb6Xue1Bw@mail.gmail.com
---
doc/src/sgml/ref/alter_table.sgml | 4 ++--
src/test/regress/expected/alter_table.out | 10 ++++++++++
src/test/regress/sql/alter_table.sql | 8 ++++++++
3 files changed, 20 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index aab2c6eb19f..00817e90360 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -163,7 +163,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<variablelist>
<varlistentry id="sql-altertable-desc-add-column">
- <term><literal>ADD COLUMN [ IF NOT EXISTS ]</literal></term>
+ <term><literal>ADD [ COLUMN ] [ IF NOT EXISTS ]</literal></term>
<listitem>
<para>
This form adds a new column to the table, using the same syntax as
@@ -175,7 +175,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</varlistentry>
<varlistentry id="sql-altertable-desc-drop-column">
- <term><literal>DROP COLUMN [ IF EXISTS ]</literal></term>
+ <term><literal>DROP [ COLUMN ] [ IF EXISTS ]</literal></term>
<listitem>
<para>
This form drops a column from a table. Indexes and
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index ac1a7345d0f..5998c670aa3 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -3849,6 +3849,16 @@ Referenced by:
ALTER TABLE test_add_column
ADD COLUMN IF NOT EXISTS c5 SERIAL CHECK (c5 > 10);
NOTICE: column "c5" of relation "test_add_column" already exists, skipping
+ALTER TABLE test_add_column
+ ADD c6 integer; -- omit COLUMN
+ALTER TABLE test_add_column
+ ADD IF NOT EXISTS c6 integer;
+NOTICE: column "c6" of relation "test_add_column" already exists, skipping
+ALTER TABLE test_add_column
+ DROP c6; -- omit COLUMN
+ALTER TABLE test_add_column
+ DROP IF EXISTS c6;
+NOTICE: column "c6" of relation "test_add_column" does not exist, skipping
\d test_add_column*
Table "public.test_add_column"
Column | Type | Collation | Nullable | Default
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 417202430a5..d6b6381ae5c 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -2331,6 +2331,14 @@ ALTER TABLE test_add_column
\d test_add_column
ALTER TABLE test_add_column
ADD COLUMN IF NOT EXISTS c5 SERIAL CHECK (c5 > 10);
+ALTER TABLE test_add_column
+ ADD c6 integer; -- omit COLUMN
+ALTER TABLE test_add_column
+ ADD IF NOT EXISTS c6 integer;
+ALTER TABLE test_add_column
+ DROP c6; -- omit COLUMN
+ALTER TABLE test_add_column
+ DROP IF EXISTS c6;
\d test_add_column*
DROP TABLE test_add_column;
\d test_add_column*
--
2.50.1 (Apple Git-155)
[application/octet-stream] v4-0002-doc-clarify-ALTER-FOREIGN-TABLE-ADD-DROP-COLUMN-s.patch (8.0K, ../[email protected]/3-v4-0002-doc-clarify-ALTER-FOREIGN-TABLE-ADD-DROP-COLUMN-s.patch)
download | inline diff:
From d828ab6dfb764230098a7afaa349260b13125e4e Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Tue, 3 Mar 2026 09:51:41 +0800
Subject: [PATCH v4 2/2] doc: clarify ALTER FOREIGN TABLE ADD/DROP COLUMN
syntax
Update the ALTER FOREIGN TABLE reference page to reflect that:
- COLUMN is optional for ADD and DROP.
- IF NOT EXISTS is supported for ADD.
The syntax has long allowed these forms, but the documentation did not
fully reflect them.
Add regression tests for ALTER FOREIGN TABLE covering:
- ADD without COLUMN.
- ADD IF NOT EXISTS behavior.
- DROP without COLUMN.
- DROP IF EXISTS behavior.
Suggested-by: Fujii Masao <[email protected]>
Author: Chao Li <[email protected]>
Reviewed-by: Fujii Masao <[email protected]>
Discussion: https://postgr.es/m/CAEoWx2n6ShLMOnjOtf63TjjgGbgiTVT5OMsSOFmbjGb6Xue1Bw@mail.gmail.com
---
doc/src/sgml/ref/alter_foreign_table.sgml | 8 +++++---
src/test/regress/expected/foreign_data.out | 8 ++++++++
src/test/regress/sql/foreign_data.sql | 5 +++++
3 files changed, 18 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/ref/alter_foreign_table.sgml b/doc/src/sgml/ref/alter_foreign_table.sgml
index e6d99e99016..228067f087c 100644
--- a/doc/src/sgml/ref/alter_foreign_table.sgml
+++ b/doc/src/sgml/ref/alter_foreign_table.sgml
@@ -32,7 +32,7 @@ ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceab
<phrase>where <replaceable class="parameter">action</replaceable> is one of:</phrase>
- ADD [ COLUMN ] <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ]
+ ADD [ COLUMN ] [ IF NOT EXISTS ] <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ]
DROP [ COLUMN ] [ IF EXISTS ] <replaceable class="parameter">column_name</replaceable> [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> [ SET DATA ] TYPE <replaceable class="parameter">data_type</replaceable> [ COLLATE <replaceable class="parameter">collation</replaceable> ]
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET DEFAULT <replaceable class="parameter">expression</replaceable>
@@ -67,11 +67,13 @@ ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceab
<variablelist>
<varlistentry id="sql-alterforeigntable-desc-add-column">
- <term><literal>ADD COLUMN</literal></term>
+ <term><literal>ADD [ COLUMN ] [ IF NOT EXISTS ]</literal></term>
<listitem>
<para>
This form adds a new column to the foreign table, using the same syntax as
<link linkend="sql-createforeigntable"><command>CREATE FOREIGN TABLE</command></link>.
+ If <literal>IF NOT EXISTS</literal> is specified and a column already
+ exists with this name, no error is thrown.
Unlike the case when adding a column to a regular table, nothing happens
to the underlying storage: this action simply declares that
some new column is now accessible through the foreign table.
@@ -80,7 +82,7 @@ ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceab
</varlistentry>
<varlistentry id="sql-alterforeigntable-desc-drop-column">
- <term><literal>DROP COLUMN [ IF EXISTS ]</literal></term>
+ <term><literal>DROP [ COLUMN ] [ IF EXISTS ]</literal></term>
<listitem>
<para>
This form drops a column from a foreign table.
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index cce49e509ab..8bbf6a2d3dd 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -827,11 +827,15 @@ COMMENT ON COLUMN ft1.c1 IS 'foreign column';
COMMENT ON COLUMN ft1.c1 IS NULL;
ALTER FOREIGN TABLE ft1 ADD COLUMN c4 integer;
ALTER FOREIGN TABLE ft1 ADD COLUMN c5 integer DEFAULT 0;
+ALTER FOREIGN TABLE ft1 ADD COLUMN IF NOT EXISTS c5 integer;
+NOTICE: column "c5" of relation "ft1" already exists, skipping
ALTER FOREIGN TABLE ft1 ADD COLUMN c6 integer;
ALTER FOREIGN TABLE ft1 ADD COLUMN c7 integer NOT NULL;
ALTER FOREIGN TABLE ft1 ADD COLUMN c8 integer;
ALTER FOREIGN TABLE ft1 ADD COLUMN c9 integer;
ALTER FOREIGN TABLE ft1 ADD COLUMN c10 integer OPTIONS (p1 'v1');
+ALTER FOREIGN TABLE ft1 ADD c12 integer; -- omit COLUMN
+ALTER FOREIGN TABLE ft1 DROP c12; -- omit COLUMN
ALTER FOREIGN TABLE ft1 ALTER COLUMN c4 SET DEFAULT 0;
ALTER FOREIGN TABLE ft1 ALTER COLUMN c5 DROP DEFAULT;
ALTER FOREIGN TABLE ft1 ALTER COLUMN c6 SET NOT NULL;
@@ -929,6 +933,8 @@ FDW options: (quote '~', "be quoted" 'value', escape '@')
-- alter noexisting table
ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c4 integer;
NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
+ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD c4 integer;
+NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c6 integer;
NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c7 integer NOT NULL;
@@ -962,6 +968,8 @@ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 OPTIONS (DROP delimiter, SET quot
NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP COLUMN IF EXISTS no_column;
NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
+ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP IF EXISTS no_column;
+NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP COLUMN c9;
NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 SET SCHEMA foreign_schema;
diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql
index aa147b14a90..00e71b73376 100644
--- a/src/test/regress/sql/foreign_data.sql
+++ b/src/test/regress/sql/foreign_data.sql
@@ -382,11 +382,14 @@ COMMENT ON COLUMN ft1.c1 IS NULL;
ALTER FOREIGN TABLE ft1 ADD COLUMN c4 integer;
ALTER FOREIGN TABLE ft1 ADD COLUMN c5 integer DEFAULT 0;
+ALTER FOREIGN TABLE ft1 ADD COLUMN IF NOT EXISTS c5 integer;
ALTER FOREIGN TABLE ft1 ADD COLUMN c6 integer;
ALTER FOREIGN TABLE ft1 ADD COLUMN c7 integer NOT NULL;
ALTER FOREIGN TABLE ft1 ADD COLUMN c8 integer;
ALTER FOREIGN TABLE ft1 ADD COLUMN c9 integer;
ALTER FOREIGN TABLE ft1 ADD COLUMN c10 integer OPTIONS (p1 'v1');
+ALTER FOREIGN TABLE ft1 ADD c12 integer; -- omit COLUMN
+ALTER FOREIGN TABLE ft1 DROP c12; -- omit COLUMN
ALTER FOREIGN TABLE ft1 ALTER COLUMN c4 SET DEFAULT 0;
ALTER FOREIGN TABLE ft1 ALTER COLUMN c5 DROP DEFAULT;
@@ -429,6 +432,7 @@ ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1;
-- alter noexisting table
ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c4 integer;
+ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD c4 integer;
ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c6 integer;
ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c7 integer NOT NULL;
ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c8 integer;
@@ -448,6 +452,7 @@ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP CONSTRAINT ft1_c1_check;
ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 OWNER TO regress_test_role;
ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@');
ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP COLUMN IF EXISTS no_column;
+ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP IF EXISTS no_column;
ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP COLUMN c9;
ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 SET SCHEMA foreign_schema;
ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 RENAME c1 TO foreign_column_1;
--
2.50.1 (Apple Git-155)
view thread (11+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: DOC: fixes multiple errors in alter table doc
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox