Create Database in PostgreSQL

In this article, we will learn how to create a database in PostgreSQL, we are familiar with SQL programming, so we will use the SQL command to create a database in PostgreSQL and also we will learn how to create a database in PostgreSQL by using PgAdmin4.

Create a database using SQL command

Using the CREATE DATABASE command, we can create the database in PostgreSQL, if we are familiar with MS SQL Server, then this command is straightforward for us. If you are using pgAdmin4, then open a SQL Tool and write the following command.


CREATE DATABASE "DB_NAME"
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'English_United States.1252'
    LC_CTYPE = 'English_United States.1252'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;


If you want to create a database on a default setup, then use only CREATE DATABASE “DB_NAME” then all default setup will be applied on the database but if you want to customize like role, language, table storage size and connection limit then use the above command. In the above SQL command, we create the database with the custom property, so we need to know about this and be careful when you create your database.

OWNER

If you want the setup role for a particular database, then create a user and assign them using OWNER when creating the database.

ENCODING

This is actually character set support; in PostgreSQL, it allows you to store text in a variety of character sets, which is called encodings. For more information, you can follow this link encoding. https://www.postgresql.org/docs/8.3/multibyte.html#MULTIBYTE-CHARSET-SUPPORTED

TABLESPACE

This allows us to register a new cluster-wide tablespace. Mean if a superuser wants to define an alternative location on the system where the data files containing database objects (such as table and indexes) can reside, then he can use the TABLESPACE. For more information click here https://www.postgresql.org/docs/8.3/sql-createtablespace.html

CONNLIMT

You can define how many concurrent connections can be made to this database using CONNLIMIT. If you don’t want to define the limit the set -1 it means no limit.

Create a database using PgAdmin4

If you want to create a database using pgAdmin4, then follow the following steps.
Step 1: Open pgAdmin4 and connect the server after that right-click on databases àcreateàdatabase see the below pic.

Step 2: After click on the create database below window will open where you will set the name of the database and owner.


Step 3: In this step, you will set up your Encoding, Template, Tablespace, Collation, Character type, Connection limit as described above, you can leave blank for the default setup.

Step 4: Here, you will set up the security you can left blank for the default setup.


Step 5: Here, you will set the parameter for the cluster-wide tablespace if you are using otherwise can let for the default setup.


Now click on the save button and your database will create if you want to see the SQL script then click on SQL tab, it looks like below.

Introduction of datatypes in PostgreSQL

Before going to learn the next steps like create database, table, function and its other thing we have to know about the datatypes, so in this article we will learn about the datatypes. It will be beneficial for us when we are doing architecture design in PostgreSQL, it will give several benefits like consistency, Validation, Compactness, and performance. Consistency means we have to choose proper datatype for a column so that we can get the consistent and fastest result. Validation means, if we use proper datatype for a column we can easily validate data that is out of scope from the outside. Compactness means, we will use appropriate datatype as per required data, if data comes in single value then we have to choose data type accordingly. Proper data type according to data scope will increase the performance which is a very important factor in any database architecture design. PostgreSQL supports a wide set of the datatype, it also provides the facility to create user-defined datatype as per user requirement.
Let’s start a brief description of data types in PostgreSQL.
smallint: it is small range integer, can store 2bytes of data which range is -32768 to +32767.
integer: its storage size is 4bytes and range is -2147483648 to +2147483647
bigint: it is large range integer; the range is -9223372036854775808 to 9223372036854775807.
decimal: the range is up to 131072 digits before the decimal point; up to 16383 digits after the decimal point.
numeric: the range is up to 131072 digits before the decimal point; up to 16383 digits after the decimal point.
real: 4bytes storage size and range is 6 decimal digits precision.
double precision: 8-bytes storage size and range is 15 decimal digits precision.
smallserial: 2-byte storage size and range is 1 to 32767.
serial: 4 bytes storage size and the range is 1 to 2147483647.
bigserial: 8 bytes storage size and the range is 1 to 9223372036854775807.
money: 8 bytes storage size and the range is -92233720368547758.08 to +92233720368547758.07.
------Character type--------
character varying(n): Variable-length bit string, variable length with the limit.
character varchar(n): Variable-length bit string, variable length with the limit.
character(n): fixed-length character string.
char(n): fixed-length character string.
-------Binary Data Types---------
bytea: 1 or 4 bytes plus the actual binary string storage size
-------Date/Time data types---------
timestamp[(p)][without time zone]: 8 bytes storage size, it's contain both date and time(no time zone).
timestamp[(p)][with time zone]: 8 bytes storage size, it's contain both date and time(with  time zone).
date: 4 bytes storage size, its contains date only (not a time of the day)
time[(p)][without time zone]: 12 bytes storage size, it contains times of demurely without a time zone.
time[(p)][with time zone]: 12 bytes storage size, it contains times of demurely with the time zone.
interval [fields ] [(p)]: 12 bytes storage size it contains time intervals.
------Boolean Type------
boolean: 1-byte storage size, state of true or false.
-------- Geometric Type-------
geometric data types represent two-dimensional spatial objects
point: 16 bytes storage size, it contain the coordinates of the two-dimensional blocks. For example (x, y) x is latitude and y is longitude, x and y are floating points.
line: 32 bytes storage size, the line connects the two points, so this data types column contains the two points as like [(x1, y1), (x2, y2)].
lseg: 32 bytes storage size, the line connects the two points, so this data types column contains the two points as like [(x1, y1), (x2, y2)] online. Suppose (x1,y1) and (x2, y2) is line points and (x4, y4) and (x5,y5) belongs to the line, it means (x4, y4) and (x5, y5) is a segment of the line.
box: 32 bytes storage size, box means a geometrical draw of rectangular and square. boxes are represented by pairs of points that the opposite corner of the box value of type box is specified using the sentence like ((x1, y1), (x2, y2)).
Where (x1, y1) and (x2, y2) are two opposite corners of the box.
path: 16 bytes storage size and can be extended as 16+16n, path are represented by the list of connected. Fast can be open where the first and last points in the list are considered not committed or closed where the first and last points are considered connected.
[(X1, Y1), (X2, Y2),…….(Xn, Yn)]
polygon: 40 + 16n storage size, polygons are very similar to the closed path but are stored differently and have their own set of support routines
[(X1, Y1), (X2, Y2),…….(Xn, Yn)]
circle: 24 bytes storage size, Circle means, a point and a radius and used like as <(x1, y1), r>.
-----------------------Network Address data types----------------------
PostgreSQL provides the facility to store Ipv4, IPv6, and Mac addresses. It is better to use this type instead of plain Text type to store network addresses because this time offers input error checking any specialized operators and functions.
cidr: 7 bytes storage size, can store Ipv4 and Ipv6 networking data.
inet: 7 bytes storage size, can store Ipv4 and Ipv6 host networking data.
macaddr: 6 bytes storage size, can use to store MAC address

