Thread: macaddr data type issue
Hi, I am looking for some confirmation and perhaps some explanation of and for the following observation. If I use "000000-000000" as input to a macaddr data type element in my database no value appears to be stored. I was intending to use this value as a "missing data" marker in my application. I've looked for documentation high and low but haven't been able to find anything regarding this behavior. I did stumble upon some source code, which I believe is relevant, which suggests that the code's author was planning for a "missing value's" value. Unfortunately, the macaddr data type output function does not seem to produce the "00:00:00:00:00:00" string that I would expect. It seems to me that if I were to input and the database would accept "000000-000000" as indicative of missing values that the database should output such a value when it is encountered in a dataset. At very least, it would seem that some additional documentation might be warranted. I suspect I am missing something here. Can anyone offer confirmation of this behavior and an explanation for, what seems to me to be, this counter-intuitive behavior? Thanks for your time and effort on my behalf. Gayland
Hi Gayland,
What you're attempting to insert is being interpreted as the NULL value
:
techdocs=> create table foo (bar macaddr);
CREATE
testdb=> insert into foo values ('00:00:00:00:00:00');
INSERT 75485253 1
testdb=> insert into foo values ('00:00:00:00:00:01');
INSERT 75485256 1
testdb=> insert into foo values ('000000-000000');
INSERT 75485258 1
testdb=> insert into foo values ('000000-000001');
INSERT 75485261 1
testdb=> insert into foo values (NULL);
INSERT 75485269 1
testdb=> select * from foo;
bar
-------------------
00:00:00:00:00:01
00:00:00:00:00:01
(5 rows)
testdb=>
You can probably use the NULL values (or your 000000-000000 value) as a
placeholder/"missing data" marker.
Does this help?
:-)
Regards and best wishes,
Justin Clift
Gayland Gump wrote:
>
> Hi,
>
> I am looking for some confirmation and perhaps some explanation of and for
> the following observation. If I use "000000-000000" as input to a
> macaddr data type element in my database no value appears to be stored. I
> was intending to use this value as a "missing data" marker in my
> application. I've looked for documentation high and low but haven't been
> able to find anything regarding this behavior. I did stumble upon some
> source code, which I believe is relevant, which suggests that the code's
> author was planning for a "missing value's" value. Unfortunately, the
> macaddr data type output function does not seem to produce the
> "00:00:00:00:00:00" string that I would expect.
>
> It seems to me that if I were to input and the database would accept
> "000000-000000" as indicative of missing values that the database should
> output such a value when it is encountered in a dataset. At very least,
> it would seem that some additional documentation might be warranted. I
> suspect I am missing something here. Can anyone offer confirmation of
> this behavior and an explanation for, what seems to me to be, this
> counter-intuitive behavior?
>
> Thanks for your time and effort on my behalf.
>
> Gayland
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
Justin Clift <justin@postgresql.org> writes:
> What you're attempting to insert is being interpreted as the NULL value
Actually, it's not NULL. The curious behavior comes from a test in the
macaddr_out routine that causes it to produce an empty-string display if
the MAC address is all zeroes:
if ((hibits(addr) > 0) || (lobits(addr) > 0))
{
sprintf(result, "%02x:%02x:%02x:%02x:%02x:%02x",
addr->a, addr->b, addr->c, addr->d, addr->e, addr->f);
}
else
{
result[0] = '\0'; /* special case for missing address */
}
This seems a tad bizarre to me, if not an outright bug. The right,
SQL-approved way to represent "missing data" is as a NULL; there's no
rationale I can see for treating an all-zeroes address like this.
There's also a special case in macaddr_in that accepts an empty input
string as meaning an all-zeroes address. This seems bogus as well.
I'm inclined to rip out both special cases, so that an all-zeroes MAC
address is handled exactly like any other address. Comments?
regards, tom lane
On Tue, 21 Aug 2001, Tom Lane wrote: > SQL-approved way to represent "missing data" is as a NULL; there's no > rationale I can see for treating an all-zeroes address like this. > > There's also a special case in macaddr_in that accepts an empty input > string as meaning an all-zeroes address. This seems bogus as well. > > I'm inclined to rip out both special cases, so that an all-zeroes MAC > address is handled exactly like any other address. Comments? Completely agreed. I stepped on this rake once. All zeros is a valid mac address and is very different from null. Thanks -- Alex Pilosov | http://www.acedsl.com/home.html CTO - Acecape, Inc. | AceDSL:The best ADSL in the world 325 W 38 St. Suite 1005 | (Stealth Marketing Works! :) New York, NY 10018 |
* Tom Lane <tgl@sss.pgh.pa.us> [010821 14:10]:
> Justin Clift <justin@postgresql.org> writes:
> > What you're attempting to insert is being interpreted as the NULL value
>
> Actually, it's not NULL. The curious behavior comes from a test in the
> macaddr_out routine that causes it to produce an empty-string display if
> the MAC address is all zeroes:
>
> if ((hibits(addr) > 0) || (lobits(addr) > 0))
> {
> sprintf(result, "%02x:%02x:%02x:%02x:%02x:%02x",
> addr->a, addr->b, addr->c, addr->d, addr->e, addr->f);
> }
> else
> {
> result[0] = '\0'; /* special case for missing address */
> }
>
> This seems a tad bizarre to me, if not an outright bug. The right,
> SQL-approved way to represent "missing data" is as a NULL; there's no
> rationale I can see for treating an all-zeroes address like this.
>
> There's also a special case in macaddr_in that accepts an empty input
> string as meaning an all-zeroes address. This seems bogus as well.
>
> I'm inclined to rip out both special cases, so that an all-zeroes MAC
> address is handled exactly like any other address. Comments?
My gut feeling is that Tom is correct, and there should be no special
cases here.
LER
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Alex Pilosov <alex@pilosoft.com> writes: > On Tue, 21 Aug 2001, Tom Lane wrote: >> I'm inclined to rip out both special cases, so that an all-zeroes MAC >> address is handled exactly like any other address. Comments? > Completely agreed. I stepped on this rake once. All zeros is a valid mac > address and is very different from null. Okay, patch applied. Gayland, if you're in a hurry for a solution you could drop the CVS version of mac.c into 7.1.* --- see http://www.ca.postgresql.org/cgi/cvsweb.cgi/pgsql/src/backend/utils/adt/mac.c regards, tom lane