Horton

Thread

  1. char(0)

    Susanne Ebrecht <susanne@2ndquadrant.com> — 2011-10-17T07:31:46Z

    Hello,

I couldn't find that somebody already mentioned it.

PostgreSQL isn't supporting CHAR(0).

An empty string has a length of 0.

CHAR(0) can have two values: NULL and empty string.

In MySQL it is very common to simulate not null boolean
by using CHAR(0).

This is a little bit annoying on migration topics.

Susanne

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

  2. Re: char(0)

    Hans-Jürgen Schönig <postgres@cybertec.at> — 2011-10-17T08:18:07Z

    On Oct 17, 2011, at 9:31 AM, Susanne Ebrecht wrote:

> Hello,
> 
> I couldn't find that somebody already mentioned it.
> 
> PostgreSQL isn't supporting CHAR(0).
> 
> An empty string has a length of 0.
> 
> CHAR(0) can have two values: NULL and empty string.
> 
> In MySQL it is very common to simulate not null boolean
> by using CHAR(0).
> 
> This is a little bit annoying on migration topics.
> 
> Susanne


hello ....

i would actually see it the other way round.
supporting char(0) is the bug here ...
if somebody used char(0) to simulate boolean not null ... let me not comment on that one for social reasons ;).

	regards,

		hans

--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de

  3. Re: char(0)

    Simon Riggs <simon@2ndquadrant.com> — 2011-10-17T08:30:40Z

    On Mon, Oct 17, 2011 at 8:31 AM, Susanne Ebrecht
<susanne@2ndquadrant.com> wrote:

> PostgreSQL isn't supporting CHAR(0).

What does the SQL Standard say?

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  4. Re: char(0)

    Thomas Kellerer <spam_eater@gmx.net> — 2011-10-17T08:53:30Z

    Susanne Ebrecht, 17.10.2011 09:31:
> Hello,
>
> I couldn't find that somebody already mentioned it.
>
> PostgreSQL isn't supporting CHAR(0).
>
> An empty string has a length of 0.
>
> CHAR(0) can have two values: NULL and empty string.
>
> In MySQL it is very common to simulate not null boolean
> by using CHAR(0).
>
> This is a little bit annoying on migration topics.

While not move on to a cleaner approach during the migration and use a "boolean not null"?

Thomas

  5. Re: char(0)

    Susanne Ebrecht <susanne@2ndquadrant.com> — 2011-10-17T12:28:14Z

    On 17.10.2011 10:30, Simon Riggs wrote:
> On Mon, Oct 17, 2011 at 8:31 AM, Susanne Ebrecht
> <susanne@2ndquadrant.com>  wrote:
>
>> PostgreSQL isn't supporting CHAR(0).
> What does the SQL Standard say?
>

Document: 02-Foundation

Section: 4.2.1 Introduction to character strings

Begin quoting
A character string is a sequence of characters. All the characters in a 
character string are taken from a single
character set. A character string has a length, which is the number of 
characters in the sequence. The length is
0 (zero) or a positive integer.
End quoting

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

  6. Re: char(0)

    Boszormenyi Zoltan <zb@cybertec.at> — 2011-10-17T13:44:18Z

    2011-10-17 14:28 keltezéssel, Susanne Ebrecht írta:
> On 17.10.2011 10:30, Simon Riggs wrote:
>> On Mon, Oct 17, 2011 at 8:31 AM, Susanne Ebrecht
>> <susanne@2ndquadrant.com> wrote:
>>
>>> PostgreSQL isn't supporting CHAR(0).
>> What does the SQL Standard say?
>>
>
> Document: 02-Foundation
>
> Section: 4.2.1 Introduction to character strings
>
> Begin quoting
> A character string is a sequence of characters. All the characters in a character string 
> are taken from a single
> character set. A character string has a length, which is the number of characters in the 
> sequence. The length is
> 0 (zero) or a positive integer.
> End quoting

