Format specifier for pointer in PostgreSQL?

Question

I know these threads here and here about pointers in C and printing pointer's address. I want to store the pointer to the postgreSQL 9.4 database with a partial index because selection is always from a small subset of the whole data and entries are always selected in order in interals from the database.

I have a big file where I know two byte positions of the event interval: end and start. I know also the maximum file size:

## Pointer to that interval
# Start index in dec: 140
# End index in dec: 666
# File size is :  241520288

Table events and its eventPointer field

CREATE TABLE events
    (
        event_id SERIAL PRIMARY KEY NOT NULL, 
        measurement_id INTEGER NOT NULL, 
        eventPointer POINTER NOT NULL, -- TODO here for type!
        is_active BOOLEAN DEFAULT FALSE
    );

where I am not sure about the datatype and how it is efficient to store such an data interval for the partial index. The partial index is created by

CREATE UNIQUE INDEX dir_events 
    ON events (measurement_id)
    USING btree
    (eventPointer)
    WHERE is_active;

I can store these as figures but would like to have a direct pointer to the specific interval. I would like store pointers to the big file in partial index such that I can do fast selections. It may be possible that postgreSQL can do these pointers itself.

How can you store pointer in PostgreSQL's partial index?

@alk I am just testing what would be the optimum way to store references to my big data file. Pointers, indexes, ... I actually do not know if there is any benefit of such pointers. Possible, just stored decimal indexes but my intuition just says use pointers. — Léo Léopold Hertz 준영, Jul 10 '15 at 13:17
What about these types: http://www.postgresql.org/docs/9.3/static/rangetypes.html — alk, Jul 10 '15 at 14:38

score 1 · Accepted Answer · answered Jul 10 '15 at 13:20

1

There is no reason or benefit to storing these file indexes as pointers.

Just store the indexes using the INTEGER type.

answered Jul 10 '15 at 13:20

caf

233,326
40
323
462

I have two fields: `index_start` and `index_end` which tells where is the interval of the event. I can create two fields in the table but I am not sure if it is too much. Probably one field enough but then I should be parsing the numbers. I think I cannot store list to the field. Probably, I just should make an external table which list all event characteristics `event_id`, `start_event`, and `end_event`. – Léo Léopold Hertz 준영 Jul 10 '15 at 13:45
2

If you need both the start and the end then just put two fields in the table. Alternatively you could store just the start and the length. – caf Jul 10 '15 at 14:04

Format specifier for pointer in PostgreSQL?

1 Answers1