Postgres column names are always lowercase.

[se-neaxplore]

Describe the bug Queriying a postgres database, the column name casing is not preserved.

To Reproduce

CREATE Table user1 (
    id uuid default gen_random_uuid(),
    user_id uuid,
    name text,
    PRIMARY KEY (id)
);

INSERT INTO user1 (
  user_id, name 
) VALUES 
  ( '3f10578f-6e3e-4c15-b77b-490b8b9ea8b1', 'user' );

CREATE Table user2 (
    id uuid default gen_random_uuid(),
    userId uuid,
    name text,
    PRIMARY KEY (id)
);

INSERT INTO user2 (
  userId, name 
) VALUES 
  ( '3f10578f-6e3e-4c15-b77b-490b8b9ea8b1', 'user' );

Running the following queries:

(j/execute! db ["select user_id as userId from user1"])

;; => [#:user1{:userid #uuid "3f10578f-6e3e-4c15-b77b-490b8b9ea8b1"}]

(j/execute! db ["select user_id as userId from user1"] {:builder-fn rs/as-unqualified-maps})

;; => [{:userid #uuid "3f10578f-6e3e-4c15-b77b-490b8b9ea8b1"}]

(j/execute! db ["select userId from user2"] )

;; => [#:user2{:userid #uuid "3f10578f-6e3e-4c15-b77b-490b8b9ea8b1"}]

(j/execute! db ["select userId from user2"] {:builder-fn rs/as-unqualified-maps})

;; => [{:userid #uuid "3f10578f-6e3e-4c15-b77b-490b8b9ea8b1"}]

(j/execute! db ["select userId as userId from user2"] {:builder-fn rs/as-unqualified-maps})

;; => [{:userid #uuid "3f10578f-6e3e-4c15-b77b-490b8b9ea8b1"}]

Expected behavior

The column names should be preserved, or respect the AS name.

From the docs:

as-unqualified-maps -- simple keywords as-is, e.g., :ID, :firstName,

https://github.com/seancorfield/next-jdbc/blob/develop/doc/result-set-builders.md

deps.edn

{:deps {
    org.clojure/clojure {:mvn/version "1.11.1"}
    com.github.seancorfield/next.jdbc {:mvn/version "1.3.894"}
    org.postgresql/postgresql {:mvn/version "42.6.0"}}}

Environment:

• OS: Linux
• Java Version: java 21.0.1 2023-10-17 LTS Java(TM) SE Runtime Environment (build 21.0.1+12-LTS-29) Java HotSpot(TM) 64-Bit Server VM (build 21.0.1+12-LTS-29, mixed mode, sharing)
• Clojure Version: 1.11.1
• Database: postgres:15.2-alpine
• Driver Library Version: org.postgresql/postgresql {:mvn/version "42.6.0"}

[seancorfield]

If you are using the default result set builder, what you get back from next.jdbc is what the JDBC driver provides as table and column names.

If you are using as-unqualified-maps, what you get back from next.jdbc is what the JDBC driver provides as column names.

Only if you use the -lower variant builders will next.jdbc attempt to lower-case anything.

Given that you are not stropping the column names in the DDL or aliases in the SQL, I would actually expect them to come back as lowercase with several databases (and as UPPERCASE with others).

[seancorfield]

SQL is not case-sensitive by definition (although some databases violate this definition). Some databases (and their drivers) behave as if everything were UPPERCASE (e.g., H2), some databases (and their drivers) behave as if everything were lowercase. A few have some aspects of case sensitivity -- MySQL treats table names as case sensitive if the host O/S is case sensitive but treats column names as case insensitive (so MySQL table names are case sensitive on Linux, but case insensitive on Windows and macOS!).

The way to get case sensitivity is to strop the identifiers (quote them) in the DDL and SQL.

None of this is happening in next.jdbc -- this is just how databases and their drivers work.

[se-neaxplore]

Big thanks for the detailed answer and taking the time to break it down. It was super helpful.

Appreciate it!