macaddr data type issue

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

* 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

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 |

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