Thread: Integer Question - Does Limit Value Make Sense
hi all,
i'm trying to work my through Agile Webdev with Rails.
unfortunately (for me, anyway!), they went with mysql
as their base db.
i'm trying to work through the code on my own using
pgsql.
i ran into a problem in their migration script (update
db script).
they use code as follows:
add_column :products, :price, :decimal, :precision =>
8, :scale => 2, :default => 0
you can get the gist of what they are doing with the
code.
i installed a plugin that let's me use integers to
handle the money portion, but this didn't work:
add_column :products, :price_in_cents, :integer,
:limit => 10, :default => 0, :null => false
due the following error:
PGError: ERROR: syntax error at or near "(" at
character 48
: ALTER TABLE products ADD price_in_cents integer(10)
it looks to me like it choked on the format
"integer(10)" part.
is limit a mysql specific issue for integer? it was
suggested on irc, but i think the guy is used to
mysql.
tia...
____________________________________________________________________________________
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail beta.
http://new.mail.yahoo.com
<operationsengineer1@yahoo.com> writes:
> it looks to me like it choked on the format
> "integer(10)" part.
> is limit a mysql specific issue for integer?
Absolutely. There might be another DB or two that takes the above
syntax, but it's certainly nowhere to be found in the SQL spec.
SQL99 defines the numeric types as
<exact numeric type> ::=
NUMERIC [ <left paren> <precision> [ <comma> <scale> ] <right paren> ]
| DECIMAL [ <left paren> <precision> [ <comma> <scale> ] <right paren> ]
| DEC [ <left paren> <precision> [ <comma> <scale> ] <right paren> ]
| INTEGER
| INT
| SMALLINT
<approximate numeric type> ::=
FLOAT [ <left paren> <precision> <right paren> ]
| REAL
| DOUBLE PRECISION
INTEGER is stated to have scale zero and implementation-defined precision.
regards, tom lane
--- operationsengineer1@yahoo.com wrote:
> hi all,
>
> i'm trying to work my through Agile Webdev with Rails.
> unfortunately (for me, anyway!), they went with mysql
> as their base db.
>
> i'm trying to work through the code on my own using
> pgsql.
>
> i ran into a problem in their migration script (update
> db script).
>
> they use code as follows:
>
> add_column :products, :price, :decimal, :precision =>
> 8, :scale => 2, :default => 0
>
> you can get the gist of what they are doing with the
> code.
>
> i installed a plugin that let's me use integers to
> handle the money portion, but this didn't work:
>
> add_column :products, :price_in_cents, :integer,
> :limit => 10, :default => 0, :null => false
>
> due the following error:
>
> PGError: ERROR: syntax error at or near "(" at
> character 48
> : ALTER TABLE products ADD price_in_cents integer(10)
>
> it looks to me like it choked on the format
> "integer(10)" part.
>
> is limit a mysql specific issue for integer? it was
> suggested on irc, but i think the guy is used to
> mysql.
maybe integer(10) is a mysql extension. I think that postgresql's numeric datatype will do
whatever you want with 10 digits for dollars and two digits for cents. You can set it to handle
anything that you want as long as you define the numeric type correctly.
http://www.postgresql.org/docs/8.2/interactive/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL
By the way did you make a finial decision one a table structure for your lending problem?
Regards,
Richard Broersma Jr.
> maybe integer(10) is a mysql extension. I think > that postgresql's numeric datatype will do > whatever you want with 10 digits for dollars and two > digits for cents. You can set it to handle > anything that you want as long as you define the > numeric type correctly. > > http://www.postgresql.org/docs/8.2/interactive/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL > > By the way did you make a finial decision one a > table structure for your lending problem? Richard, i got it to work, but i'm now banging my head on an extension that stores pennies and converts to dollar and cents. anyway, i'll figure it out eventually. i haven't made a decision yet. i'm thinking through three projects right now and working through the agile webdev book - all on my not so free time. i'll get there, but i need to think it through more so it gels in my head. thanks for the help. ____________________________________________________________________________________ Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail beta. http://new.mail.yahoo.com
> <operationsengineer1@yahoo.com> writes: > > it looks to me like it choked on the format > > "integer(10)" part. > > > is limit a mysql specific issue for integer? > > Absolutely. There might be another DB or two that > takes the above > syntax, but it's certainly nowhere to be found in > the SQL spec. > SQL99 defines the numeric types as > > <exact numeric type> ::= > NUMERIC [ <left paren> <precision> [ > <comma> <scale> ] <right paren> ] > | DECIMAL [ <left paren> <precision> [ > <comma> <scale> ] <right paren> ] > | DEC [ <left paren> <precision> [ > <comma> <scale> ] <right paren> ] > | INTEGER > | INT > | SMALLINT > > <approximate numeric type> ::= > FLOAT [ <left paren> <precision> > <right paren> ] > | REAL > | DOUBLE PRECISION > > INTEGER is stated to have scale zero and > implementation-defined precision. > thanks, Tom. oe1 > regards, tom lane > ____________________________________________________________________________________ Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail beta. http://new.mail.yahoo.com
> Richard, i got it to work, but i'm now banging my head > on an extension that stores pennies and converts to > dollar and cents. anyway, i'll figure it out > eventually. Do you have some sort of extention that you are trying to figure out the use of, or are you looking for an algorithm that will convert units in pennies to decimal dollars? Regards, Richard Broersma Jr.
--- Richard Broersma Jr <rabroersma@yahoo.com> wrote: > > Richard, i got it to work, but i'm now banging my > head > > on an extension that stores pennies and converts > to > > dollar and cents. anyway, i'll figure it out > > eventually. > > Do you have some sort of extention that you are > trying to figure out the use of, or are you > looking for an algorithm that will convert units in > pennies to decimal dollars? > > Regards, > > Richard Broersma Jr. > Richard, dollars_and_cents is a RoR extension that allows one to store everything in the db as cents (integer) and then automatically convert back to dollars in the application itself. the column name has to be [name]_in_cents. i chose price_in_cents. the extension automatically makes [name] available to the app, in my case price, and formats it in monetary notation. all i needed to do was restart my server to get it to take effect. i finally have it running right now. ____________________________________________________________________________________ Cheap talk? Check out Yahoo! Messenger's low PC-to-Phone call rates. http://voice.yahoo.com