PostgreSQL - DDL component

beefwittedvalentineData Management

Nov 29, 2012 (4 years and 11 months ago)

274 views

LABORATORY HANDOUT #4




Marian Ursu, 1999

version 1.1

1











AIM

To understand and learn the main data definition commands supported by PostgreSQL.


MOTIVATION

PostgreSQL is the language supported by Postgres. Postgres is the academic version of Ingres,
i.e. the version developed to be used in academia f
or teaching purposes. Ingres is more powerful
(i.e. efficient, reliable …), but Postgres is sufficient for education purposes. A great advantage
offered by Postgres is the fact that its implementation details are available for free (!!!) so that
students a
nd researchers can amend it to implement their ideas. The main idea is that Postgres is
suitable to illustrate the concepts taught in the lectures and to give you sufficient skills for your
future jobs.


Ingres and Oracle are amongst the main industrial DB
MSs based on the relational model. They
both base their data definition and manipulation language on the standard SQL92. Each particular
system implements a superset of a subset of SQL92. However, all implementation have in
common the majority of the SQL92

features.


Postgres provides a series of extensions to the standard SQL92, such as extending SQL types,
operators and functions, an OO component, etc. Some of them will be exemplified during the
following labs. The others are left for you to explore, shou
ld you be interested (hopefully, you will).
A set of on
-
line manuals are provided for reference at

http://homepages.gold.ac.uk/marian
-
ursu/hypermanual/pg95user.html


So far, you have experienced in brief the main features of PostgreSQL (as one of the mult
iple
implementations of the standard SQL92). It is the time now to look and explore them into more
depth.


MAIN OBJECTIVES

1.

Create (define) a table



(domains
-

not supported) data types



candidate keys and primary key



(foreign keys
-

not supported)



constrain
ts

2.

Alter a table

3.

Drop a table

4.

(Backup a database)


REMARKS

1.

Remember the hints given in the previous set of handouts

2.

Most of the information presented here is necessary for the completion of Coursework #2.


HINT

Read first the whole content of these handout
s (even if only superficially), and only afterwards
start the work.


PostgreSQL

Data Definition Component



LABORATORY HANDOUT #4




Marian Ursu, 1999

version 1.1

2





CREATE TABLE


Description

CREATE TABLE <table
-
name> (


@<column
-
definition
-
clause>


[@<table
-
constraint
-
clause>]


);

For further details refer to “CREATE TABLE” in the PostgreSQL Use
r’s Guide.


Remember that what is typed in uppercase represents a keyword and has to be used as
described, what is specified in lowercase within “<“ and “>“ are user defined words and must be
substituted with the appropriate names (as chosen by the user),
“[“ and “]” delimit an optional item
and “@” in front of a language element (such as a user defined word) represents a list of those
language elements separated by commas (e.g. “@<column
-
definition
-
clause>” represents
“<column
-
definition
-
clause>, <column
-
d
efinition
-
clause>, …<column
-
definition
-
clause>”).


The syntax of the CREATE TABLE statement is actually richer, for it also includes an INHERITS
clause. However, since this is not a relational feature it is not going to be presented herein.



THE COLUMN D
EFINITION CLAUSE


The column definition clause has the following format

<column
-
name> <type>

[ DEFAULT <value>





| <column
-
constraint
-
clause> ]

Apart from specifying the <column
-
name> and the associated <type>, a column definition clause
can also consi
sts,
optionally
, of a default value specification, a constraint clause or a primary key
specification. The constraint clause specifies a list of integrity constraints that must be satisfied by
new or updated entries for the INSERT or UPDATE operations to s
ucceed.


TYPES


Domains are not fully supported in SQL. However, a primitive version of domains is
achieved via basic type declaration. SQL supports seven primitive data types.


For further information refer to chapter 3, Data Types, of the PostgreSQL Us
er’s Guide.


The seven basic types are



numeric



monetary



character



date/time



boolean



