The following bug has been logged online:

Bug reference: 1223
Logged by: Shigeaki Momose

Email address: sig_momose (AT) yahoo (DOT) co.jp

PostgreSQL version: 7.4.2

Operating system: Linux

Description: RedHat Linux 8

Details:

The type of macaddr column could not work "Like" pattern matches.

The detail as follows.

[...]$ createdb mac_test
CREATE DATABASE
[...]psql -d mac_test
Welcome to psql 7.4.2, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

mac_test=# create table addr( id integer ,
mac_test(# mac macaddr,
mac_test(# constraint addr_pk primary key(id) );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "addr_pk" for
table "addr"
CREATE TABLE
mac_test=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | addr | table | momo
(1 row)

mac_test=# \d addr
Table "public.addr"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
mac | macaddr |
Indexes:
"addr_pk" primary key, btree (id)

mac_test=# insert into addr values( 1, '001006-000000');
INSERT 29383 1
mac_test=# insert into addr values( 2, '001006-000002');
INSERT 29384 1
mac_test=# insert into addr values( 3, '001006-000003');
INSERT 29385 1
mac_test=# insert into addr values( 4, '001006-000001');
INSERT 29386 1
mac_test=# insert into addr values( 5, '001006-00000f');
INSERT 29387 1
mac_test=# insert into addr values( 6, '001006-00000d');
INSERT 29388 1
mac_test=# insert into addr values( 7, '001006-00000a');
INSERT 29389 1
mac_test=# insert into addr values( 8, '001006-00000c');
INSERT 29390 1
mac_test=# insert into addr values( 9, '001006-045f23');
INSERT 29391 1
mac_test=# insert into addr values( 10, '001006-045f2e');
INSERT 29392 1
mac_test=# select * from addr;
id | mac
----+-------------------
1 | 00:10:06:00:00:00
2 | 00:10:06:00:00:02
3 | 00:10:06:00:00:03
4 | 00:10:06:00:00:01
5 | 00:10:06:00:00:0f
6 | 00:10:06:00:00:0d
7 | 00:10:06:00:00:0a
8 | 00:10:06:00:00:0c
9 | 00:10:06:04:5f:23
10 | 00:10:06:04:5f:2e
(10 rows)

mac_test=# select * from addr where mac like '%2e';
ERROR: operator does not exist: macaddr ~~ "unknown"
HINT: No operator matches the given name and argument type(s). You may need
to add explicit type casts.
mac_test=#


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Like is for comparing texts, so convert to text?

maybe try something like:

SELECT * FROM addr WHERE mac::TEXT LIKE '%2E';

Hope this help, have a nice day,

--
Fabien Coelho - coelho (AT) cri (DOT) ensmp.fr

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)