Re: moving from MySQL to pgsql

On 10/11/2012 02:07 PM, Vineet Deodhar wrote:
> On Thu, Oct 11, 2012 at 5:26 AM, Ondrej Ivanič <ondrej.ivanic@gmail.com
> <mailto:ondrej.ivanic@gmail.com>> wrote:
>
>     Hi,
>
>     On 10 October 2012 19:47, Vineet Deodhar <vineet.deodhar@gmail.com
>     <mailto:vineet.deodhar@gmail.com>> wrote:
>      > 3) Can I simulate MySQL's TINYINT data-type (using maybe the
>     custom data
>      > type or something else)
>
>     What do you exactly mean? Do you care about storage requirements or
>     constraints? The smallest numeric type in postgres is smallint: range
>     is +/- 32K and you need two bytes. You can use check constraint to
>     restrict the range (postgres doesn't have signed / unsigned types):
>
>     create table T (
>        tint_signed smallint check ( tint_signed >= -128 and tint_signed
>     =< 127 ),
>        tint_unsigned smallint check ( tint_unsigned >= 0 and
>     tint_unsigned =< 255 )
>     )
>
>
> Yes. Considering the storage requirements , I am looking for TINYINT
> kind of data type.

The storage difference between `SMALLINT` and a `TINYINT` would be ...
tiny, given the space taken up by tuple headers, etc.

As it is, a row containing four SMALLINT columns is 32 bytes, vs 40
bytes for INTEGER columns or 28 for BOOLEAN.

regress=# SELECT pg_column_size( (BOOLEAN 't', BOOLEAN 't', BOOLEAN 'f',
BOOLEAN 'f') );
  pg_column_size
----------------
              28
(1 row)

regress=# SELECT pg_column_size( (SMALLINT '2', SMALLINT '3', SMALLINT
'4', SMALLINT '5') );
  pg_column_size
----------------
              32
(1 row)

regress=# SELECT pg_column_size( (INTEGER '2', INTEGER '3', INTEGER '4',
INTEGER '5') );
  pg_column_size
----------------
              40
(1 row)


The difference between SMALLINT and BOOLEAN (or TINYINT if Pg supported
it) is 1 byte per column. If you had 30 smallint columns and quite a few
million rows it might start making a difference, but it's *really* not
worth obsessing about. Unless you have high-column-count tables that
contain nothing but lots of integers of range 0-255 there's no point caring.

--
Craig Ringer