How to define foreign key in POSTGRESQL

A foreign key is a column(s) in one table that references the primary key of another table, establishing a link between the two tables. 

Syntax

  constraint <title> Foreign key (<ColumnName>) References <SourceTableName>(<columnnameinsource>),
  

Example

CREATE TABLE Warriors (
  WarriorID uuid primary key,
  WarriorName VARCHAR(200),
  WarriorTitleID uuid not null,
  WarriorWingID uuid null,
  IsActive boolean,
  constraint Warrior_To_Title Foreign key (WarriorTitleID) References Titles(TitleID),
  constraint Warrior_To_Wing Foreign Key (WarriorWingID) References Wings(WingID)
);

How to define Primary Key in POSTGRESQL

 Primary Key is a key how data physically aligned in the memory system. It will give ability for fast retrieval of data.

Key aspects in primary key

1. When you define a primary key PostgreSQL automatically creates a unique B-tree index on the columns available in the table.
2. Use SERIAL or BIGSERIAL for auto-incrementing primary keys. These types automatically generate unique sequential values. refer (available data types in POSTGRESQL)
3. Each table can have only one primary key
4. but a primary key can consist of multiple columns (composite key).

Example for creating table

CREATE TABLE Wings
         ( 
             WingID uuid primary key, 
             WingName VARCHAR(100), 
             IsActive boolean 
        );

Alter the existing table:

ALTER TABLE Wings ADD COLUMN ColID SERIAL PRIMARY KEY;

How to Create Table in POSTGRESQL

Creating table in PostgreSQL is very much similar like MSSQL. Syntax is like below, 

 CREATE TABLE <TableName>

 (

    <column name1> <data type>,

    <column name2> <data type>,

    ...

);

Example: 

     CREATE TABLE Wings

         ( 

             WingID uuid primary key, 

             WingName VARCHAR(100), 

             IsActive boolean 

        );

PREGRESQL supported data types

 

Below table show the datatypes available in POSTGRESQL

Name	Aliases	Description
bigint	int8	signed eight-byte integer
bigserial	serial8	autoincrementing eight-byte integer
bit [ (n) ]		fixed-length bit string
bit varying [ (n) ]	varbit [ (n) ]	variable-length bit string
boolean	bool	logical Boolean (true/false)
box		rectangular box on a plane
bytea		binary data (“byte array”)
character [ (n) ]	char [ (n) ]	fixed-length character string
character varying [ (n) ]	varchar [ (n) ]	variable-length character string
cidr		IPv4 or IPv6 network address
circle		circle on a plane
date		calendar date (year, month, day)
double precision	float8	double precision floating-point number (8 bytes)
inet		IPv4 or IPv6 host address
integer	int, int4	signed four-byte integer
interval [ fields ] [ (p) ]		time span
json		textual JSON data
jsonb		binary JSON data, decomposed
line		infinite line on a plane
lseg		line segment on a plane
macaddr		MAC (Media Access Control) address
macaddr8		MAC (Media Access Control) address (EUI-64 format)
money		currency amount
numeric [ (p, s) ]	decimal [ (p, s) ]	exact numeric of selectable precision
path		geometric path on a plane
pg_lsn		PostgreSQL Log Sequence Number
pg_snapshot		user-level transaction ID snapshot
point		geometric point on a plane
polygon		closed geometric path on a plane
real	float4	single precision floating-point number (4 bytes)
smallint	int2	signed two-byte integer
smallserial	serial2	autoincrementing two-byte integer
serial	serial4	autoincrementing four-byte integer
text		variable-length character string
time [ (p) ] [ without time zone ]		time of day (no time zone)
time [ (p) ] with time zone	timetz	time of day, including time zone
timestamp [ (p) ] [ without time zone ]		date and time (no time zone)
timestamp [ (p) ] with time zone	timestamptz	date and time, including time zone
tsquery		text search query
tsvector		text search document
txid_snapshot		user-level transaction ID snapshot (deprecated; see pg_snapshot)
uuid		universally unique identifier
xml		XML data

How To Rename Existing Database in POSTGRESQL

using below query you can rename the database

ALTER DATABASE <FromDBName> RENAME TO <ToDBName>