Installing PostgreSQL on Windows

Installing PostgreSQL on Windows
Follow the following step to install PostgreSQL on windows.
Step 1: Download the expected version suitable for you from the link https://www.enterprisedb.com/downloads/postgres-postgresql-downloads#windows
Step 2: Double click on the installer file, an installation wizard will appear and guide you through multiple steps where you can choose different options that you would like to have in PostgreSQL. The following illustrates each step and its options for installation. If you install a different version, you may get additional steps. 
PostgreSQL Install on Window Step 2
Step 3: In this step of the installation process would be to select the directories for installation location where PostgreSQL would be installed. By default, it is stored under the C:\\ProgramFile.
Step 4: Enter the password for the database super-user and service account.
PostgreSQL Install on Window Step 4
Step 5: Enter the port for PostgreSQL. Make sure that no other applications are using this port. Leave it as default if you are unsure.
PostgreSQL Install on Window Step 5
Step 6: Choose the default locale used by the database.
PostgreSQL Install on Window Step 6
Step 7: You’ve completed providing information for the PostgreSQL installer. Click the Next button to install PostgreSQL
PostgreSQL Install on Window Step 7
Step 8: The installation may take a few minutes to complete.
PostgreSQL Install on Window Step 7
After finish the installation please check and open the PostgreSQL server.
WindowàPostgreSQLàpgAdmin and click. It will ask you for the admin password. Just enter the password you’ve used in the installation step.
Connect-to-PostgreSQL-Server
Now the PostgreSQL installation process is done, you are ready to use PostgreSQL.

Introduction of PostgreSQL

Introduction of PostgreSQL
PostgreSQL introduction
PostgreSQL is an object-relational database management system and the most advanced open-source database system. PostgreSQL has active development of more than 15 years and a proven architecture which has earned it a strong reputation for reliability, data integrity, and accuracy. It was developed based in POSTGRES 4.2 at the Berkeley Computer Science Department, University of California. PostgreSQL supports almost all the platforms Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows.
In this article, we will cover the following topics:
1-   A Brief History of PostgreSQL
2-   PostgreSQL features
3-   What makes PostgreSQL stand out
4-   Who is using PostgreSQL

A Brief History of PostgreSQL
PostgreSQL, originally called Postgres, was created at UCB by a computer science professor named Michael Stonebraker. Stonebraker started Postgres in 1986 as a follow-up project to its predecessor, Ingres, now owned by Computer Associates.

1977-1985 − A project called INGRES was developed.
·         Proof-of-concept for relational databases
·         Established the company, Ingres, in 1980
·         Bought by Computer Associates in 1994

1986-1994 − POSTGRES
·         Development of the concepts in INGRES with a focus on object orientation and the query language - Quel
·         The code base of INGRES was not used as a basis for POSTGRES
·         Commercialized as Illustra (bought by Informix, acquired by IBM)

1994-1995 − Postgres95
·         Support for SQL was added in 1994
·         Released as Postgres95 in 1995
·         Re-released as PostgreSQL 6.0 in 1996
·         Establishment of the PostgreSQL Global Development Team

