Skip to main content

Posts

Drop a database in PostgreSQL

In this article we will learn how to drop a database in PostgreSQL, we will learn how to drop a database using SQL command and using pgAdmin4 as well. Be careful before dropping a database because once you dropped the database you will lose all the complete information stored in the database.
Using DROP DATABASE SQL Command in PostgreSQLUsing this command, we can drop the database. Once you execute this SQL command, you will lose all your data, database structure, tables, schemas, etc. So be careful before dropping any database, make sure that you are leaving the correct database, I suggest that you have to take the backup before leaving the database.

DROPDATABASEDatabase_Name

Using pgAdmin4 drop databaseRight click on database-> select Delete/Drop

Recent posts

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 CREATE DATABASE command we can create the database in PostgreSQL, if we are familiar with MS SQL Server then this command is very easy for us. If you are using pgAdmin4 then open a SQL Tool and write the following command.

CREATEDATABASE"DB_NAME" WITH OWNER=postgres ENCODING='UTF8' LC_COLLATE='English_United States.1252' LC_CTYPE='English_United States.1252' TABLESPACE=pg_default CONNECTIONLIMIT=-1;

If you want to create a database on 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 abov…

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 which 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 datatype 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 dataty…

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. 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. 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. Step 6: Choose the de…

Introduction of PostgreSQL

Introduction of PostgreSQL PostgreSQL is an object-relational database management system and 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 to 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 calle…

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@ResultTABLE ( IDINT, NAMEVARCHAR(50), [ADDRESS]VARCHAR(255) ) INSERTINTO@Result EXECusp_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.
SELECTDISTINCTo.name FROMsyscommentss INNERJOINsysobjectsoONs.id=o.id