DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=PkB8qlowPSckda6rRkWLSCxO+TOtAYRGIYlMZk9UO8btG6jZ06G6ZspvPTm32YU3qU
	8iYq3k0TVASFL+ou4fLZtrK0ClkrZAqt8GjPfMx2EPCtbXQbJ4ReHA6v/m5CUHYQhBpP
	NmqgAWpzZN4MrY44/npkayYEbnjW7VPxjogmA=
MIME-Version: 1.0
In-Reply-To: <4DBFC6F8.8010507@2ndQuadrant.com>
References: <BANLkTi=8Ws27gAVkQY44gf9OH2j_+kME2w@mail.gmail.com>
	<4DBFC6F8.8010507@2ndQuadrant.com>
Date: Tue, 3 May 2011 14:41:39 +0200
Message-ID: <BANLkTim-zyNoo5f0MhzouU==9RHuxKO1nw@mail.gmail.com>
Subject: Re: BINARY and BINARY VARYING datatypes in PostgreSQL
From: Grzegorz Szpetkowski <gszpetkowski@gmail.com>
To: Susanne Ebrecht <susanne@2ndquadrant.com>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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@2ndquadrant.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 bindi=
ng
> to encodings / charsets / collations.
>
> You =A0should be able to convert BINARY / VARBINARY text into CHAR / VARC=
HAR
> by telling
> which encoding is used for the binary text.
>
> As I said before PostgreSQL not yet implemented that you can have differe=
nt
> 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 conforman=
ce?
> What is core SQL:2008?
>
> ISO / IEC 9075 is SQL Standard. It is written from agents / experts of lo=
ts
> 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
>
>