1.3. Creating a New Table

You can create a new table by specifying the table name, along with all column names and their types:

CREATE TABLE emp (
       empno           NUMBER(4) NOT NULL,
       ename           VARCHAR2(10),
       job             VARCHAR2(9),
       mgr             NUMBER(4),
       hiredate        DATE,
       sal             NUMBER(7,2),
       comm            NUMBER(7,2),
       deptno          NUMBER(2)
);

You can enter this into EnterpriseDB PSQL with line breaks. EnterpriseDB PSQL will recognize that the command is not terminated until the semicolon.

White space (i.e., spaces, tabs, and newlines) may be used freely in SQL commands. That means you can type the command aligned differently than the above, or even all on one line. Two dashes ("--") introduce comments. Whatever follows them is ignored up to the end of the line. SQL is case insensitive about key words and identifiers, except when identifiers are double-quoted to preserve the case (not done above).

VARCHAR2(10) specifies a data type that can store arbitrary character strings up to 10 characters in length. NUMBER(7,2) is a fixed point number with precision 7 and scale 2. NUMBER(4) is an integer number with precision 4 and scale 0.

EnterpriseDB supports the usual SQL data types INT, SMALLINT, NUMBER, REAL, DOUBLE PRECISION, CHAR, VARCHAR2, DATE and TIMESTAMP as well as various synonyms for these types.

The second example will store departments and their associated names and locations:

 
CREATE TABLE dept (
     deptno          NUMBER(2) NOT NULL,
     dname           VARCHAR2(14),
     loc             VARCHAR2(13)
);

Finally, it should be mentioned that if you don't need a table any longer or want to recreate it differently you can remove it using the following command:

DROP TABLE tablename;