I am looking at 6WD2_02_Foundation_2007-12.pdf.
Search for "<character length>" that is used in section "6.1 <data type>":

<character length> := <unsigned integer> [ <char length units> ]

Section 6.1 doesn't talk about limiting the definition to > 0 values

But in page 157, section "5.3 <literal>":

============================================================
17) The declared type of a <character string literal> is fixed-length character string. 
The length of a <character
string literal> is the number of <character representation>s that it contains. Each <quote 
symbol> contained
in <character string literal> represents a single <quote> in both the value and the length 
of the <character
string literal>. The two <quote>s contained in a <quote symbol> shall not be separated by 
any <separator>.

NOTE 92 — <character string literal>s are allowed to be zero-length strings (i.e., to 
contain no characters) even though it is
not permitted to declare a <data type> that is CHARACTER with <character length> 0 (zero).
============================================================

So, a table column is not allowed to be char(0) or varchar(0). It's explicit in NOTE 92.

Best regards,
Zoltán Böszörményi

-- 
----------------------------------
Zoltán Böszörményi
Cybertec Schönig&  Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
      http://www.postgresql.at/

  7. Re: char(0)

    Susanne Ebrecht <susanne@2ndquadrant.com> — 2011-10-17T13:57:20Z

    Hello,

On 17.10.2011 15:44, Boszormenyi Zoltan wrote:
> 2011-10-17 14:28 keltezéssel, Susanne Ebrecht írta:
>> On 17.10.2011 10:30, Simon Riggs wrote:
>>> On Mon, Oct 17, 2011 at 8:31 AM, Susanne Ebrecht
>>> <susanne@2ndquadrant.com> wrote:
>>>
>>>> PostgreSQL isn't supporting CHAR(0).
>>> What does the SQL Standard say?
>>>
>>
>> Document: 02-Foundation
>>
>> Section: 4.2.1 Introduction to character strings
>>
>> Begin quoting
>> A character string is a sequence of characters. All the characters in 
>> a character string are taken from a single
>> character set. A character string has a length, which is the number 
>> of characters in the sequence. The length is
>> 0 (zero) or a positive integer.
>> End quoting
>
> I am looking at 6WD2_02_Foundation_2007-12.pdf.
> Search for "<character length>" that is used in section "6.1 <data 
> type>":
>
> <character length> := <unsigned integer> [ <char length units> ]
>
> Section 6.1 doesn't talk about limiting the definition to > 0 values
>
> But in page 157, section "5.3 <literal>":
>
> ============================================================
> 17) The declared type of a <character string literal> is fixed-length 
> character string. The length of a <character
> string literal> is the number of <character representation>s that it 
> contains. Each <quote symbol> contained
> in <character string literal> represents a single <quote> in both the 
> value and the length of the <character
> string literal>. The two <quote>s contained in a <quote symbol> shall 
> not be separated by any <separator>.
>
> NOTE 92 — <character string literal>s are allowed to be zero-length 
> strings (i.e., to contain no characters) even though it is
> not permitted to declare a <data type> that is CHARACTER with 
> <character length> 0 (zero).
> ============================================================
>
> So, a table column is not allowed to be char(0) or varchar(0). It's 
> explicit in NOTE 92.
>

I looked this up again.

I found more passages in which is written that it should start with 0.

But in the section you mentioned here - 1 is given.

That is a bug in SQL Standard. SQL Standard is inconsistent here.
The next SQL Standard meeting is next week.
Of course I will bring up this problem.
We will see what will be the outcome of it.

Susanne

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

  8. Re: char(0)

    Andreas Pflug <pgadmin@pse-consulting.de> — 2011-10-17T14:41:24Z

    Am 17.10.11 10:53, schrieb Thomas Kellerer:
> Susanne Ebrecht, 17.10.2011 09:31:
>> Hello,
>>
>> I couldn't find that somebody already mentioned it.
>>
>> PostgreSQL isn't supporting CHAR(0).
>>
>> An empty string has a length of 0.
>>
>> CHAR(0) can have two values: NULL and empty string.
>>
>> In MySQL it is very common to simulate not null boolean
>> by using CHAR(0).
>>
>> This is a little bit annoying on migration topics.
>
> While not move on to a cleaner approach during the migration and use a
> "boolean not null"?