geometric



network and host addresses

New types (user defined) may be added by means of the DEFINE TYPE command. This
aspect, for the time being, will be overlooked.


Main commands

LABORATORY HANDOUT #4




Marian Ursu, 1999

version 1.1

3

The m
ain numeric and character types have already been presented in the previous set of
handouts. A special kind of numeric data type is SERIAL use mainly as an identifier or
cross
-
reference. For instance for the table:

CREATE TABLE example (


--

attribute defi
nition


identifier

SERIAL,


--

others




);

whenever a row is inserted into the table, the value associated with the column “identifier”
will become the incremented value of the tuple previously inserted. See what happens
when you insert values in a relati
on that has an attribute of type
SERIAL
, but you do not
provide value for it. See what happens if you provide values? What if the automatic
increase of the
SERIAL

attribute reaches a value that was inputted by hand? Explore the
functions “
nextval
”, “
currva
l
” and “
setval
” (in PostgreSQL User’s Guide).

Note that a sequence is not implicitly dropped when the table is dropped. The DROP
SEQUENCE command must be explicitly used.


PostgreSQL offers a wide variety of date/time types. They are



abstime



date



datetime



interval



reltime



time



timespan



timestamp

Two will be presented into more detail. For the rest (in case you need them) refer to the
PostgreSQL User’s Guide.


Type

Earliest

Latest

Resolution

date

4713BC

32767AD

1 day

time

00:00:00.00

23:59:59.99

1 microse
c


A set of time/date
styles

is associated with these types
-

ISO, SQL, Postgres and German
-

a set of order conventions
-

European, NonEuropean and US
-

and also a set of
constants

-

current, epoch, infinity,
-
infinity, invalid, now, today, tomorrow, yes
terday. For further
details refer to the PostgreSQL User’s Guide.


The boolean type (not part of SQL92) is specified as


Sate

Output

Input

true

‘t’

TRUE, ‘t’, ‘true’, ‘y’, ‘yes’, ‘1’

false

‘f’

FALSE, ‘f’, ‘false’, ‘n’, ‘no’, ‘0’


m潳瑧牥tn䰠i異灯r瑳⁳潭
攠ef⁴ 攠e慳ic⁧ 潭整物c⁴ 灥sⰠI畣栠hs⁰ i湴ni湥ⰠI潸ⰠI潬y杯測n
circle, etc. For more details refer to PostgreSQL User’s Guide.


EXAMPLES


Example of a table definition using the most common data types:


CREATE TABLE example_data_types (


--

numeric

LABORATORY HANDOUT #4




Marian Ursu, 1999

version 1.1

4


e
xact_small


INT2,


exact_numeric

INT4,


exact_big


INT8,


real_small


FLOAT4,


real_big


FLOAT8,


identifier


SERIAL,


--

character


one_char


CHAR,


fixed_length

CHAR(10),


fixed_limit


VARCHAR(25),


no_limit


TEXT,


--

currency


money



MONEY,


--

time/d
ate


time_val


TIME,


date_val


DATE,


--

boolean


yes
-
no


BOOL,


--

etc.

);


A set of operators and a set of functions are associated with each data type.


OPERATORS


The operators available in PostgreSQL are presented in the Chapter 4 of the User’s Guide
.
Since the presentation format there is suitable for this handout, you are referred to those
tables. Print them out if you consider it necessary. However, since the manual is available
on
-
line, you only need to consult these tables when you want some info
rmation you are not
sure of (e.g. you do not remember what the truncation operator looks like or you do not
know whether the square root operator has a higher precedence with respect to the
multiplication operator or not, etc.). For the moment a brief read
ing suffices. You should be
aware of what operations can be performed and not necessarily of what the corresponding
operators are named; for this you can always look up the manual.


Note: The notions of precedence (which operator is evaluated first) and as
sociativity (what
is the order of evaluation for operators of the same precedence) are considered known
from last year, from the programming course. If you do not know what they mean, ask your
lab supervisor.


