Converting to PostgreSQL from SQLite

  1. ‹ Older
  2. 3 years ago

    Emile S

    6 Jan 2017 Europe (France, Strasbourg)

    So, for PostgreSQL, empty = NULL ?

  3. Jean-Yves P

    6 Jan 2017 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    for sqlite also empty=null
    but sqlite accept any value in a field, so if you put '' in an integer sqlite will accept it, and not postgres
    you will then consider that '' is empty, but it is not null.

  4. Kem T

    6 Jan 2017 Pre-Release Testers, Xojo Pro, XDC Speakers Connecticut

    Right, NULL means "the absence of any value" whereas and empty string is still a string, and thus a value.

    To underscore Jean-Yves' point, SQLite does not do type checking, or even length checking. You can assign any value at all to any column, and extract any column as any type, even if it doesn't make sense. You can define a column as VARCHAR(2), then assign the full text of War and Peace to it. PostgreSQL won't allow any of that.

  5. Neil B

    6 Jan 2017 Pre-Release Testers

    Yes PostgreSQL lets you inserts a text representation of a number but not actual text that can't equal the data type.

    'this works
    dim s as string
    s = "1"
    db.SQLExecute("UPDATE Table SET IntegerColumn = '" + s + "' WHERE ID = 1")
    
    'this causes an error
    s = ""
    db.SQLExecute("UPDATE Table SET IntegerColumn = '" + s + "' WHERE ID = 1")

or Sign Up to reply!