0

I have a schema that uses composite types and domains, similar to this:

create domain float3 as double precision[3];

create type Sample as (
    pos float3,
    dist double precision
);

create table scans (
    id uuid not null primary key,
    samples Sample[] not null
);

If there were much fewer Sample's per scan, then of course I would want a samples table with a scan_id foreign key, but I have determined that is not what I want - the array is most suitable for my use-case.

I have some Rust types,


#[derive(sqlx::Type, Debug, Serialize, Deserialize)]
#[sqlx(type_name = "Float3")]
pub struct Float3([f64; 3]);
// I wish I could use a type alias instead

#[derive(sqlx::Type, Debug, Serialize, Deserialize)]
#[sqlx(type_name = "Sample")]
pub struct Sample {
    pub pos: Float3,
    pub dist: f64,
}

I would love to be able to INSERT a row using Rust. I'm trying to use sqlx but I could move to a different library if it helped in this case.

let id = Uuid::from_v4();
let samples: Vec<Sample> = ...;
sqlx::query!(
  "INSERT INTO scans (id, samples) values ($1, $2)",
  id,
  samples
)...;

Unfortunately I get the error unsupported type _sample for param #2. _sample is the type name auto generated by Postgres as the type for the array field on the scans table. If I have a field that is a single Sample, I still cannot insert, with the error unsupported type sample for param #2.

I'd really rather not just make everything jsonb (which is my backup plan), I'd prefer to use composite types if possible.

DoubleEwe
  • 151
  • 8
  • Why not use a normalized data model with a flat `Sample` - `x`, `y`, `z` and `dist`? – Stefanov.sm Jul 14 '23 at 09:19
  • I don't want to incorporate Float3 into Sample because I also have a Float4x4 (a 4x4 matrix, a 2d array of 16 total values) which is decidedly less convenient to have as multiple variables. I don't want to normalise the `Sample[]` column into a `samples` table because the number of samples is very high and they only need to ever be accessed in context of a `scan`. – DoubleEwe Jul 15 '23 at 01:30

0 Answers0