Function min(uuid) does not exist in postgresql

Question

I have imported tables from Postgres to hdfs by using sqoop. My table have uuid field as primary key and my command sqoop as below:

sqoop import --connect 'jdbc:postgresql://localhost:5432/mydb' --username postgreuser --password 123456abcA --driver org.postgresql.Driver --table users --map-column-java id=String --target-dir /hdfs/postgre/users --as-avrodatafile --compress -m 2

But I got the error:

Import failed: java.io.IOException: org.postgresql.util.PSQLException: ERROR: function min(uuid) does not exist

I tried executed the sql command: SELECT min(id) from users and got the same error. How could I fix it ? I use Postgres 9.4, hadoop 2.9.0 and sqoop 1.4.7

Answer 1

I'd like to credit @robin-salih 's answer, I've used it and implementation of min for int, to build following code:

CREATE OR REPLACE FUNCTION min(uuid, uuid)
RETURNS uuid AS $$
BEGIN
    IF $2 IS NULL OR $1 > $2 THEN
        RETURN $2;
    END IF;

    RETURN $1;
END;
$$ LANGUAGE plpgsql;


create aggregate min(uuid) (
  sfunc = min,
  stype = uuid,
  combinefunc = min,
  parallel = safe,
  sortop = operator (<)
);

It almost the same, but takes advantages of B-tree index, so select min(id) from tbl works in few millis.

P.S. I'm not pgsql expert, perhaps my code is somehow wrong, double check before use in production, but I hope it uses indexes and parallel execution correctly. I've made it just from sample code, not digging into theory behind aggregates in PG.

Answer 2

Postgres doesn't have built-in function for min/max uuid, but you can create your own using the following code:

CREATE OR REPLACE FUNCTION min(uuid, uuid)
RETURNS uuid AS $$
BEGIN
    IF $2 IS NULL OR $1 > $2 THEN
        RETURN $2;
    END IF;

    RETURN $1;
END;
$$ LANGUAGE plpgsql;


CREATE AGGREGATE min(uuid)
(
    sfunc = min,
    stype = uuid
);