1

Approximate size and type of c_matrix_data is 2k x 3k double which is equivalent max to rand(5000,5000) in Matlab 2016b. The random set selection of such data corresponds max 1 GB size (so BYTEA ok) for many measurements, presenting about 5% of all measurements. Queries to CREATE and INSERT bulk with RETURNING

DROP TABLE IF EXISTS measurements, c_matrices;    
CREATE TABLE measurements (
        measurement_id SERIAL PRIMARY KEY NOT NULL,
        db_id INTEGER NOT NULL,
        patient_id INTEGER NOT NULL,
        channel_id INTEGER NOT NULL,
        c_id INTEGER NOT NULL,
        time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        CONSTRAINT no_duplicate_measurements UNIQUE (time),
        CONSTRAINT no_same_measurements UNIQUE (db_id, patient_id, channel_id, c_id)
);

CREATE TABLE c_matrices (
        c_id SERIAL PRIMARY KEY NOT NULL,
        measurement_id INTEGER NOT NULL,
        event_index_start INTEGER NOT NULL,
        event_index_end INTEGER NOT NULL,
        c_matrix_data BYTEA NOT NULL,
        c_size_in_bytes INTEGER NOT NULL,
        UNIQUE (measurement_id, event_index_start, event_index_end)
);

-- http://dba.stackexchange.com/a/107062/69807
WITH ins1 AS (
   INSERT INTO measurements (measurement_id, db_id, patient_id, channel_id) -- TODO not sure since this autogenerated
   VALUES (1, 2, 3, 4) -- if data comes from client app
   -- SELECT ... if data from other db TODO  how to do this here SELECT c_id FROM c_matrices WHERE measurement_id==777 
   RETURNING measurement_id  -- generating a serial ID
   )
INSERT INTO c_matrices (measurement_id, event_index_start, event_index_end, c_matrix_data, c_size_in_bytes)
    VALUES (1, 2, 3, 'testBinaryData', 1234) 
    RETURNING c_id -- generating a serial id -- TODO right?
    FROM   ins1;

I run psql data -f creates.sql and get where the error comes from INSERT INTO ... RETURNING

DROP TABLE
CREATE TABLE
CREATE TABLE
psql:creates.sql:38: ERROR:  syntax error at or near "FROM"
LINE 10: FROM   ins1;

PostgreSQL: 9.4
OS: Debian 8.5 64 bit
Client application: Matlab 2016b
Linux kernel: 4.6 of backports
Hardware: Asus Zenbook UX303UA

  • So 100M data points per measurement? How many of those do you want to store? Also, how do you plan to query them? – dezso Oct 15 '16 at 18:39
  • @dezso I want to store 1-5% of them for internal quality assurance. There is now the CREATE of tables. - - This field is open matrix_data double TODO | NOT NULL. What do you think? – Léo Léopold Hertz 준영 Oct 15 '16 at 18:40
  • @dezso I plan to use RETURNING in querying the database. What do you think? Please, see the body. – Léo Léopold Hertz 준영 Oct 15 '16 at 18:50
1

Looks good, though I have to wonder if this isn't a better match for PostGIS's raster, or another tool entirely.

INSERT INTO c_matrices (measurement_id, event_index_start, event_index_end, c_matrix_data, c_size_in_bytes)
    VALUES (1, 2, 3, 'testBinaryData', 1234) 
    RETURNING c_id -- generating a serial id -- TODO right?
    FROM   ins1;

Not sure what this is supposed to do. To have a FROM you must have a SELECT, RETURNING comes after FROM. Close though.

-- Sample data.
# CREATE TABLE foo (id serial, b int);

-- Demo
# INSERT INTO foo (b) SELECT 42 RETURNING id;
 id 
----
  1
(1 row)
  • What would be your approach with PostGIS's raster? – Léo Léopold Hertz 준영 May 25 '17 at 9:46
  • 1
    @LéoLéopoldHertz준영 Create a raster 2k x 3k double, and populate it. PostGIS has MapAlgebra functions and the like that you can run on the raster. It depends on what kinds of searching and indexing you're doing, but a raster is essentially a matrix under the hood: a visual representation of data. – Evan Carroll May 25 '17 at 17:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.