Introduction
This chapter provides an overview of how to use SQL to perform simple operations. This tutorial is only intended to give you an introduction and is in no way a complete tutorial on SQL.
In the following example we assume that you have created a database named mydb as described in the previous chapter and have been able to start psql
Examples can also be found in the PostgreSQL source distribution in the directory src/tutorial/.
To use those files, first change to that directory and run make:
cd .../src/tutorial
make
This creates the scripts and compiles the C files containing user-defined functions and types. Then to start to tutorial do the following:
psql -s mydb
mydb=> \i basics.sql
This \i command reads in commands from the specified file. psql's -s option puts you in single step mode which pauses before sending each statement to the server. This commands used in this section are in the file basics.sql.
Concepts
PostgreSQL is a relational database management system(RDBMS), that means it is a system for managing data stored in relations. Relation is essentially a mathematical term for table. The notion of storing data in tables is so common place today that it might seem inherently obvious but there are a number of other ways of organising database.
Each table is a named collection of rows. Each row of a given table has the same set of named columns and each colum is of a specific datatype. Where as columns have a fixed order in each row, it is important to remember that SQL does not guarntee the order of the rows within the table in any way.
Creating a New Table
you can create a table by specifying the table name along with all column names and their types.
Create table weather (
city varchar(80),
temp_lo int, -- low temperature
temp_hi int, -- high temperature
prcp real, -- precipitation
date date
);
you can enter this into psql with the line breaks. psql will recognise that the command is not terminated until the semicolon.
Populating a Table With Rows
The INSERT statement is used to populate a table with rows:
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
All the data types use rather obvious input formats. Constants that are not simple numeric values usually must be surrounded by single quotes, as in the example. The datatype is actually quite flexible in what it accepts, but for this tutorial we will stick to the unambiguous formate shown here.
The point type requires a coordinate pair as input as shown here
INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
This syntax used so far requires you to remember the order of the columns. As alternative syntax allows you to list the columns explicitly:
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
You can list the columns in a different order if you wish or even omit some columns, e.g., if the precipitation
is unknown:
INSERT INTO weather (date, city, temp_hi, temp_lo)
VALUES ('1994-11-29', 'Hayward', 54, 37);
Enter all the commands shown above so you have some data to work with in the following sections.
You can use COPY to load large amounts of data from flat-text files. This is usually faster because the COPY command is optimised for this application while allowing less flexibility that INSERT
COPY weather FROM '/home/user/weather.txt';
Where file name for the source file must be available on the machine running the backend process.
Top comments (1)
Nice