Обсуждение: Add column with If Not Exists

Hi

I would like to suggest the addition of the "If not exists" to the Add 
Column feature of Postgres.  There are quite common situations where 
ensuring a column exists is important so that an update to remote 
devices will not fail but it is not so important that deprecated fields 
be removed.  This is often the case with backward compatibility.  New 
columns will not affect old systems but allows all remote devices 
running older software to be updated using the same process as new 
devices.  Once the hardware reaches end of life it will be replaced and 
the new hardware will use the new columns.  So having the ability to 
Alter the table with a series of Add Column commands ensures that the 
new records included in the update are added to the table.  Having the 
"if not exists" would remove all of this potential complexity and allows 
a quick and easy method to ensure the column exists in the table using 
simple SQL commands that will not cause errors if the column is already 
present.

I understand that some people will feel this is not the recommended way 
to proceed.  I understand there are lots of ways this can be handled 
differently as I have been working with database of various kinds for 
over 30 years.  All I am suggesting is that this feature would be nice 
to have in some scenarios and thus would like it considered.  I do not 
wish to start a discussion on whether using it is a sound approach or 
not as that is a different discussion.

-- 
Regards,
Simon White
dCipher Computing
705-500-0191

On 08/04/2018 08:56 AM, Simon White wrote:
> Hi
> 
> I would like to suggest the addition of the "If not exists" to the Add 
> Column feature of Postgres.  There are quite common situations where 
> ensuring a column exists is important so that an update to remote 
> devices will not fail but it is not so important that deprecated fields 
> be removed.  This is often the case with backward compatibility.  New 
> columns will not affect old systems but allows all remote devices 
> running older software to be updated using the same process as new 
> devices.  Once the hardware reaches end of life it will be replaced and 
> the new hardware will use the new columns.  So having the ability to 
> Alter the table with a series of Add Column commands ensures that the 
> new records included in the update are added to the table.  Having the 
> "if not exists" would remove all of this potential complexity and allows 
> a quick and easy method to ensure the column exists in the table using 
> simple SQL commands that will not cause errors if the column is already 
> present.

Sort of like this:

https://www.postgresql.org/docs/9.6/static/sql-altertable.html

"ADD [ COLUMN ] [ IF NOT EXISTS ] ..."

in 9.6+

> 
> I understand that some people will feel this is not the recommended way 
> to proceed.  I understand there are lots of ways this can be handled 
> differently as I have been working with database of various kinds for 
> over 30 years.  All I am suggesting is that this feature would be nice 
> to have in some scenarios and thus would like it considered.  I do not 
> wish to start a discussion on whether using it is a sound approach or 
> not as that is a different discussion.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

сб, 4 авг. 2018 г. в 18:57, Simon White <simonwhite@dciphercomputing.com>:
>
> Hi
>
> I would like to suggest the addition of the "If not exists" to the Add
> Column feature of Postgres.  There are quite common situations where
> ensuring a column exists is important so that an update to remote
> devices will not fail but it is not so important that deprecated fields
> be removed.  This is often the case with backward compatibility.  New
> columns will not affect old systems but allows all remote devices
> running older software to be updated using the same process as new
> devices.  Once the hardware reaches end of life it will be replaced and
> the new hardware will use the new columns.  So having the ability to
> Alter the table with a series of Add Column commands ensures that the
> new records included in the update are added to the table.  Having the
> "if not exists" would remove all of this potential complexity and allows
> a quick and easy method to ensure the column exists in the table using
> simple SQL commands that will not cause errors if the column is already
> present.
Hi Simon !

Recently I've released a tool that can be used to bypass errors like
("object already exists")
Please, take a look at https://github.com/dmitigr/pgspa
Feel free to contact me for any question about this tool.