Skip to main content

Create Table in PostgreSQL


In this tutorial, you will find out how to use the Create Table statement to create new tables in PostgreSQL.

Create Table is a keyword (watchword), which is notify the database system to create a new table. The unattainable name for the table or identifier follows the given statement in the table. At the beginning, the applicant issuing an empty database has the current database.
Then, in the brackets, the list comes up, evaluates each column of the table, and what kind of data it is. The syntax from the example below will be clear.
The PostgreSQL CREATE table description is used to create a new table in any database.

Syntax

To create a new table in PostgreSQL, you employ the Create Table statement. The following table


CREATE TABLE table_name(<field1 name, type, constraints >,<field2 name, type, constraints > )


For example : I need to create a table Employee which will contain the data of employees of an organization like Name, Salary, DOJ, Department, Position etc.

Syntax


CREATE TABLE Public."Employee"
(
    "EmpId" Serial NOT NULL,
    "FName" character varying(50) NOT NULL,
    "LName" character varying(50) NULL,
    "DOJ" Timestamp with zone NOT NULL",
    "Salary" numeric NOT NULL,
    "DepartMent" character varying(50) NULL,
    "Position"  character varying(50) NULL   
)


Check out the syntax of the Create Table Statement in more detail.
·         After the table is created, first specify the name of the new table. The temporary keyword is to create a temporary table.
·         After this, you list the column name, its data type and the column constraints. Multiple columns in a table can be separated by a comma (,). The column evaluates the rules for the constraints column e.g.,  NOT NULL.
·         After the column list, you define table-level constraints that determine the rules of the data in the table.
·         After that, you set an existing table from which the new table is inherited. This means that the new table contains all the columns in the current table and the columns are explained in the CREATE TABLE statement. This is a PostgreSQL extension for SQL.


Popular posts from this blog

Add day to ISODate in MongoDB

We can use $add operator to add days in ISODate in mongodb, $add is the Arithmetic Aggregation Operator which adds number and date in mongodb.
Syntax:

{ $add: [ <expression1>, <expression2>, ... ] }

Note:  If one of the argument is date $add operator treats to other arguments as milliseconds to add to the date.
Example: Suppose we have a Test collection as below.

{"Title" : "Add day to ISODate in MongoBD","CreatedDate" : ISODate("2016-07-07T08:00:00.000Z")}

Query to add 2 days in CreatedDate

db.Test.aggregate([      { $project: { Title: 1, AddedDate: { $add: [ "$CreatedDate", 2*24*60*60000 ] } } }    ])

Result:

{ "_id" : ObjectId("579a1567ac1b3f3732483de0"), "Title" : "Add day to ISODate in MongoBD", "AddedDate" : ISODate("2016-07-09T08:00:00.000Z") }

Note: As mentioned in above note we have to convert days in millisecond because $add operator treat to other arg…

Remove special character from string in MongoDB

Problem: Suppose wehave a collection and one field is type string contains some special character (like !@#$%) and we don’t want these special character.
Solution: We can easily remove the special character from field using script “replace(/[^a-zA-Z 0-9 ]/g, '')” in our query.  How can we remove special character from string using this script please see following example.
Example: Suppose we have a collection “EduSurvey “where we are collecting information from institutions.

{Name:"JB institute”, About:"This is good one collage for MBA", Information:"This $%%institute ##has good faculty etc$$"}
{Name:"MK institute”, About:"This is good one collage for MCA", Information:"This$$%# is the dummy text12"}
{Name:"MG institute”, About:"This is good one collage for B,Tech", Information:"This# institute@ has&* good infrastructure"}

Did you notice Information fields contains some special character so we…