Converting to PostgreSQL from SQLite

Databases
21

So, for PostgreSQL, empty = NULL ?

22

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.

23

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.

24

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")