Обсуждение: '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@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. -- 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@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.