PostgreSQL - CREATE INDEX

Question

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?

Dan LaRocque · Accepted Answer · 2010-04-22 17:42:14Z

1

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...

answered Apr 22, 2010 at 17:42

Dan LaRocque

5,1932 gold badges24 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mocopera Over a year ago

Thanks for your reply. The link you have provided is very interesting and will help me in the future. It explains how to create an index on your custom data type (Point3D), but i need the index working on a field of the data type (Point3D.id). I need to use c data types because its necessary a high performance. "CREATE TYPE AS is essentially the same as the row type of a table..." and CREATE TYPE creates a new base type. Anyway, i just have created a function getPointID that simply returns the id of a Point3D and at the moment is working. :D Thanks for your time.

Kuberchaun · Accepted Answer · 2010-04-21 19:45:01Z

0

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;

answered Apr 21, 2010 at 19:45

Kuberchaun

30.5k8 gold badges54 silver badges59 bronze badges

2 Comments

nos Over a year ago

This question is really more about how to get indexes working on a custom type in postgresqu-

mocopera Over a year ago

First of all, thank you four your reply. Second, @nos is right, I want to get my indexes working on my c data types. I know null is not equal to another null value, but, in fact, this is the behaviour I want. The user can provide an id, in this case, to a point, but if not, id is null, so there will be many points with id field equal to null. Greets to all and thanks again.

Collectives™ on Stack Overflow

PostgreSQL - CREATE INDEX

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related