Mirror of forum.nim-lang.org

7957 :: The `db_postgres`, distinguish "" from NULL

• alexeypetrushin (orginal) [2021-05-10T00:39:43+02:00] view original

  Is there a way to distinguish Empty String from NULL in db_postgres?

  The docs mention that """... NULL database values will be converted to nil ...""", but it seems like it's not always working.

  In the example below there are 2 strange things:

  • The definition of column doesn't indicate if it's nullable or not.
  • Empty empty string returned as a value instead of nil.

  import postgres, db_postgres, strutils, sequtils
  
  let conn = open("localhost:5432", "postgres", "", "nim_test")
  
  let batch = """
    drop table if exists test_users;
    
    create table test_users(
      name varchar(100) not null,
      nick varchar(100)
    );
    
    insert into test_users (name, nick) values ('Jim', null)
  """
  for part in batch.split(";"): conn.exec(sql(part))
  
  var columns: DbColumns
  for row in conn.instantRows(columns, sql("select name, nick from test_users")):
    echo columns[0].typ.notNull # ==> false   <- Error, should be true
    echo columns[1].typ.notNull # ==> false
    echo row[0]                 # ==> "Jim"
    echo row[1]                 # ==> ""      <- Error, should be nil
  Run

---

• mikra (orginal) [2021-05-10T01:24:38+02:00] view original

  be careful - PostgreSQL's behaviour differs from Oracle : an empty string is not null there (same as in SQLServer)

---

• Araq (orginal) [2021-05-10T07:10:41+02:00] view original

  Iirc you cannot distinguish NULL from "" in the db_ modules and have to use other Nimble packages instead. There is "Norm", "Ormin" and others.

---

• geohuz (orginal) [2021-08-20T05:40:30+02:00] view original

  I have a similar question, for the following statement:

  db.exec(sql"insert into data (name) values (?)", name)
  Run

  the name field could be a string value or null, what is the best way to deal with it?

---