How to INSERT INTO ... RETURNING for matrices in PostgreSQL?

Question

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

score 1 · Accepted Answer · answered May 25 '17 at 04:30

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)

How to INSERT INTO ... RETURNING for matrices in PostgreSQL?

1 Answers1