Re: 'create script' not usable for columns set 'not null' and 'default'

In pgAdmin III v.1.14.1, if you navigate to
schemas/public/tables/<table>/Columns/<column> and right-click on the column
name and press "create script", you often get a script that will not execute.

The problem occurs when you have a column that has "NOT NULL" and a "DEFAULT"
property.

e.g. A script generated by pgadmin might be as follows:
> -- Column: waged
>
> -- ALTER TABLE custom_ccowa_kwc DROP COLUMN waged;
>
> ALTER TABLE custom_ccowa_kwc ADD COLUMN waged2 integer;
> ALTER TABLE custom_ccowa_kwc ALTER COLUMN waged2 SET NOT NULL;
> ALTER TABLE custom_ccowa_kwc ALTER COLUMN waged2 SET DEFAULT 1;

But if you delete that column and insert the above text into the SQL editor
and press "Execute query". then you get the following error message:

> ERROR: column "waged2" contains null values
>
>
> ********** Error **********
>
> ERROR: column "waged2" contains null values
> SQL state: 23502

Could pgAdmin please be modified to generate a script like this instead:
> ALTER TABLE custom_ccowa_kwc ADD COLUMN waged2 integer NOT NULL DEFAULT 1;

Thanks for considering this, I couldn't find a similar bug report in the archives.

On Tue, Jul 3, 2012 at 8:38 AM, Rohan Carly <se456(at)rohan(dot)id(dot)au> wrote:
> In pgAdmin III v.1.14.1, if you navigate to
> schemas/public/tables/<table>/Columns/<column> and right-click on the column
> name and press "create script", you often get a script that will not
> execute.
>
> The problem occurs when you have a column that has "NOT NULL" and a
> "DEFAULT" property.
>
> e.g. A script generated by pgadmin might be as follows:
>>
>> -- Column: waged
>>
>> -- ALTER TABLE custom_ccowa_kwc DROP COLUMN waged;
>>
>> ALTER TABLE custom_ccowa_kwc ADD COLUMN waged2 integer;
>> ALTER TABLE custom_ccowa_kwc ALTER COLUMN waged2 SET NOT NULL;
>> ALTER TABLE custom_ccowa_kwc ALTER COLUMN waged2 SET DEFAULT 1;
>
>
> But if you delete that column and insert the above text into the SQL editor
> and press "Execute query". then you get the following error message:
>
>> ERROR: column "waged2" contains null values
>>
>>
>> ********** Error **********
>>
>> ERROR: column "waged2" contains null values
>> SQL state: 23502
>
>
> Could pgAdmin please be modified to generate a script like this instead:
>>
>> ALTER TABLE custom_ccowa_kwc ADD COLUMN waged2 integer NOT NULL DEFAULT 1;
>
>
> Thanks for considering this, I couldn't find a similar bug report in the
> archives.

It's not really a bug. The current behaviour generates a query that
will work, if there are no nulls in the table (i.e. the table is
empty). The suggested query will always cause a complete rewrite of
the table which is often undesirable.

It is generally expected that the script that are generated are edited
to suit your requirements, before being run (otherwise, why not just
use the GUI), and really are only meant as a starting point.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 3/07/2012 4:09 PM, Dave Page wrote:
> On Tue, Jul 3, 2012 at 8:38 AM, Rohan Carly <se456(at)rohan(dot)id(dot)au> wrote:
>> In pgAdmin III v.1.14.1, if you navigate to
>> schemas/public/tables/<table>/Columns/<column> and right-click on the column
>> name and press "create script", you often get a script that will not
>> execute.
>>
>> The problem occurs when you have a column that has "NOT NULL" and a
>> "DEFAULT" property.
>>
>> e.g. A script generated by pgadmin might be as follows:
>>> -- Column: waged
>>>
>>> -- ALTER TABLE custom_ccowa_kwc DROP COLUMN waged;
>>>
>>> ALTER TABLE custom_ccowa_kwc ADD COLUMN waged2 integer;
>>> ALTER TABLE custom_ccowa_kwc ALTER COLUMN waged2 SET NOT NULL;
>>> ALTER TABLE custom_ccowa_kwc ALTER COLUMN waged2 SET DEFAULT 1;
>>
>> But if you delete that column and insert the above text into the SQL editor
>> and press "Execute query". then you get the following error message:
>>
>>> ERROR: column "waged2" contains null values
>>>
>>>
>>> ********** Error **********
>>>
>>> ERROR: column "waged2" contains null values
>>> SQL state: 23502
>>
>> Could pgAdmin please be modified to generate a script like this instead:
>>> ALTER TABLE custom_ccowa_kwc ADD COLUMN waged2 integer NOT NULL DEFAULT 1;
>>
>> Thanks for considering this, I couldn't find a similar bug report in the
>> archives.
> It's not really a bug. The current behaviour generates a query that
> will work, if there are no nulls in the table (i.e. the table is
> empty). The suggested query will always cause a complete rewrite of
> the table which is often undesirable.
>
> It is generally expected that the script that are generated are edited
> to suit your requirements, before being run (otherwise, why not just
> use the GUI), and really are only meant as a starting point.
>
Alright, fair enough.

FYI: I'm often adding new columns to a table on my development database using
the GUI. After fine tuning over several iterations through the GUI, I use
'create script' to quickly add the new column to the matching table on the
live database. This technique works fine for transferring whole tables, but
not for individual columns, due to the difficulty I reported.