Note: Apart from this connection need to close other open connection to the database to close the connection refer close existing connections

How to kill all open connections to POSTGRESQL using SQL command

 To close all existing connection to your database. We can pass the process/connection id to the predefined function pg_terminate_backend. Execute below query

SELECT 

    pg_terminate_backend(pid) 

FROM 

    pg_stat_activity 

WHERE 

    -- exclude current connection window

    pid <> pg_backend_pid()

    AND datname = '<database name>' 

Note: Database name is case sensitive

How to Create Data Base In POSTGRESQL Using pgAdmin

 In left pane select 

Select Server -> Databases right click -> select Create Database -> It will open create Database window.

In General tab provide the data base name and owner (user name) for the database

In Definition tab you can provide the template for the database

CREATE DATABASE actually works by copying an existing database. By default, it copies the standard system database named template1. Thus that database is the “template” from which new databases are made. If you add objects to template1, these objects will be copied into subsequently created user databases. This behavior allows site-local modifications to the standard set of objects in databases. For example, if you install the procedural language PL/Perl in template1, it will automatically be available in user databases without any extra action being taken when those databases are created.

However, CREATE DATABASE does not copy database-level GRANT permissions attached to the source database. The new database has default database-level permissions.

There is a second standard system database named template0. This database contains the same data as the initial contents of template1, that is, only the standard objects predefined by your version of PostgreSQL. template0 should never be changed after the database cluster has been initialized. By instructing CREATE DATABASE to copy template0 instead of template1, you can create a “pristine” user database (one where no user-defined objects exist and where the system objects have not been altered) that contains none of the site-local additions in template1. This is particularly handy when restoring a pg_dump dump: the dump script should be restored in a pristine database to ensure that one recreates the correct contents of the dumped database, without conflicting with objects that might have been added to template1 later on.

Another common reason for copying template0 instead of template1 is that new encoding and locale settings can be specified when copying template0, whereas a copy of template1 must use the same settings it does. This is because template1 might contain encoding-specific or locale-specific data, while template0 is known not to.

To create a database by copying template0, use:

SQL syntax

CREATE DATABASE dbname TEMPLATE template0;

In Security tab you can define what are the user will have access to the database and their access rights.

Save It will create the database in the server

How To create new login (user) in POSTGRESQL in pgAdmin

Once connecter to the server. In left pane 

Right click on User/Login Roles -> select create -> click on user/login role

It will Open window to create Group role. In General tab you can provide the name of the user and Definition  tab you provide the password, expiration date (user will be automatically deactivated after the specified date and time) and connection limit (concurrently how many connection can be done through the user). In Privileges tab you can define this user can login (if it is unchecked it will be considered as role) to the pgadmin and have any super user permission such as create database, create role and inherit rights from parent option (be caesious this will give all databases access which are already assigned to the creating user). In membership tab you can add this user to any group

         

 

How To Connect POSTGRESQL

After Installation of POSTGRESQL open pgAdmin application. It is like MSSQL server management studio tool. It used to connect datatbase and run queries you wanted.

 In left panel expand the server list and pick which server you want to connect.

Right click on server name -> click on connect server

Enter password -> click Ok it will connect you to the server.      

Difference Between NOLOCK and NOWAIT

NOWAIT will return error if the original table has (transaction) locked on it.
NOLOCK will read the data irrespective of the (transaction) lock on it.

In either case there can be incorrect data or unexpected result. There is no guarantee to get the appropriate data.

Here is the example of the how both provide different result on similar situation.

In this sample scenario we will create a table with a single row. We will open a transaction and delete a single row. We will now close the transaction and read the query with once with NOLOCK hint and once with NOWAIT hint. After the test we will rollback original transaction, which was deleting the record. As we will rollback transaction there will be no change in resultset however, we will get very different results in the case of NOLOCK and will get error in case of NOWAIT.

First Let us create a table:




Now let us open three different connections.

Run following command in the First Connection:




Run following command in the Second Connection:




Run following command in the Third Connection:




You can notice that result is as discussed earlier. There is no guarantee to get 100% correct result in either case. In the case of NOLOCK and will get error in case of NOWAIT. If you want to get the committed appropriate result, you should wait till the transaction lock on the original table is released and read the data.

