On 3/07/2012 4:09 PM, Dave Page wrote:
On Tue, Jul 3, 2012 at 8:38 AM, Rohan Carly <se...@rohan.id.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.
--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
