Re: BINARY and BINARY VARYING datatypes in PostgreSQL

On 02.05.2011 12:46, Grzegorz Szpetkowski wrote:
> I know that BIT and BIT VARYING types were deleted from SQL:2003 and
> there are "old new" BINARY, BINARY VARYING in SQL:2008. I have two
> question:
>
> 1) Are these types technically the same (I mean implementation things) ?
> 2) Is PostgreSQL aim to support BINARY, BINARY VARYING in 9.1 and
> leave BIT and BIT VARYING for backward compability (for example as
> aliases) ?

BIT and BINARY are two different data types.

Let me try to explain it in an understandable way:
You have CHARACTER and CHARACTER VARYING these are bind to specific
encodings / charsets / collations.

PostgreSQL not implemented own encodings / charsets / collation.
We use what is given by libc.

When you use CHARACTER or VARCHAR as datatype in your column and let us
say your system is configured to store in utf8 -
and your client is using latin2 -
Then you tell the system that your client is using latin2 and the system
will convert
your input fully automatic and transparent into utf8.
Same with output - it will convert it into latin2 before returning.

For example German umlauts and Euro sign use 1 byte in Latin9 -
In UTF-8 two bytes are used for umlauts and 3 bytes for the Euro sign.

This is great and useful for text input.

But - you may not want that this automatic converting happens.
You may want to store something different then text in CHAR / VARCHAR
columns.
There are some other reasons, why you might not want this automatic
converting.

For this BINARY and VARBINARY is used.
BINARY and VARBINARY should ignore all client and system encodings /
charsets / collations
and just store the data like they are and return them like they are.

You can see BINARY and VARBINARY as CHAR and VARCHAR just without a binding
to encodings / charsets / collations.

You should be able to convert BINARY / VARBINARY text into CHAR /
VARCHAR by telling
which encoding is used for the binary text.

As I said before PostgreSQL not yet implemented that you can have different
encodings / charsets per column.

PostgreSQL use one encoding for all columns. It is the encoding which
you configured during
initdb. Usually it is utf8 today.

We support BYTEA which also ignores locale settings.

http://www.postgresql.org/docs/9.0/static/datatype-binary.html

"The SQL standard defines a different binary string type, called BLOB or
BINARY LARGE OBJECT. The input format is different from bytea, but the
provided functions and operators are mostly the same."

Removing data types is such a big change that major version number
should change.

The release after 7.4 was named 8.0 and not 7.5.
Afair there was a data type cleaning in 8.0.

This means - I doubt that any data type will be removed before
PostgreSQL 10.0.

> "PostgreSQL supports most of the major features of SQL:2008. Out of
> 179 mandatory features required for full Core conformance, PostgreSQL
> conforms to at least 160. In addition, there is a long list of
> supported optional features. It might be worth noting that at the time
> of writing, no current version of any database management system
> claims full conformance to Core SQL:2008."
There are countries where comparative advertising is forbidden.

Who says that 179 mandatory features are required for full core
conformance?
What is core SQL:2008?

ISO / IEC 9075 is SQL Standard. It is written from agents / experts of
lots of countries.
Experts of the single countries coming from research institutes and
companies who
develop database systems (as far as there is a developing company in
that country).

Means theory and practice meeting each other.

The question is:
Is it more important to implement the theories or is it more important
to listen to
the users and implement what they really need?

Best Regards,

Susanne

--
Susanne Ebrecht - 2ndQuadrant
PostgreSQL Development, 24x7 Support, Training and Services
www.2ndQuadrant.com