Thread: int4 to varchar length (pgadmin3) issue
hi all, i initially set up my document revision column as int4 and used integers to define my revisions. somebody didn't like integer revisions and requested that i go to alpha revisions. when i change (in pgadmin3) the int4 columns to varchar, everything goes well in pgadmin3 up until the grayed out box for the length of the varchar stays grayed out. iow, i want to limit the the newly converted varchar revision to 2 characters, but can't due to the grayed out boxes in pgadmin3. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> iow, i want to limit the the newly converted varchar > revision to 2 characters, but can't due to the grayed > out boxes in pgadmin3. I saw this example at: http://www.postgresql.org/docs/8.1/interactive/sql-altertable.html ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5); This should do what you want. Regards, Richard Broersma Jr.
> > iow, i want to limit the the newly converted > varchar > > revision to 2 characters, but can't due to the > grayed > > out boxes in pgadmin3. > > I saw this example at: > http://www.postgresql.org/docs/8.1/interactive/sql-altertable.html > > ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK > (char_length(zipcode) = 5); > > This should do what you want. > > Regards, > > Richard Broersma Jr. > Richard, thank you. i think this does what i want, however, i want to understand a bit more. this is an additional defined constraint, correct? is this constraint added automagically by pgadmin when you enter a length value or is this a different method to achieve a similar result? tia.. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> this is an additional defined constraint, correct? Yes, it will add a constraint to your table that specifically works on that particualar field. > is > this constraint added automagically by pgadmin when > you enter a length value or is this a different > method to achieve a similar result? I am not exactly sure what your question is, but the ALTER TABLE <...> is a Data Definition statement. Once the statement comments, then any future attempts by any connection to update/insert will blocked if the data doesn't meet the constraint criteria. Regards, Richard Broersma Jr.
operationsengineer1@yahoo.com schrieb: > when i change (in pgadmin3) the int4 columns to > varchar, everything goes well in pgadmin3 up until the > grayed out box for the length of the varchar stays > grayed out. > > iow, i want to limit the the newly converted varchar > revision to 2 characters, but can't due to the grayed > out boxes in pgadmin3. You could add a new varchar(2) column with a temporary name like "tempname". I suppose your revisionnumbers are stored in am column "revisioninfo" and none of them has more than 2 digits and all are positive. Then run : update table1 set tempname = revisioninfo; That copies the existing data into the new column and does an implicit typecast from int4 to char. Now you can drop your numeric column "revisioninfo" and rename the column "tempname" to "revisioninfo". Setting an explicit constraint to watch the varchar(2) column isn't necesary this way because the PG won't let you insert more than 2 chars anyway.
Richard Broersma Jr schrieb: >> iow, i want to limit the the newly converted varchar >> revision to 2 characters, but can't due to the grayed >> out boxes in pgadmin3. > > I saw this example at: > http://www.postgresql.org/docs/8.1/interactive/sql-altertable.html > > ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5); > > This should do what you want. Actually it is not. This constraint enforces the length of zipcodes to exactly 5 chars. operationsengineer1 rather wants an upper limit of 2 chars but not exactly 2 everywhere. Theres no need for a separate constraint as a comumn declaration of varchar(2) would do. operationsengineer1's problem is that pgAdmin lets him change the data type of the column but not the size of the resulting varchar. The size field in the form is inactive and empty. Thats a wee bit silly of pgAdmin because it lets you alter the size of varchar columns that are created as varchar in the first place but not of columns that got converted to varchar. But thanks to your link to the documentation I found the solution. ALTER TABLE table1 ALTER COLUMN revisioninfo TYPE varchar(2);