Thread: char(0)
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
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
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? --=20 =A0Simon Riggs=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 http:/= /www.2ndQuadrant.com/ =A0PostgreSQL Development, 24x7 Support, Training & Services
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
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
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/
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
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
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
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
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
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