Re: BINARY and BINARY VARYING datatypes in PostgreSQL

Thank you for comprehensive answer.

You are right, it is better to implement what users really need in
their work, but I believe that people who writing SQL Standard want to
give it pragmatic and usable.

Regards,
Grzegorz Szpetkowski

2011/5/3 Susanne Ebrecht <susanne(at)2ndquadrant(dot)com>:
> 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
>
>