On 7/12/05, Joe <svn@freedomcircle.net> wrote:
> I have a MySQL database that I'm converting to PostgreSQL which has 10 columns
> with TINYINT type, i.e., a one-byte integer. Only one of them qualifies as a
> true BOOLEAN. Two are entity identifiers (for limited range "classes" or
> "categories") and three others are "type"/code values. The last four are month
> numbers or day of month numbers (not implemented as date types because the year
> is separate and is part of the primary key).
>
> I'm wondering what would be the best conversion choice for these columns:
> smallint, numeric(1), char(1), something else? AFAICT, the application does not
smallint takes two bytes. Numeric(1) will take around 10 bytes and char(1) will
take 5 bytes (4 bytes for length of data). The closest match is smallint which
is reasonably small and will do the trick. Remember that PostgreSQL doesn't
have unsigned types.
This may seem "a terrible waste of diskspace" at the first glance, but keep
in mind that PostgreSQL's power lies in an intelligent planner. You can
safely nomralize data and create views which "emulate" one-table which
both simplifies development and can be more efficient in terms of
storage and raw speed.
> directly do any arithmetic on these columns, but the identifier and code types
> are used as indices into PHP arrays. The month/day values are formatted into
> dates for display (and the month also servers as an index into a month-name array).
You should consider using date / time / timestamp column for dates and use
functions like extract(day from mydate). Remember you can use functional
indexes, so if you need data to by indexed by day, you can:
CREATE INDEX dayindex ON sometable ((extract(day from datecolumn)));
Regards,
Dawid