Thread: cast bytea to macaddr

cast bytea to macaddr

From

Matt Zagrabelny

Date:

28 September 2023, 18:00:36

Greetings Pg folks,

I have a database schema (not mine) that uses bytea to represent a MAC address. I'd like to create a view that casts that field into a MACADDR type.

# \d lease4
Table "public.lease4"
Column │ Type │ Collation │ Nullable │ Default
════════════════╪══════════════════════════╪═══════════╪══════════╪═════════
hwaddr │ bytea │ │ │

# select hwaddr from lease4;
hwaddr
════════════════
\x9cebe803e3b9

It looks like I can cast the bytea to text and then manipulate that and finally cast it to macaddr.

Is there a better, or more canonical, way to convert/cast this field?

Thanks for any help!

-m

Re: cast bytea to macaddr

From

Matt Zagrabelny

Date:

28 September 2023, 21:24:08

Hi Boris,

[bringing this email back to the mailing list]

A couple of questions:

1. What do the double parenthesis '(( Values .... ))' mean?

2. What is the 'x(field)' in the column alias?

Thanks for educating me!

-m

On Thu, Sep 28, 2023 at 10:34 AM Boris Zentner <bzm@2bz.de> wrote:

Hi matt,

This is my take. I guess this is also your solution ( encode(fteld, 'hex'): :macaddr )

SELECT encode(fteld, 'hex'): :macaddr FROM ((VALUES (decode('aabbccddeeff', 'hex'))))
AS x(field)
--
Boris

Am 28.09.2023 um 17:01 schrieb Matt Zagrabelny <mzagrabe@d.umn.edu>:

Greetings Pg folks,

I have a database schema (not mine) that uses bytea to represent a MAC address. I'd like to create a view that casts that field into a MACADDR type.

# \d lease4
Table "public.lease4"
Column │ Type │ Collation │ Nullable │ Default
════════════════╪══════════════════════════╪═══════════╪══════════╪═════════
hwaddr │ bytea │ │ │

# select hwaddr from lease4;
hwaddr
════════════════
\x9cebe803e3b9

It looks like I can cast the bytea to text and then manipulate that and finally cast it to macaddr.

Is there a better, or more canonical, way to convert/cast this field?

Thanks for any help!

-m

Re: cast bytea to macaddr

From

Boris Zentner

Date:

28 September 2023, 23:34:32

Hi Matt,

1. What do the double parenthesis '(( Values .... ))' mean?

Nothing, it was easier to continue typing and close one more on the phone. Single parenthesis are enough.

2. What is the 'x(field)' in the column alias?

X(field) is just to make the example more readable. And name the example table x with the column field to illustrate the main part of the example encode(yourfield, 'hex')::macaddr