An operator that you probably may need but may

not be aware that it is called an operator
is the typecasting operator “
::
”. It is used to change the type of a value/variable into the
specified type. For instance, if you have a table definition as


CREATE TABLE test (

--

attributes definition

name


VA
RCHAR(25),

house_no

INT,

tel_no


INT8,

--

attribute definition

)


and you want to provide the name, house number and telephone number as a single string
then you can use the following expression (the || stands for append strings):


LABORATORY HANDOUT #4




Marian Ursu, 1999

version 1.1

5

CREATE ‘Name ’ || name
|| ‘ ‘ || ‘; House no. ‘ ||
house_no::varchar(10) || ‘; Tel. no. ‘ || tel_no::varchar(10) AS
details

FROM test;




FUNCTIONS


The functions available in PostgreSQL are presented in Chapter 5
-

User’s Guide. The
points made above about operators are valid f
or functions too.



THE DEFAULT CLAUSE


The default clause specifies a data value for a column, for the situations when the insert
statement does not specify any value for the respective column. The specified value can be



a constant



a niladic function



a us
er defined function or expression

A niladic function can either be a niladic user or a niladic datetime. The only niladic user
function supported is

CURRENT_USER :

getpgusername()

whereas the niladic datetime functions supported are

CURRENT_DATE :

date(‘n
ow’)

CURRENT_TIME

:


time(‘now’)

CURRENT_TIMESTAMP :

timestamp(‘now)

The meaning of each of these functions is explicit in their name.


Examples of use

CREATE TABLE example (


att1


INT


DEFAULT 100,


att2


VARCHAR(25)

DEFAULT ‘no name’,


att3


MONEY


D
EFAULT ‘£10.0’,


att4


DATE


DEFAULT CURRENT_DATE


);



THE COLUMN CONSTRAINT CLAUSE


The general format is

[CONSTRAINT <name>] { NOT NULL


|






UNIQUE |






PRIMARY KEY |





CHECK <constraint> }

Remember that at least one element from w
ithin the curly brackets must be chosen when
the clause is used.


The NOT NULL constraint specifies that the respective column cannot contain NULL
values. Usage:

CREATE TABLE example (


--

named constraint


att1


INT


CONSTRAINT const1 NOT NULL,


--

un
-
na
med constraint

LABORATORY HANDOUT #4




Marian Ursu, 1999

version 1.1

6


att2


CHAR(10)

NOT NULL

);


The UNIQUE constraint specifies that the respective column cannot allow duplicate values.
If the column allows null values then two or more nulls are not considered duplicates (this is
not in accordance with the
SQL92 standard). Actually, the UNIQUE constraint is the
mechanism by which a column can be “declared” as a candidate key.

Example of use

CREATE TABLE example (


id


INT,


name


VARCHAR(30) CONSTRAINT unique_name UNIQUE,


address

VARCHAR(100) UNIQUE

);

If

a composite candidate key is to be specified then a
table constraint

(rather than a
column
constraint
) may be used instead (described in a following section).


The PRIMARY KEY constraint is already known by you.

Note that only
one

PRIMARY KEY constraint

can be specified for a table.


The differences between a PRIMARY KEY and a UNIQUE constraint are as follows:



a primary key does not allow null values



only one primary key constraint is allowed for a table


The CHECK constraint specifies a condition (i.e.
an expression that evaluates to a boolean
value) that has to be complied with by any value in the table.

Example of use:

CREATE TABLE example (


name


VARCHAR(30) CONSTRAINT not_void CHECK ( name <> ‘’ ),


salary INT


CHECK ( salary > 12000 AND sa
lary < 100000 )

);



THE TABLE CONSTRAINT CLAUSE


The column definition clause has the following format

[CONSTRAINT <name>] {

UNIQUE <column>
commalist

|





PRIMARY KEY <column>
commalist

|





CHECK <constraint>


|





<foreign
-
key
-
statement> }

