The Author – Bart Busschots
Creative Commons
Unless otherwise stated, the content on this site is licensed under Creative Commons Attribution, Noncommercial, No Derivative Works 3.0 License
Dec
19
Beating PostgerSQL 8 Into Submission on OS X 10.5 Leopard
Filed Under Computers & Tech on December 19, 2007 | Leave a Comment
In general the move to Leopard has been very smooth for me but there was one notable exception, getting a working PostgreSQL 8 sever up and running on my MacBookPro. A few weeks ago I’d expended a lot of time and effort on this and gotten nowhere so at that stage I’d decided to work around the problem by using a remote PostgreSQL server rather than running one locally. This works fine as long as you have broadband internet access. However, I’m off to Belgium for a week on Saturday where I’ll have no broadband but where I will need to get some development work done that requires access to a PostgerSQL server.
With the end of the week getting closer I had another go at getting PosgreSQL to behave on OS X and I did eventually succeed. I got 90% of the way there by following these instructions but then I ran into a few strange problem that took a little more time and effort to work around. However, the good news is that I got it all figured out in the end!
[tags]PostgreSQL, OS X, OS X 10.5 Leopard[/tags]
Dec
9
Accessing Auto Increment Values from PostgreSQL with Java
Filed Under Software Development, Computers & Tech on December 9, 2007 | 7 Comments
It’s not uncommon that as part of a complex transaction you need to insert a row into a table with an auto-incrementing primary key and then use the key from the row you just generated as a foreign key in another insert within the same transaction. Java provides a mechanism to return the auto-generated keys from an insert query without the need to execute a second query by means of the function java.sql.Statement.getGeneratedKeys(). Unfortunately the PostgreSQL JDBC connector does not appear to support this feature (at least the version I am using does not). Given the fact that we do not have this functionality available to us when using a PostgreSQL database we need to use a separate query to retreive the value of the key we just generated. It goes without saying that we need to do this two-step process as part of a transaction but it may not go without saying that the correct way to extract the value for the key is by querying the sequence that the key is being generated from and not by querying the table directly. It is true that most of the time the highest value of the incrementing variable will be the value just inserted but that is not a guarantee.
Jun
8
PostgreSQL on the Mac
Filed Under System Administration, Computers & Tech on June 8, 2006 | Leave a Comment
Having recently discussed running MySQL on the Mac I’d now like to tackle PostgreSQL. I’m sorry to say things are not quite as rosy just yet. PostgreSQL don’t provide us with a binary distribution for the Mac nor do they provide a nice Panel for the System Preferences App. There are also less choices when it comes to GUIs for manipulating and designing your database but there are still options and things are still a lot better than they could be. Read more
Oct
6
A DISTINCT quirk in PostgreSQL
Filed Under 42 (Life the Universe & Everything) on October 6, 2005 | Leave a Comment
I was doing some NASTY SQL coding this evening (or squealing as Smiler would say) and I got bogged down quite a while trying to get DISTINCT and ORDER BY working in the same query. The PostgreSQL error message was not entirely clear and I must have misinterpreted it about 5 times before I finally figured out what it was so desperately trying to tell me!
Turns out that even if you don’t actually want to select the field you are sorting on in a query that uses the DISTINCT keyword you still HAVE to include it in your SELECT list or PSQL will have kittens!
BTW, I am aware that it is considered a ‘bad smell’ by some hard-core SQL programmers to use to DISTINCT key word and I generally agree with their logic (why go to the effort of selecting a load of extra rows that you are just gonna prune off in the end?). However, I can’t for the life of me figure out how to get rid of the DISTINCT in the query below, any insights you may have would be appreciated (looks at Bon)!
SELECT DISTINCT a.id AS assignmentId, a.name AS assignmentName, a.team_id AS teamId,
t.name AS teamName, a.completed AS assignmentComp, a.last_updated_at AS lastUpdated
FROM ((student_team st INNER JOIN teams t ON st.team_id = t.id)
INNER JOIN assignments a ON t.id = a.team_id)
INNER JOIN projects p ON t.project_id=p.id
WHERE a.completed='true'
ORDER BY a.last_updated_at DESC LIMIT 5;
Aug
12
Switching from MySQL to PostgreSQL
Filed Under 42 (Life the Universe & Everything) on August 12, 2005 | 4 Comments
I was recently forced to switch from MySQL to PostgreSQL for a project I am working on and at first I was very reluctant to change because I had been using MySQL for years, was very familiar with it, and knew my way round the developers section of the MySQL website. However, having worked with PSQL I would now never go back to MySQL, it is simply in a lower league than PSQL!
When using a DB the differences are not all that big but when creating and admining DBs there are some differences that will throw you at first so I’ll list the ones I came across and how to get by them.
User Management
MySQL has a bizarre model of user management, the user bart from say localhost is considered a completely separate user to the user bart from any host or the user bart from a particular host. This makes managing users interesting and TBH I have always hated this aspect of MySQL. PSQL takes a simpler view, it uses the system accounts as its accounts. The user bart is the user bart no matter where he is logging in from and his password is his system password. Also, there is no root account as such on PSQL, if you access PSQL as the user who owns PSQL then you have ‘root’ powers, if you don’t you only have the powers that have been given to you by the DBA with GRANT statements.
Control of access to different databases by different users from different places is all controlled from a text file called pg_hba.conf in which you specify how PSQL should authenticate different users trying to access different DBs from different places. You have a number of choices ranging from ‘trust’ which lets any system user access the DB as any user without a password to ‘password’ which requires the user to supply a password and ‘ident’ which only allows the user to log into the DB as the user they are logged into the system as. pg_hba.conf is well commented with lots of examples so it is really easy to manage client authentication on PSQL and makes MySQL look over complicated and under-powered.
AUTO_INCREMENT
There is no AUTO_INCREMENT keyword in PSQL. This scared the crap out of me at first because I thought I had a real problem, but of course I didn’t, PSQL has a special data type for fields that need to auto increment called SERIAL. This creates an implicit sequence which adds a slight complication in that you need to grant users access to BOTH the table AND the sequence if you want them to be able to add rows to the table. The sequence always has a name with the format: <table>_<field>_seq.
ENUM
Again PSQL does not have an ENUM type which, at first, seems like a problem but you can create fields that are effectively enumerations using the CHECK keyword (and you can do MUCH MUCH more with it too!). To set up an enumeration called user_level with the possible values ‘STUDENT’, ‘MENTOR’ and ‘ADMIN’ you would use the following syntax:
user_level VARCHAR(10) DEFAULT ‘STUDENT’ NOT NULL CHECK (user_level IN (‘STUDENT’, ‘MENTOR’, ‘ADMIN’))
Transactions
PSQL has excellent transaction support and it is stable and works with things like JDBC which is more than can be said for MySQL ATM. And, to make it even better, the syntax is immensely simple!
BEGIN;
-- do work
COMMIT;
Shell
The psql shell to access PSQL is very very different to the mysql shell you access MySQL through. Basically you are just gonna have to RTFM it! Also, when you get stuck (and you will) just remember that \h gives you help and \q exits! The thing I missed most were MySQLs simple keywords SHOW and DESCRIBE, PSQL has the same functionality but just with much less memorable commands, e.g. \dt lists all the tables in the current DB.
Overall I find that PostgreSQL is a more powerful and more mature and robust piece of software than MySQL and since it is also open source I can see no reason to choose MySQL over PostgreSQL. Yes, if you have been using MySQL for years you will have a bit of a learning curve but trust me, it is well worth the bit of time and effort to do that learning!