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(dot)ivanic(at)gmail(dot)com
> <mailto:ondrej(dot)ivanic(at)gmail(dot)com>> wrote:
>
> Hi,
>
> On 10 October 2012 19:47, Vineet Deodhar <vineet(dot)deodhar(at)gmail(dot)com
> <mailto:vineet(dot)deodhar(at)gmail(dot)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