How to create database and tables in PostgreSQL?

To create a new database in PostgreSQL you must be a superuser or have CREATEDB privilege. Here is the syntax for creating a new database in PostgreSQL.

CREATE DATABASE name
[ [ WITH ] [ OWNER [=] database_owner ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ TABLESPACE [=] tables_pace ]
[ CONNECTION LIMIT [=] connection_limit ] ]

- Advertisement -

When creating a new database, by default, you are the owner of that database. You can also assign the owner of the database by using the addition OWNER to another user by specifying the user name.

By default, the newly created database clones the standard system database template. You can also create a new virgin database by assigning template0 to the TEMPLATE. In this case, PostgreSQL will create all standard predefined objects in the new database.

Encoding specifies the encoding of the database you want to create.

Table space allows you to define the specific location in the server where the data resides.

- Advertisement -

Connection LIMIT allows you to set the number of concurrent connections to connect the new database, -1 by default means unlimited connection.

For example, to create a database called store you can use the following query:

CREATE DATABASE store
WITH OWNER = postgres
ENCODING = 'WIN1252';

Once created, you can delete the database by using DROP DATABASE statement. Here is the syntax:
DROP DATABASE database_name
To remove the database store in the database server, you can apply the DROP DATATBASE statement above as below:

DROP DATABASE store
For convenience, PostgreSQL provides an alternative way to manage database via shell programs which are called createdb and dropdb. Both of programs can be launched from command line, then they connect to the PostgreSQL database server and execute Create Database and Drop Database statements.

To create a new table in PostgreSQL you use CREATE TABLE statement. The CREATE TABLE statement creates a new, initially empty table in the current database you are working with. The owner of the table will be the user issuing the CREATE TABLE statement.

To create a table you need to specify followings criteria:

• Table name
• Column list along with their data type, length of column and optionally column constraints. Column constraints include NULL/ NOT NULL, UNIQUE, PRIMARY KEY,…

• Table constraints. Table constraints including UNIQUE, PRIMARY KEY, FOREIGN KEY…
In PostgreSQL, when you create a new table, it also creates a data type which represents the composite type corresponding to one row of the table. Because of this reason, the table name has to be unique within its specific schema.

Let’s take a look at several examples of using CREATE TABLE statement.

Here is the query to create a table item:

CREATE TABLE item
(
item_id serial NOT NULL,
description character varying(64) NOT NULL,
cost_price numeric(7,2),
sell_price numeric(7,2),
CONSTRAINT item_pk PRIMARY KEY (item_id)
)
WITH (OIDS=FALSE);

• First, you specify the table name after the CREATE TABLE keyword.
• Next, you define the column list within the parentheses and separated them a by a comma. In each column definition, you define the column name, its data types and its constraints. For example, the description column has the column name “description”, the data type is the character with a maximum length is 64 and its constraint is NOT NULL.
• Then you define the table constraint name item_pk. It indicates that the item_id column is the primary key of the table.
• Finally, WITH (storage_parameters = values,…) specifies the optional storage parameters. OIDS is an abbreviation for Object IDentifierS. If you assign OIDS to true, you specify that the new row of the table should have object identifiers assigned to it.

Creating Temporary Table:

With the Create Table statement, you can also create a temporary table. Temporary tables are automatically dropped at the end of a session. Normally, the temporary table is used to store the immediate results from other queries in stored procedures. Here is the syntax of creating a temporary table:

CREATE {TEMPORARY | TEMP} TABLE table_name
(
column_name1 data_type(length) constraints,
column_name2 data_type(length) constraints,
...
)

You just specify the keyword TEMPORARY or TEMP before the table name to create a temporary table. Here is the query to create a temporary table tmp.
CREATE TEMP TABLE tmp
(
id smallint NOT NULL,
name character varying(25) NOT NULL,
CONSTRAINT id_pk PRIMARY KEY (id)
)
Create Inheriting Table:

One of the advantages of PostgreSQL in comparison with other RDMBS products is an inheriting table. That is the reason why PostgreSQL is called ORDMBS (Object Relational Database Management System). Yes, you can create a table which inherits from the other tables called parent tables. When we say table A inherits tables B and C, it means table A inherits all columns of Table B and C. Table A is called a child table or inheriting table and tables B and C are called parent tables.

In this example, we create a vehicle table as a parent table, and then we create a car table which inherits from the vehicle table. Let’s take a look in more detail to see how it works.
CREATE TABLE vehicle
(
"name" character varying(25) NOT NULL,
id serial NOT NULL,
CONSTRAINT vehicle_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE TABLE car
(
-- Inherited: "name" character varying(25) NOT NULL,
model character varying(25) NOT NULL
-- Inherited: id integer NOT NULL DEFAULT
-- nextval('vehicle_id_seq'::regclass)
)
INHERITS (vehicle)
WITH (OIDS=FALSE);

INSERT INTO car(name,model)
VALUES ('Car','Lexus');

INSERT INTO car(name,model)
VALUES ('Car','Ferrari');

First, we created a vehicle table, then we created a car table with INHERITS addition, and finally, we insert data into the car table. When we inserted data into the car table, the data is automatically inserted to the vehicle table as well. The car table has a model column and inherits two columns from the parent table vehicle.
There are some restrictions while using inheriting table as follows:

• Column name has to be unique in the parent’s tables. If there is same column name exists in parent tables their data types have to be matched otherwise an error will have occurred. If there is no conflict, the columns are merged to form a single column in the inheriting table.
• If the inheriting column has the same name as the column name of the parent’s table. Their data type has to be matched ad the column definition will be merged into one. All constraints from the columns of the parents’ table are inherited by the column of the inheriting table.
• In the inheriting table, if it specifies explicitly the default value for the inheriting column, the default value from the parent tables is overridden. Otherwise, any parent table which specifies default values for the column must all specify the same default, or an error will have occurred.

When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert.

This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.

- Advertisement -