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

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