Skip to main content

Schema in PostgreSQL

Name of the Schema table is accumulation. A schema can also have thoughts, indexes, sequences, data types, operators, and functions. Schema corresponds to the directories at the operating system level, apart from that the schema cannot be nested. PostgreSQL statement Creation schema creates a schema.
Syntax: Here is the original syntax of the CREATE schema:
CREATE SCHEMA name;

Syntax for schematic table

Here is the basic syntax for creating tables in schema:
CREATE TABLE <schema name>.<Table Name>(Column1 int null, …);
Illustration
Let's see an illustration for creating schemas. Link to database testing and create a schema schema1 that is as follows –
create schema schema1;
The message "Building schema" indicates that the schema has been created successfully.
Now, create a table in the above schema, which is as follows –
create table schema1.table1(
   ID   INTEGER  NOT NULL,
   NAME CHARACTER VARYING (50)  NOT NULL,
   DOJ  TIMESTAMP WITH TIME ZONE NOT NULL,
   ADDRESS  CHAR (500),
   SALARY   NUMERIC,
   PRIMARY KEY (ID)
);

Syntax, drop schema

To remove/delete the schema, if it is zero (all objects in it are dropped), apply the command –
DROP SCHEMA schema1;
To leave a schema with all included items, use the command –
DROP SCHEMA schema1 CASCADE;

Benefits of using Schema

This allows many users to access the database without interrupting each other.
This database organizes groups into logical groups so that they can be more systematic.
Third-party Praxis can be placed in various schemas so that they do not drag together with other items.
There are one or more named databases in the PostgreSQL database cluster. Users and appropriation groups are shared throughout the cluster, but no separate data is shared in the database. Client connections from the server can only use data in a single database, which is set in connection request.
In one database, one or many schemas are called, which have commutation tables. Schema includes data types, functions and other types of named objects, including operators. The name of the same object can be employed without conflict in various schemas; For illustration, both schema 1 and MySQL can have a table named Mytable. Various databases, schemas are not strictly different: The employer can access the objects in the database from which it is associated, if there is reciprocity to do so.
  • There are different reasons for using Schema:
  • To allow, many operators use the database without interruption with each other.
  • To make database groups more manageable to organize in logical groups.
  • Third party execution can be kept in different schemas so that they do not collide with other objects.
Schema corresponds to the index at the operating system level, in addition the schema cannot be nested.

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 characters from string in SQL server

I faced many times an issue to remove special characters from a string. Suppose you are working on searching concept and you have to remove the special characters from search string due to query performance, there are many solution are available but T-SQL is easily resolved this issue.
Following query may help you to resolve your issue.

DECLARE@strVARCHAR(400) DECLARE@expresVARCHAR(50)='%[~,@,#,$,%,&,*,(,),.,!]%' SET@str='(remove) ~special~ *characters. from string in sql!' WHILEPATINDEX(@expres,@str)> 0 BEGIN SET@str=Replace(REPLACE(@str,SUBSTRING(@str,PATINDEX(@expres,@str), 1 ),''),'-',' ') END SELECT@str