PostgreSQL features
PostgreSQL has many advanced features that other enterprise database management systems offer, such as:
·         User-defined types
·         Table inheritance
·         Sophisticated locking mechanism
·         Foreign key referential integrity
·         Views, rules, subquery
·         Nested transactions (savepoints)
·         Multi-version concurrency control (MVCC)
·         Asynchronous replication
The recent versions of PostgreSQL support the following features:
·         Native Microsoft Windows Server version
·         Tablespaces
·         Point-in-time recovery

What makes PostgreSQL stand out?
PostgreSQL is the first database management system that implements a multi-version concurrency control (MVCC) feature, even before Oracle. The MVCC feature is known as snapshot isolation in Oracle.
PostgreSQL is a general-purpose object-relational database management system. It allows you to add custom functions developed using different programming languages such as C/C++, Java, etc.
PostgreSQL is designed to be extensible. In PostgreSQL, you can define your own data types, index types, functional languages, etc. If you don’t like any part of the system, you can always develop a custom plugin to enhance it to meet your requirements, e.g., adding a new optimizer.
If you need any support, an active community is available to help. You can always find the answers from the PostgreSQL’s community for the issues that you may have when working with PostgreSQL. Many companies offer commercial support services in case you need one.

Who is using PostgreSQL
Many companies have built products and solutions using PostgreSQL. Some featured companies are Apple, Fujitsu, Red Hat, Cisco, Juniper Network, etc. Check out the PostgreSQL’s featured users section for the complete list of organizations that are using PostgreSQL.

Query to find stored procedures by nested stored procedure name

Problem: Suppose we have a stored procedure which has been used in several stored procedure, I mean stored procedure usp_proc1 is nested in many stored procedures like below

BEGIN
  DECLARE @Result TABLE
  (
    ID INT,
       NAME VARCHAR(50),
       [ADDRESS] VARCHAR(255)
  )
  INSERT INTO @Result
  EXEC usp_proc1 @Name='codefari'
END

So I want to find all those queries who containing usp_proc1
Solution: There is a lot of solutions, I'm giving some of them below.
If you want to get the only name of the stored procedures then use the following query. Using join query on system tables syscomments and sysobjects we can get the stored procedures name which containing the particular table, nested procs or any other string.

SELECT DISTINCT o.name
FROM syscomments s
INNER JOIN sysobjects o ON s.id=o.id
WHERE s.TEXT LIKE '%text for search%'

If you want to get stored procedures and its type then run the following query.

SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%text for search%'

We can get the expected result using the following query also, in this query I used only sys.objects table to get the appropriate result. The following query will return sp_name, schema_name, type, and type_desc.

SELECT schema_name(o.schema_id) AS [schema_name],
  NAME AS sp_name
  ,type
  ,o.type_desc
FROM sys.objects o
WHERE o.is_ms_shipped = 0
  AND OBJECT_DEFINITION(object_id) Like '%text for search%'
  AND type = 'p' ORDER BY o.NAME

We can get the appropriate result using table sysdepends and sysobjects see the following query. It will return all those records of stored procedures and its dependent stored procedures. If you want to apply a filter then un-comment the AND condition.

SELECT o.name sp_name, dpt.name nested_spname
 FROM sysdepends d
INNER JOIN sysobjects o on d.id = o.id
INNER JOIN sysobjects dpt on d.depid = dpt.id
WHERE o.xtype = 'P' AND dpt.xtype = 'P'
--AND dpt.name like '%text for search%'

Using system procedure sp_depends we can get the all expected records, see the following query.

EXEC sp_depends @objname = N'proc name';

How to drop multiple tables with common prefix in one query?

Problem: Suppose we have a situation where we need to drop those tables that have some prefixes string, so it is possible to drop those tables with a common prefix in a single query.

Solution: yes it is possible to drop all those tables that have the common prefix in a single query. Using following query you can delete all those tables that begin with a certain prefix string. In where condition just put the common prefix string in where condition (Like ‘prefix%’)

DECLARE @query NVARCHAR(MAX) = N'';

SELECT @query += '
DROP TABLE '
    + QUOTENAME(s.name)
    + '.' + QUOTENAME(t.name) + ';'
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s
    ON t.[schema_id] = s.[schema_id]
    WHERE t.name LIKE 'MX_100%';

EXEC sp_executesql @query;

This query may create an issue, if a table has a foreign key relationship, you'll either need to drop them first or arrange the output to drop the tables in a certain order.
If you want to monitor exactly what goes on when the query is running then use the following query. 

DECLARE @query varchar(4000)
DECLARE csr CURSOR FOR
SELECT 'drop table [' + Table_Name + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name LIKE '<prefix>%'

OPEN csr
WHILE 1 = 1
BEGIN
    FETCH csr INTO @query
    IF @@fetch_status != 0 BREAK
    EXEC@query)
END
CLOSE csr;
DEALLOCATE csr


Note: If you are not a Database developer then don’t use above query contact to your database department.

PostgreSQL-Query: Sort result set by specific field values using ORDER BY Clause

Problem: Suppose we have a book_inventory table which has some columns such as id, isbn, title, author, publisher, publish_date, etc.. whe...