Postgres Succinctly®
CHAPTER 6
Basic SQL
Compared to the other chapters in this book, this will be a reasonably short one. Why?
Well it's very simple really. Postgres is 100 percent ANSI-95 and beyond SQL compliant.
What this means in practice is that everything you already know about your existing database of choice that is standard compatible SQL will still work in Postgres.
There are already many excellent SQL references available on the Internet, so it would be of no use for me to cover stuff that's already done better elsewhere.
Getting your SQL commands into Postgres is typically done in one of two ways. For one, you can use pgAdmin's SQL editor where you get syntax highlighting and full-screen editing. You can open this by selecting the database node in the object tree that you wish to work with, and then clicking on the SQL magnifying glass button on the main toolbar:
SQL editor button
This will open the following editor for you to enter your SQL queries:
SQL Editor
Or you can choose the Graphical Query Builder tab:
Graphical Query Builder
The other way is to execute the SQL Shell option from the Postgres group in your Windows Start menu:
Postgres in Start menu
When selecting this option you’ll be prompted to enter information such as server address, database name, user name, and password. You should end up with something like the following if you use the objects we created in the last chapter:
SQL Shell
The commands that the shell recognizes can be found at http://www.postgresql.org/docs/9.0/static/app-psql.html.
I won’t repeat them all here, as we'll be using the GUI, but if you need to script your interactions with Postgres, then the PSQL shell is the perfect way to do so. If you’re logged in and can't find a way out, \q will quit the shell.
Putting in the practice
Open a pgAdmin and click on the magnifying glass icon shown in Figure 48 to open the SQL editor.
I often find it's useful to expand the tree for the tables I'm working with, and then resize my Query window so it covers the right-hand pane as shown in the following figure:
Resizing the Query window
This allows me to see my table and column names and such while I'm typing so that I can see what I'm working with.
First we'll insert some data into our test table. Type the following into your SQL window, and then click the Run button or press F5 to execute it.
insert into mytable(name, email, webpage, bio) values( "Peter Shaw", "http://shawtyds.wordpress.com", "a Geek pure and simple....") |
If you've done this, then you'll be looking at an error message and wondering why a column with the same name as the first field’s data doesn't exist.
Remember what I was saying earlier about case sensitivity in object names? Well, if you look I made the classic mistake of using double quotes when entering strings.
Like much of the advice I've given so far, this is another sticking point that frequently catches new users of Postgres. While I agree the error message could be better, Postgres is actually doing what it's supposed to and trying to interpret the table or object name as it's spelled.
If you change all those double quotes to single, then you should see that the new row is now inserted into your table.
Repeat the command a few times with different data just so you have four or five more rows of data to play with.
You'll notice that when applying the insert command, we specifically told Postgres exactly what columns we wanted to insert by including a comma list in brackets after the table name, and we made sure that exactly the same number of data parameters were provided in the values portion.
This was done so that we would not attempt to insert data into the gid column, which if you remember, is now under the control of a Postgres sequence.
If you now type:
select * from mytable |
into the SQL editor and press F5, then you should see something like the following, only showing the data you inserted instead:
Output pane
Other simple SQL statements work as you would expect:
select * from mytable where gid = 2 |
Output pane
select * from mytable where gid in(2,3,4) |
Output pane
update mytable set name = 'Alan Person' where gid = 3 |
Output pane
Output pane
delete from mytable where gid = 4 |
Output pane
Output pane
One more thing before we move on: Because Postgres is so compatible, querying the INFORMATION_SCHEMA table the same way you do in many other database systems will yield the same results.
SELECT * FROM information_schema.columns |
When run on MS SQL using SQL Management Studio, and on Postgres using pgAdmin, this command returns the same columns of information:
Output pane
In the next chapter we'll cover some more interesting examples of SQL that are specific to Postgres.
- Seamless integration with PostgreSQL databases
- Real-time data visualization for actionable insights
- Customizable dashboards to meet your specific needs
- Leverage PostgreSQL's scalability and performance