0
  • As per the stackoverflow question HERE postgres column does not store dashes for the UUID data type
  • Yet when I load the value of any UUID column using pg-promise it always shows dashes
  • How do I retrieve these UUIDs without dashes using pg-promise

Code sample illustrating the problem

var pgPromise = require("pg-promise"),
    pgp = pgPromise({}),
    db = pgp({
        database: "mydatabase",
        host: "localhost",
        password: "somepass",
        port: 5432,
        ssl: false,
        user: "myuser"
    });
pgp.pg.types.setTypeParser(20, parseInt);
db.query("CREATE TABLE test(myid uuid not null primary key)")
.then((e => {
    var r = require("crypto").createHash("md5").update("test data").digest("hex");
    return db.query("INSERT INTO test(myid) VALUES($1) ON CONFLICT DO NOTHING", [r])
}))
.then((e => db.query("SELECT * FROM test")))
.then((e => (console.log(e), db.query("DROP TABLE test"))))
.then((() => console.log("test success!")))
.catch((e => console.error(e)));
PirateApp
  • 5,433
  • 4
  • 57
  • 90
  • 3
    You don't. pg-promise retrieves the string representation of the uuid, not some binary representation, and the string format always contains the dashes. Just remove them afterwards if you don't like them. – Bergi Jul 29 '21 at 12:25
  • thank you for the answer!so this has to be done at the application level each time while retrieving the id, does pg-promise offer any type of query transformer – PirateApp Jul 29 '21 at 12:28
  • 1
    @PirateApp Why would pg-promise get involved into any of these? It is a framework for executing SQL as you specify it, and that's it. So pg-promise isn't relevant here. – vitaly-t Jul 29 '21 at 12:30
  • 1
    You might be able to change the query to return a string of your choosing instead of an uuid, or you might be able to use a [custom `pg-types` parser](https://github.com/brianc/node-pg-types) but I don't see why you would want to do that everywhere. It's only a display issue. – Bergi Jul 29 '21 at 12:32
  • "*Code sample illustrating the problem*" - I don't see where the problem is, can you post out the error you're getting or explain how it doesn't work as expected? – Bergi Jul 29 '21 at 12:34
  • @Bergi the code sample prints a uuid which contains a dash, i want it to return one without dashes. the dashes themselves are not stored in any postgres columns but only displayed – PirateApp Jul 29 '21 at 12:50
  • 1
    @PirateApp Just transform the UUID strings that you got by removing the dashes. It is trivial, and you needlessly overcomplicate it. – vitaly-t Jul 29 '21 at 12:52
  • 1
    @PirateApp You mean the `console.log(e)` bothers you? Just write `console.log({myid: e.myid.replaceAll("-", "")})` – Bergi Jul 29 '21 at 12:54
  • 1
    Btw, don't use `parseInt` for bigints, [use `BigInt` instead](https://stackoverflow.com/a/39176670/1048572) – Bergi Jul 29 '21 at 12:56

1 Answers1

1

This line did the trick

pgp.pg.types.setTypeParser(pgp.pg.types.builtins.UUID, (val) => {
    val === null ? null : val.replace(/-/g, '')
});

Thank you for your suggestions @vitaly-t and @Bergi

PirateApp
  • 5,433
  • 4
  • 57
  • 90