A schema is a namespace – a logical thing. It is used to organize the names of database objects. It has nothing to do with the way the data is stored. A tablespace is a physical thing. It’s a container for data and has nothing to do with the logical organization of the database objects. … Read more
Another thing to be aware of is that you can copy files out from under a live database. Given that you may have a possibly large database, you could just copy it OOB from your test/production machine to another machine. Depending on the write load of the machines it may be advisable to trigger a … Read more
sqlite> .header on sqlite> .mode column sqlite> create table ABC(A TEXT, B VARCHAR); sqlite> pragma table_info(ABC); cid name type notnull dflt_value pk ———- ———- ———- ———- ———- ———- 0 A TEXT 0 0 1 B VARCHAR 0 0
1M records inserted in 22 minutes works out to be 758 records/second. Each INSERT here is an individual commit to disk, with both write-ahead log and database components to it eventually. Normally I expect that even good hardware with a battery-backed cache and everything you will be lucky to hit 3000 commit/second. So you’re not … Read more
Calling Filemaker Pro, Access for the Mac is kind of like saying, Mac OS X is Windows for the Mac. They’re both in the same category of software, they’re integrated programming environments. It’s like you have MySQL, PHP, HTML and your editor put together in a GUI. Comparing the two, they both have pros an … Read more
From our experience, data provenance includes only high level view of the system for business users, so they can roughly navigate where their data come from. It’s provided by variety of modeling tools or just simple custom tables and charts. Data lineage is a more specific term and includes two sides – business (data) lineage … Read more
Plain freeform text. Validating all the world’s post/zip codes is too hard; a fixed list of countries is too politically sensitive; mandatory state/region/other administrative subdivision is just plain inappropriate (all too often I’m asked which county I live in–when I don’t, because Greater London is not a county at all). More to the point, it’s … Read more
If you don’t have foreign keys pointing your id you could add new column, fill it, drop old one and rename new to old: alter table my_table add column new_id bigint; begin; update my_table set new_id = id where id between 0 and 100000; commit; begin; update my_table set new_id = id where id between … Read more
Run postgresql in a background process. Start a separate thread in your application that would start a postgresql server in local mode either by binding it to localhost with some random free port or by using sockets (does windows support sockets?). That should be fairly easy, something like: system(“C:\Program Files\MyApplication\pgsql\postgres.exe -D C:\Documents and Settings\User\Local Settings\MyApplication\database … Read more
As the names indicate, those are template databases for creating new databases. template1 is the one used by default. You can alter / add / remove objects there to affect every newly created DB. CREATE DATABASE basically makes a copy of it on the file level (very fast) to create a new instance. template0 starts … Read more