Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities.
I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a
quick-reference to MySQL that did not begin with the assumption I couldn't tell a database from a linked list. Now, as I'm getting my feet wet with PostgreSQL, I wanted something similar. Here are some notes and links I have found helpful. Hopefully they will help you as well.
Notice that's a CAPITAL U -- unlike we use in MySql.
But what's the default user name?
No, it's not root. Or ... probably not. It's the name of the OS user under which PostgreSQL was originally configured to run. Usually that's "postgres".
If you attempt to log in using a username that has not been created, you get the error:
PostgreSQL authentication methods reveals that peer authentication is not the recommended method ... and it only works on the local machine anyway ... so I really don't know why it seems to be the default.
Changing the authentication method gets you to the pg_hba.conf file. Where is it? While logged into psql, type
PG Admin III seems to be a nice GUI. I don't see as many options as MySQL Workbench, but if you prefer a GUI to the command-line, it looks pretty good. The "SQL Pane" that shows up by default is for the DDL of the selected object, not a place for you to enter a query of your own. To get that window, click the desired database in the left-hand pane, then click the "SQL" icon in the menu bar. The other thing about it that's not as nice as MySQL Workbench or MS SQL Server Management Studio is that it displays only 1 result set when you execute multiple queries.
Creating a database
From the shell command prompt createdb -U UserName DBNAME
From the psql command prompt or a SQL query in pgAdmin III create database DBNAME;
Changing to a different database ... don't!
There is no USE DBName in PostgreSQL. One must disconnect and reconnect. In psql,
As I learn more, I'll consider adding more articles. I'm taking some notes on DDL items and am about to try my first PL/Scheme function to treat hierarchical relationships as a graph to traverse. In the meantime, feel free to visit updated notes at my own website's
intro to PostgreSQL.
Please add any comments below with examples or insights that you think will help.