Friday, September 25, 2020
    Home Database Technology PostgreSQL create database and tables

    PostgreSQL create database and tables

    The blog post explains how to create database and tables in postgresql

    How to create database and tables in PostgreSQL?

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

    [ [ 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 addition OWNER to another user by specifying the user name.

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

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

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

    - Advertisement -

    Connection LIMIT allows you set the number of concurrent connection 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:

    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:

    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 constrains includes: 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 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)

    • 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 column name “description”, data type is character with maximum length is 64 and its constraint is NOT NULL.
    • Then you define the table constraint name item_pk. It indicates that 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 assign 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 session. Normally the temporary table is used to store the immediate results from other query in stored procedures. Here is the syntax of creating temporary table:

    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 temporary table. Here the query to create temporary table tmp.
    id smallint NOT NULL,
    name character varying(25) NOT NULL,

    Create Inheriting Table:

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

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

    -- 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)

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

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

    First we created vehicle table, then we created 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 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 parents tables. If there is a same column name exists in parent tables their data types has to be match otherwise an error will be 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 a column name of parents table. Their data type has to be match ad the column definition will be merged into one. All constraints from the columns of parents table are inherited by the column of the inheriting table.
    • In the inheriting table, if it specify explicitly default value for inheriting column, the default value from parents tables is overridden. Otherwise, any parents table which specify default values for the column must all specify the same default, or an error will be occurred.

    Consider Reading to these articles:

    - Advertisement -
    - Advertisment -

    Most Popular

    ORA-01194: file 1 needs more recovery to be consistent

    The blog post explains how to restore and recover database using until cancel with error ORA-01194

    How to enable Archivelog

    This blog post explains how to enable archive log mode in Oracle database for newest versions.

    Shared vs Static Library Performance

    The article explains the benefits of a shared library and static library usage in application building for improving application performance.

    Migration methods of Oracle Database

    Blog post explains which tricks and methods are simple to perform migration of small and large Oracle database

    Recent Comments