If you use SQL Server, you’re used to the database doing page verification for you as the sensible default. If you want SQL Server to not verify data, you have to do a bit of extra work. Naturally, I would’ve assumed that this was the case with other databases since, after all, having good data on disk is important. [caption id=“attachment_1065” align=“alignright” width=“300”] Not quite a check sum, but delicious enough.
Why would anyone want to use PostgreSQL instead of SQL Server? There are a lot of factors to consider when choosing how to store your data. Sometimes we need to look deeper than the standard choice and consider something new. If you’re starting a brand new project, where should you store your data? Here are ten reasons why you might want to consider PostgreSQL over SQL Server.
Releases Every Year Let’s face it, waiting three to five years for new functionality to roll out in any product is painful.
I recently covered the internals of a row in PostgreSQL, but that was just the storage piece. I got more curious and decided that I would look into what happens when a row gets updated. There are a lot of complexities to data, after all, and it’s nice to know how our database is going to be affected by updates.
Getting Set Up I started by using the customer table from the pagila sample database.
I answered a question a few days ago on Stack Overflow about PostgreSQL & SQL Server btree storage fundamentals. After I answered the question, I started thinking more and more about how PostgreSQL stores data on the row. Rather than be content to live in ignorance, I went digging through PostgreSQL’s source code and some hex dumps to find out what was going on inside a row.
Getting Started To get started, we’ll create a separate test database that we’ll cunningly calltest.
Dimitri Fontaine has been working on adding extension packaging functionality to PostgreSQL for a while. His hard work has paid off and PostgreSQL 9.1 will have extensions. How will extensions work? At a cursory glance, extensions are similar to libraries or DLLs in the programming world. Extensions are packaged units of code that can be installed using the CREATE EXTENSION command. This command will execute a script file that installs any of the extensions objects, including any C code that needs to be built or loaded.
In the previous article you learned how to create a single table. One table does not make for a very interesting database. Since we looked at how theactor table was created, let’s take a look at the most logical follow up: thefilm table.
The film table Without going into all of the details of the film table, we do know that the table looks something like this:CREATE TABLE public.film ( film_id int4 NOT NULL DEFAULT nextval('film_film_id_seq'::regclass), title varchar(255) NOT NULL, description text NULL, release_year year NULL, language_id int2 NOT NULL, original_language_id int2 NULL, rental_duration int2 NOT NULL DEFAULT 3, rental_rate numeric(4,2) NOT NULL DEFAULT 4.
In the previous article, we talked about creating your first PostgreSQL database and I went over the syntax of createdb and psql in a bit of detail. Next up, we’re going to look at creating the structure of the pagila database.``` createdb -U jeremiah pagila psql -U jeremiah -f pagila-schema.sql pagila psql -U jeremiah -f pagila-insert-data.sql pagila
### Working With SQL Even though SQL is the name of the language that we use to work with data, there are two subsets of SQL that you will hear people refer to: DML and DDL.
Once you’ve installed PostgreSQL (OS X Instructions) (other platforms), you’ll probably want to get started with a new database. Unfortunately PostgreSQL does not include a sample database by default. You can download the pagila sample database from the pgFoundry (direct download link). Pagila is a simple movie rental database, sort of like what you would get if you ran a video rental store. It is also a port of a sample MySQL database with changes made to take advantage of PostgreSQL’s features – you can read about the differences in the README file.
Want to know how I analyzed the Summit session data? I exported the data from the abstract selection system into a tab delimited file. Since I use a Mac at home, I used the tools available to me: PostgreSQL. I loaded the data using the PostgreSQL COPY command to bulk load the report output and then did some data mojo using PostgreSQL. Most of it was exactly the same as it would be for SQL Server with a few differences.