Crash course in PostgreSQL, part 2

Page 3 of 3

Exporting table data

Putting data into a PostgreSQL table is fun, and you can export it even more easily with the COPY and \copy commands. Again, COPY requires a terminating semi-colon and an output directory that the postgres user can write to, while \copy has no terminating semi-colon and must output to the logged-in psql user's directory. Here are examples of both:

testdb=# COPY comics TO '/etc/postgresql/9.0/main/freakout.txt';
testdb=# \copy comics TO '/home/carla/freakout1.txt'

You may use any of the COPY and \copy command options on exports, such as delimiters, just like when you copy data from a file into a table.

We could go on for ages on specific operations and commands, and that would be way fun. But even in online articles there are limits, and now you know enough to continue exploring different commands on your own such as updates, deletions, joins, how to handle nulls, finding specific records and fields, and so on. So now let's take a quick look at some key terminology.

Important terminology

Databases are chock-full of special terminology. Here are four terms you should know and understand, and in knowing, and understanding them, you'll be ahead of a lot of people who think they know databases.

Views are wonderful shortcuts for queries. A query is a request to see data, like our simple SELECT * from comics; example. Queries can grow large and complex and span multiple tables. Any query can be defined in a view, and then instead of typing a long query command simply type the view name.

Transactions are fundamental to all databases. A common criticism of MySQL is that it does not support transactions. It does, depending on which storage backend you're using, but that is a long story for another day. A transaction groups all of the steps for a particular operation into a single operation that returns success only if all the steps succeed. If a single step fails then the whole transaction fails, rather than leaving the transaction in an incomplete and erroneous state. The transaction is not reported as completed until all the steps are written to disk. For example, consider all of the steps involved in ordering a book from an online seller. You want your payment credited, book moved to the shipping queue, and an order confirmation. The seller wants their inventory updated and payment processed correctly. If any of these steps fail then a well-designed ordering system reports the error, and it must be corrected or done over.

Schema is a word you'll see a lot, and there are a lot of different ideas on what it means. Creating a formal schema for your PostgreSQL databases is optional, and it starts with the CREATE SCHEMA command. Schema is the metadata that describes all the elements of database structure: tables, operators, data types, functions, the relationships between tables, every piece of your database. There are tools for creating graphical schemas illustrating all the different relationships, which is very useful for understanding your database structure and debugging problems. In PostgreSQL schemas allow you to arbitrarily organize database objects and to give users access to arbitrary database objects, which is a nice simple and flexible way to control user access.

Normalization is one of those words that sounds important and something that needs to be done -- normalize your tables! And it is. Normalization covers a lot of ground; in a nutshell it is reducing redundancy. This makes your database more efficient, and easier to maintain. So it encompasses tasks such as designing your tables to avoid duplication, using whitespace consistently, using consistent terminology and spelling for better searches, and sanitizing user input -- which is beautifully illustrated in the XKCD comic, Exploits of a Mom, in which we meet Little Bobby Tables.

This article, "A crash course in PostgreSQL, part 2," was originally published at ITworld. For the latest IT news, analysis and how-tos, follow ITworld on Twitter and Facebook

| 1 2 3 Page 3
ITWorld DealPost: The best in tech deals and discounts.
Shop Tech Products at Amazon