Sounds much too straight forward, not mysql-ish artistic enough...

Regards,
Andreas

  9. Re: char(0)

    John R Pierce <pierce@hogranch.com> — 2011-10-17T15:04:37Z

    On 10/17/11 7:41 AM, Andreas Pflug wrote:
> Sounds much too straight forward, not mysql-ish autistic enough...

I fixed your spelling.  HTH!



-- 
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

  10. Re: char(0)

    Hans-Jürgen Schönig <postgres@cybertec.at> — 2011-10-17T15:11:47Z

    On Oct 17, 2011, at 4:41 PM, Andreas Pflug wrote:

> Am 17.10.11 10:53, schrieb Thomas Kellerer:
>> Susanne Ebrecht, 17.10.2011 09:31:
>>> Hello,
>>> 
>>> I couldn't find that somebody already mentioned it.
>>> 
>>> PostgreSQL isn't supporting CHAR(0).
>>> 
>>> An empty string has a length of 0.
>>> 
>>> CHAR(0) can have two values: NULL and empty string.
>>> 
>>> In MySQL it is very common to simulate not null boolean
>>> by using CHAR(0).
>>> 
>>> This is a little bit annoying on migration topics.
>> 
>> While not move on to a cleaner approach during the migration and use a
>> "boolean not null"?
> 
> Sounds much too straight forward, not mysql-ish artistic enough...
> 
> Regards,
> Andreas


yes, if you do proper migration you should try to get rid of stupid design like this..
it is possible to stand on your head actually ... it just makes no sense if you are waiting for the bus.
the fact that mysql has something does not implicitly mean that it makes sense to have it as well.

a way to get around it would be ...

CREATE TYPE my_intentionally_broken_type ... ;).
but, i would not see that as recommendation actually :).

	regards,

		hans

--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de

  11. Re: char(0)

    Susanne Ebrecht <susanne@2ndquadrant.com> — 2011-10-18T14:38:37Z

    On 17.10.2011 16:41, Andreas Pflug wrote:
> Am 17.10.11 10:53, schrieb Thomas Kellerer:
>> Susanne Ebrecht, 17.10.2011 09:31:
>>> Hello,
>>>
>>> I couldn't find that somebody already mentioned it.
>>>
>>> PostgreSQL isn't supporting CHAR(0).
>>>
>>> An empty string has a length of 0.
>>>
>>> CHAR(0) can have two values: NULL and empty string.
>>>
>>> In MySQL it is very common to simulate not null boolean
>>> by using CHAR(0).
>>>
>>> This is a little bit annoying on migration topics.
>> While not move on to a cleaner approach during the migration and use a
>> "boolean not null"?
> Sounds much too straight forward, not mysql-ish artistic enough...

Depends if you want / are able to touch the application source code or not.

Susanne

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

  12. Re: char(0)

    Tom Lane <tgl@sss.pgh.pa.us> — 2011-10-18T17:17:15Z

    Susanne Ebrecht <susanne@2ndQuadrant.com> writes:
> On 17.10.2011 16:41, Andreas Pflug wrote:
>> This is a little bit annoying on migration topics.
>> While not move on to a cleaner approach during the migration and use a
>> "boolean not null"?
>> Sounds much too straight forward, not mysql-ish artistic enough...

> Depends if you want / are able to touch the application source code or not.

If you're expecting to move a mysql application to postgres with zero
source code changes, you're living in a fantasy world anyway ... but
this difference is hardly likely to be your worst problem.

AFAICT the SQL standard is perfectly clear on this.  *Values* of type
varchar can be of zero length, but that does not mean that you can
*declare* a column to be varchar(0), and that NOTE says specifically
that you can't.

			regards, tom lane