The table
constraint clause is very similar to the column constraint clause. The main difference
consists in the fact that (even though this is not strictly true, we shall conform to this convention) in
the table constraint clause more than one columns are accessibl
e; moreover, for the UNIQUE and
PRIMARY KEY clauses, the list of columns / attributes must be explicitly stated. For instance, a
simple candidate or primary key can be specified easier in a column constraint clause whereas a
composite candidate or primary
key is easier specified in a table constraint clause. The restriction
of having only one primary key constraint / definition per table remains. Along the same lines,
whereas the column constraint CHECK is to be specified on only the respective column, a ta
ble
constraint CHECK can be specified on more than one columns.





LABORATORY HANDOUT #4




Marian Ursu, 1999

version 1.1

7

Example of a table constraint clause

CREATE TABLE example (


id




INT,


name


VARCHAR (30),


address


VARCHAR (100),


salary


INT,


dept_id CHAR(5),


PRIMARY KEY (id),


UNIQUE


(name, address),


CONSTRAINT non_void_name_add CHECK ( name <> ‘’ AND address <> ‘’ ),


CONSTRAINT min_salary CHECK ( salary > 12000 ),


CONSTRAINT max_salary CHECK ( salary < 100000 ),


FOREIGN KEY (dept_id) REFERENCES department


O
N DELETE RESTRICT


ON UPDATE CASCADE

);

Obviously, the above statement requires the existence of another table, called “department”,
which should have as a primary key a field “dept_id” of type “CHAR(5)”. For example, a possible
definition for “depar
tment” could have been:


CREATE TABLE department (


dept_id


CHAR(5),


dept_name


VARCHAR (30),


address


VARCHAR (100),


budget


INT,


PRIMARY KEY (dept_id),


For further details refer to the CREATE TABLE command as described in the User Guide
.


Foreign keys can also be implemented by means of triggers. You do not need to look into this
right now. This is going to be studied in a following lab.



ALTER TABLE


This command changes the definition of a table. It has three versions. One to add a ne
w column
to an existing table, one to rename an existing column and one to rename an existing table.
Description:

ALTER TABLE <table
-
name> ADD [COLUMN] <column
-
name> <column
-
type> ;

ALTER TABLE <table
-
name> RENAME [COLUMN] <old
-
name> TO <new
-
name> ;

ALTER
TABLE <old
-
table
-
name> RENAME TO <new
-
table
-
name> ;

COLUMN is “noise” and can therefore be eliminated from the command.

The syntax is actually richer, for it also includes also an “*” parameter which refers to the
“children” (in the inheritance hierarchy)

tables. For details refer to the PostgreSQL User’s Manual.


Examples of use

ALTER TABLE example ADD new
-
column INT ;

ALTER TABLE example RENAME addr TO address ;

ALTER TABLE example RENAME TO employees ;



LABORATORY HANDOUT #4




Marian Ursu, 1999

version 1.1

8

DROP TABLE


An existing table is removed from the

database. Description:

DROP TABLE <table
-
name>
commalist

;

Be careful when using this command. Once removed the table cannot be restored. Note that by
means of the DELETE command the content of a table can be emptied but the table is not
destroyed.


Examp
le of use:

DROP TABLE example, employees ;





Experiment with the described commands until you get to fully understand them. Use your previous
knowledge to test the results (i.e. use INSERT and UPDATE statements to check constraint definitions,
SELECT st
atements to check, for instance, the insertion of default values, etc.). You might have questions
about different issues; for instance “can a value of a certain type be casted into another type?”, “can new
types be defined by the user?”, “can a column cons
traint clause be used in conjunction with an ALTER
TABLE statement?”, etc. Try to find the answer to your questions by using the (on
-
line) PostgreSQL User’s
Guide and by experimentation. If not successful, ask the lab supervisor. Don’t forget to use your l
ogbook!


Tasks