PostgreSQL - CREATE INDEX

Question

Hi! I'm working with PostgreSQL to create some data types written in C.

For example, I have: typedef struct Point3D { char id[50]; double x; double y; double z;
} Point3D;

The input and output functions are working properly.

But the problem is the following: Every id of Point3D must be unique (and can be NULL), so I have decided to create an unique index on this field id, but is that possible?

I'm thinking in something like this: create unique index test_point3d_idx on test_point3d (( getID(columname) )); where getID returns the field ID of columname.

But I need to implement getID and I am really blocked.

Any advice?

Answer 1

A unique column will allow multiple values of NULL because NULL is an unknown value so one null compared to another can never really be considered to be equal. Now logically you might consider NULL = NULL to be true, but unique constraint doesn't work that way.

Simple example to prove it.

CREATE TABLE test2
(
  unq_id integer NULL,
  CONSTRAINT uq_test2 UNIQUE (unq_id)
);


INSERT INTO test2 
SElECT NULL;

INSERT INTO test2 
SElECT NULL;

INSERT INTO test2 
SElECT NULL;

SELECT *
FROM test2;

Answer 2

The Postgres manual section "Interfacing Extensions To Indexes" explains indexes on user-defined types like your Point3D. That requires a fair amount of work. I don't know any shortcuts.

Unrelated to your question: are you sure you need this C-language Point3D datatype? Mistakes in such a datatype definition can "confuse or even crash the server". I presume the same applies to C-language operator functions supporting it.

Could you create tables with four columns, one for each Point3D field? Otherwise, could you forego C in favor of a simple CREATE TYPE point3d AS (id char(50), x float8, y float8, z float8)? Perhaps not, but worth a shot...