![]() ![]() Pascal case / camel case as described in our article To try to write application code so that it works across multiple database platforms.ĭatabase lock-in is the lesser of other evils. Involve lots of stats, complex analytics of which we've decided it's much more efficient to make the database the engine to perform that than It is best to make the decision consciously and play out the pros / cons of what you are gaining and losing. Product-lockin is a step that should not be taken lightly and should be considered on an application by application basis. Standards are there to minimize product/vendor lock-in,īut they often fall short of covering your needs. Product lock-in is a reality even when you have no vendors and when you are using open source. Or even a lower version of a database platform). ![]() It's a hard decision to make consciously because of the WHAT IFS (not just about betting on success of PostgreSQL,īut the fact that you can't market your work to people who need it to work for a different database platform Using PostgreSQL array support, complex type, and built-in regular expression support shamelessly just because it makes so many tasks so much succinct and faster. So we've been taking liberties using syntax that will probably not be portable such as Should really be timestamps or dates or even times and if you really want the time zone following your data around.įor most of the apps we are porting, we don't ever intend to go back nor have it work with anything but PostgreSQL, If you are converting from SQL Server 2005/2000 you have to consciously think about whether the columns you had the old way Which are equivalent to PostgreSQL types so mapping is much cleaner for 2008. This small little annoyance is a less of a concern in SQL Server 2008+ since 2008 introduced (DATE, TIME, DATETIMEOFFSET) PostgreSQL timestamp with time zone, timestamp without time zone, date, time. Which while designed for MS Access work equally well when writing raw SQL. In your database as we described in Using MS Access with PostgreSQL You to write Oracle compatible syntax- any of these will cast ('TRUE','FALSE','true','t','f', 'false')Īre all valid constructs in PostgreSQL but invalid in SQL ServerĪnd you could use 0 and 1 too if you install some autocasts PostgreSQL does have auto casts for boolean to text which allows So the way you would use PostgreSQL boolean in a where would take form: WHERE is_active = true It like a 0 / 1 number is not allowed unless you define type casts for such behavior. In PostgreSQL, you would use a boolean as if it were a boolean and treating Such as you can't add bits unless you cast them to integers etc.īut for sake of argument we can wave our hands and call it a small integer that would be In SQL Server you would use a bit in SQL as if it were an integer - sorta. When writing raw SQL, however, they are different beasts. we work withĬast both SQL Server bit and PostgreSQL boolean to the boolean type of the application language. It doesn't cast naturally to a boolean type in applications like SQL Server's bit or PostgreSQL boolean so we suggest you stay away from itįor boolean use. ![]() Leo really loves the PostgreSQL boolean type so much so that he's willing to trade portability for having a real true/false data type.Ī bit which can be more than 1 in length, but SQL Server, similar to most other relational databases I can think ofĭoesn't really have a true boolean type even in SQL Server 2008 and upcoming version. Here are a couple of key differences and similarities in data types between the two. SQL Server and PostgreSQL data type differences and equivalents With their CREATE TABLE statements, data types, and how they handle other things that makes porting applications not so trivial. In thisĪrticle we'll describe some things to watch out for and provide a function we wrote to automate some ofĪlthough both databases are fairly ANSI-SQL compliant, there are still differences We've been working on converting some of our SQL Server apps to PostgreSQL. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |