I’ve always been interested in Postgres – it never seemed to be quite as crazy as MySql, and since I’ve used Access (LoLWUT?), Ingres (party like it’s 1989), DB2 (meh), Oracle (and the difference between god and Larry Ellison is…), SQLite (awesome!), ESE (key-value FTW), SAPDB (why?), MySQL (how did this ever become popular?), different versions of SQL Server, and a few I don’t quite recall at various times I thought I’d give it a try. Here are few gotchas that tripped me up, considering my primarily SQL Server background.
#1 The user-name in the connection string for the otherwise quite awesome looking npgsql seems to need to be lower-case. I created the login via SQL as PascalCase. When it showed up in the pgAdmin III tool it was all lower-case, so maybe it is Postgres’ fault…not sure.
#2 Database functions execute by default with the rights of the Invoker. You can change this with a little bit of extra stuff in the create function statement, by switching to SECURITY DEFINER instead of SECURITY INVOKER (the default). In the MS SQL Server world stored procedures (the closest analogue to Postgres functions) run with the security rights of the creator.
#3 When calling pg_get_serial_sequence to get the name of the sequence that is defined for a serial column I had to double-quote the name of the table like this, which I found slightly odd:
select * from pg_get_serial_sequence('"<table name>"', 'Id')
Otherwise all pretty cool and straightforward.