SQL SERVER how to launch to Performance Monitor

There are three ways to launch  Performance Monitor.

1) Type “start perfmon” at the command prompt.

2) Go to Start | Programs | Administrative Tools | Performance Monitor.

3) Go to Start | Run | Perfmon.

Follow the images which explains how to use Perfmon and add different counters.

SQL SERVER Get Current Database Name

SELECT DB_NAME() AS DataBaseName

It will give the name of the database that currently running while running the query.

SQL PRIMARY KEY Constraint

 The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only ONE primary key.
SQL PRIMARY KEY Constraint on CREATE TABLE:
The following SQL creates a PRIMARY KEY on the "P_Id" column when the "Persons" table is created:
SQL Server / Oracle / MS Access:                                                                                   CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
MySQL / SQL Server / Oracle / MS Access:                                                                  CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)
MySQL:                                                                                                                              CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)
SQL PRIMARY KEY Constraint on ALTER TABLE
MySQL / SQL Server / Oracle / MS Access:                                                                      ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id)
To DROP a PRIMARY KEY Constraint
MySQL / SQL Server / Oracle / MS Access:                                                                            ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

SQL UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database table.
The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table. The following SQL creates a UNIQUE constraint on the "P_Id" column when the "Persons" table is created:
SQL Server / Oracle / MS Access:                                                                                  CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
MySQL / SQL Server / Oracle / MS Access:                                                                      CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)
MySQL                                                                                                                              CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)
SQL UNIQUE Constraint on ALTER TABLE
For single Col-    ALTER TABLE Persons ADD UNIQUE (P_Id)
For multi Col- ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
For Drop:
  MySQL- ALTER TABLE Persons DROP INDEX uc_PersonID
  SQL Server / Oracle / MS Access- ALTER TABLE Persons DROP CONSTRAINT uc_PersonID

SQL NOT NULL Constraint

The NOT NULL constraint enforces a column to NOT accept NULL values.
The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.
The following SQL enforces the "P_Id" column and the "LastName" column to not accept NULL values:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

The SQL Constraints

Constraints are used to limit the type of data that can go into a table.
Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).
We will focus on the following constraints:
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT

The ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
Syntax: 
To add-  ALTER TABLE <table_name> ADD <column_name>  datatype
To delete a col-  ALTER TABLE table_name DROP COLUMN column_name
To chng datatyp of col-  ALTER TABLE table_name ALTER COLUMN column_name datatype

The CREATE TABLE Statement

The CREATE TABLE statement is used to create a table in a database.
Syntax:
CREATE TABLE <table_name>
(
<column_name1> <data_type>,
<column_name2> <data_type>,
<column_name3> <data_type>,
....
)
Example:
 Now we want to create a table called "Persons" that contains five columns: P_Id, LastName, FirstName, Address, and City.
Query:
CREATE TABLE Persons
(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
Output:
The empty "Persons" table will now look like this:

P_Id    LastName    FirstName    Address    City      
                   

The CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a database.
Syntax:           CREATE DATABASE <database_name>
Example:         CREATE DATABASE my_db

Parts ofSQL

SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).
The query and update commands form the DML part of SQL:
SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database
The DDL part of SQL permits database tables to be created or deleted. It also defines indexes (keys), specifies links between tables, and imposes constraints between tables. The most important DDL statements in SQL are:

CREATE DATABASE - creates a new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index

can i put Semicolon after SQL Statements?

Some database systems require a semicolon at the end of each SQL statement.
Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.
We are using MS Access and SQL Server 2000 and we do not have to put a semicolon after each SQL statement, but like MySQL database programs force you to use it.

What Can SQL do?

SQL can execute queries against a database
SQL can retrieve data from a database
SQL can insert records in a database
SQL can update records in a database
SQL can delete records from a database
SQL can create new databases
SQL can create new tables in a database
SQL can create stored procedures in a database
SQL can create views in a database
SQL can set permissions on tables, procedures, and views
NOTE:   SQL is not case sensitive

What is SQL?

SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL is an ANSI (American National Standards Institute) standard