Alter table column to int(9) UNSIGNED ZEROFILL NOT NULL

Question

After reading the following link..

https://dba.stackexchange.com/questions/94443/how-should-a-numeric-value-that-is-an-external-key-be-stored

I decided to alter a column from text to:

int(9) UNSIGNED ZEROFILL NOT NULL

However, I am not sure of the SQL statement to use. I know the below is not correct because it does not include the the 9 digits, unsigned zerofill and not NULL parameters.

ALTER TABLE "Organizations" ALTER COLUMN "EIN" TYPE INTEGER using "EIN"::INTEGER

UPDATE: Since Postgres does not use zerofill or INT(9). What would be the recommended data type of an EIN number that is 9 digits?

why you think the statement is not correct?.. I would use exactly that one :) at least from first look — Vao Tsun, Oct 23 '17 at 12:02
Because it does not show int(9), modify the column to unsigned zero fill and not null. — user2012677, Oct 23 '17 at 12:02
use `set not null` for not null, integer does not have variable length and I dont know what zerofill is — Vao Tsun, Oct 23 '17 at 12:04
I found this: alter table tablename alter column columnname SET NOT NULL;, but do I need to run it seperately and it still does not alter the column for int(9), unsigned zerofill. — user2012677, Oct 23 '17 at 12:06
There is no such thing as `int(9)` in Postgres (or "zerofill"). And if you thought that `int(x)` would define a constraint in MySQL you were wrong to begin with. MySQL will happily store `999999` in an `int(1)` column. — , Oct 23 '17 at 12:07
@user2012677 yes - two separate statements afaik. is zerofill about the fillfactor?.. — Vao Tsun, Oct 23 '17 at 12:08
@VaoTsun, Yes, https://stackoverflow.com/questions/5256469/what-is-the-benefit-of-zerofill-in-mysql — user2012677, Oct 23 '17 at 12:10
Reading the above and link, what type of store would you recommend? — user2012677, Oct 23 '17 at 12:11
ah - so its not fillfactor - it's padding... you want some form of "automatic padding" — Vao Tsun, Oct 23 '17 at 12:17
@VaoTsun: `zerofill` (just as the `(9)` part) is a _hint_ for the frontend on how that number should be displayed. Nothing more. The client is free to completely ignore that. — , Oct 23 '17 at 12:26
@a_horse_with_no_name ah - its not stored in db, but rather smth like `datestyle` for integers?.. — Vao Tsun, Oct 23 '17 at 12:36

score 2 · Answer 1 · answered Oct 23 '17 at 12:59

I would recommend below as is in two statements:

ALTER TABLE "Organizations" ALTER COLUMN "EIN" TYPE INTEGER using "EIN"::INTEGER;
ALTER TABLE "Organizations" ALTER COLUMN "EIN" SET NOT NULL;

decoration with padding zeros can be done on select with client (or rule, which would be effectively just a view, selected instead, and thus I think overcomplicating here - ((and changing to int to select text with zeroes - does not sound reasonambe))), eg:

t=# select lpad(123::int::text,9,'0');
   lpad
-----------
 000000123
(1 row)

so If its needed, can be mocked up

score 1 · Answer 2 · answered Oct 23 '17 at 13:07

For the 9-digit restriction, a domain over int can work:

CREATE DOMAIN ein AS int CHECK (VALUE>0 AND VALUE<1000000000);

Then ein can be used in declarations as a type, for instance:

=> CREATE TABLE test(id ein, t text);
CREATE TABLE

=> insert into test values(2*1e9::int);
ERROR:  value for domain ein violates check constraint "ein_check"

=> insert into test values(100);
INSERT 0 1

The zerofill bit is different, it's about presentation, not storage, and that part cannot be specialized for a domain.

You may instead apply to_char to the values, for example:

=> select to_char(id,'000000000') from test;
  to_char  
------------
  000000100

and possibly access this through a stored view or a presentation function that takes only the ein as argument if you prefer to abstract this from the client.

To go further, you could create a full type with CREATE TYPE backed with C code for the INPUT and OUTPUT function, and these functions could implement the 9-digit left-padded format as the input/output format, so that the user may never see anything else at the SQL level.

Alter table column to int(9) UNSIGNED ZEROFILL NOT NULL

2 Answers2