2023-04-11 sql | Clojure Slack Archive

sql

Dumch 2023-04-11T18:00:15.348379Z

I am trying to pass the Postgres 65535 parameter limit by utilizing "unnest". It works like a charm for every type except for arrays. Working example:

(db-dsl/exec-sql
  "INSERT INTO test (num,dob) (SELECT * FROM UNNEST (?::bigint[], ?::date[])) RETURNING *"
  (into-array [1 2])
  (into-array ["01.01.2010" "01.01.1990"]))

But I can't pass an array type:

(db-dsl/exec-sql
  "INSERT INTO test (nums,dob) (SELECT * FROM UNNEST (?::bigint[][], ?::date[])) RETURNING *"
  (into-array [(into-array [1 2]) (into-array [1 2])])
  (into-array ["01.01.2010" "01.01.1990"]))

Have you tried to achieve something like this?

Dumch 2023-04-11T18:00:35.042269Z

Table example:

CREATE TABLE test (
  oid	BIGSERIAL PRIMARY KEY,
  num	BIGINT,
  nums	BIGINT[],
  dob	TIMESTAMP);

seancorfield 2023-04-11T18:05:51.959409Z

If you're using next.jdbc under the hood, there are "type hint" functions that may help...

seancorfield 2023-04-11T18:06:10.337369Z

https://cljdoc.org/d/com.github.seancorfield/next.jdbc/1.3.865/api/next.jdbc.types

Dumch 2023-04-11T18:39:46.724939Z

Thank you! I tried:

(db-dsl/exec-sql
 "INSERT INTO test (nums) (SELECT * FROM UNNEST (?::bigint[][])) RETURNING *"
 (jdbc-types/as-array (into-array [(into-array [1 2]) (into-array [1 2])])))

The result is the same as before:

ERROR: column \"nums\" is of type bigint[] but expression is of type bigint\n  Hint: You will need to rewrite or cast the expression.\n  Position: 33

If there is a type hint like as-array-of-arrays 🙂

seancorfield 2023-04-11T18:46:37.843749Z

Sounds like it is UNNESTing things more than one level?

seancorfield 2023-04-11T18:46:57.069909Z

(I don't use PG so I'm not familiar with how that is supposed to work)

Dumch 2023-04-11T21:05:35.034989Z

Found a way with unnesting separately. It can pass 65k rows, don't know yet whether performance could suffer from this approach (compared to the previous version).

(db-dsl/exec-sql
  "INSERT INTO test (nums,dob)
    (SELECT unnest_2d_1d(a1), unnest(a2) FROM (SELECT ?::bigint[][], ?::date[]) t(a1,a2))
  RETURNING *"
  (into-array [(into-array [1 2]) (into-array [1 2])])
  (into-array ["01.01.2010" "01.01.1990"]))

Need a procedure to reduce an array dimension, like I used unnest_2d_1d Can be found here: https://stackoverflow.com/questions/8137112/unnest-array-by-one-level

🤯 1

Clojurians Log v2

sql