Select Query in PostgreSQL

In this tutorial, you will learn how to query basic data using the original PostgreSQL SELECT selection statement.
SELECT keywords are most commonly used in PostgreSQL to fetch records from the table, the selection statement is very sophisticated statements to PostgreSQL regarding performance, so use the SELECT statements with accuracy. PostgreSQL selection details are deployed to obtain data from the database table, which is returning the data in the form of the results table. These result tables are called result-sets.

Syntax for SELECT statements:


SELECT column1, column2, columnN FROM table_name;


Here, column 1, column 2 is the fields of ​​a table whose value you want to receive. If you want to access all the fields in the result set then you can apply the following syntax –

SELECT * FROM table_name;

INSERT data into table in PostgreSQL

The PostgreSQL INSERT INTO statement assures you to include a new row in the table. A row or multiple rows can be inserted at the time of the query results.

Syntax to use of INSERT INTO keyword in PostgreSQL

The original sentence of the INSERT INTO statement is as follows:

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);


Here, column 1, column 2, columns are columns in the table in which you want to insert the data.
The names of the target column can be listed in any order. The value given by the price segment or the query is linked to the left or right from a clear or built-in column list.
If you are adding values ​​to all the columns in the table, you may not need to specify the column (s) in the SQL query. However, undo doubt that the order of values ​​is in the same table in the table. The SQL INSERT INTO syntax will be as follows-

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

Output explanation when Inserted records in the table -PostgreSQL

The following table explains the output message and their purpose-
S.No.
Output message and representation
1
INSERT oid 1
The message was returned when only one row was inserted. OID is the statistical OID of the inserted row.
2
INSERT 0 #
If several rows were entered, the message came back. # Contains the number of rows.

Illustration

Let us make the Employee table in myDB like this –
CREATE TABLE Employee(
   ID INT PRIMARY KEY     NOT NULL,
   EmpName                TEXT    NOT NULL,
   Age                    INT     NOT NULL,
   Address                CHAR(50),
   Salary                 REAL,
   DOJ                          DATE
);

In the following instance, there is a line in the Employee's table –
INSERT INTO Employee (ID, EmpName, Age, Address, Salary, DOJ) VALUES (1, 'DK', 25, 'Noida', 20000.00,'2019-06-15');

The following instance is to include a line; The salary column has been left here and therefore its default value will be –
INSERT INTO Employee (ID, EmpName, Age, Address, DOJ) VALUES (2, 'Anil', 25, 'Delhi', '2018-12-13');

Instead of specifying the following example values, use the DOJ column for the DEFAULT clause –
INSERT INTO Employee (ID, EmpName, Age, Address, Salary, DOJ) VALUES (3, 'Ria', 23, 'Gkp', 25000.00, DEFAULT );

The following illustrations include several lines using multi-line syntax –
INSERT INTO Employee (ID, EmpName, Age, Address, Salary, DOJ) VALUES ('Sia',22, ‘Noida’ 65000.00, '2018-12-13'), (5, 'Naina', 27, 'Delhi', 85000.00, '2018-06-15');

Schema in PostgreSQL

The 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 the schema:
CREATE TABLE <schema name>.<Table Name>(Column1 int null, …);
Illustration
Let's see an example of 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 the connection request.
In one database, one or many schemas are called, which have commutation tables. The 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 the 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; besides, the schema cannot be nested.

Drop Table in PostgreSQL

In this tutorial, you will understand how to use the DROP Table Statement to remove existing tables from the database in PostgreSQL.

PostgreSQL DROP table statement is used to define the table and remove all the data, index, rule, trigger and obstacles related to that table.
DROP table removes tables from the database. Only its owner can demolish a table. To clear the table without any losses, use DELETE or TRUNCATE.

The DROP table removes any indexes, rules, triggers, and obstacles that are present for the target table. However, due to viewing the foreign-key block of a table or any other table, CASCADE should be specified. (CASCADE will completely remove a dependent approach, but in case of foreign-key it will only remove foreign-key constraints, not completely other tables.) You should be cautious while using this command because once the table is removed, all the available information in the table will be permanently lost.

Parameters

IF EXISTS: If the table does not exist, do not enter an error. In this case, a notice is issued.

Name: The name of the table to drop (optional schema-worthy).

CASCADE: Automatically reject any items that are dependent on the table (such as views).

RESTRICT: If any object depends on it, then refuse to leave the table. This is the default.

PostgreSQL DROP TABLE syntax

To delete a current table from the database, you use the DROP table statement as shown below:


DROP TABLE [IF EXIST] table_name[CASCADE/RESTRICT];


To permanently delete the table from the database, you specify the name of the table after the DROP TABLE keyword.
If you delete a non-existent table, the PostgreSQL problem is an inaccuracy. To avoid this situation, you can use the IF EXISTS parameter after the DROP table clause.

If the table you want to delete is used in ideas, constraints or any other object, then CASCADE gives you a grant to automatically remove those dependent items with the table.

RESTRICT Refuse to drop the table if it has a dependent table (foreign key relation). By default, PostgreSQL uses RESTRICT

After removing several tables of DROP tables together, you can put a list of tables, each table is different from a comma.

Note that only the owner of the schema, superuser and table owner has sufficient authority to remove the table.

PostgreSQL DROP TABLE examples

 Suppose we have a table Table1 and we don’t have it need onwards, so we have to remove this table permanently from the database. Use the following syntax


DROP TABLE Table1;


 If Table1 has the dependent table then above query will return the error:

[Err] ERROR:  cannot drop table Table1 because other objects depend on it

DETAIL:  constraint Table2_Table1_id_fkey on table Table2 depends on table Table1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.


Use the following syntax if Table1 has a dependent table.


DROP TABLE Table1 CASCADE;


CASCADE removes the relations between tables.

If Table1 doesn’t exist then what will happen, absolutely will raise Error:
[Err] ERROR:  Table Table1 doesn’t exist.

To resolve this issue, please use the following syntax.


DROP TABLE IF EXISTS Table1 CASCADE;




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 notifies the database system to create a new table. The unattainable name for the table or identifier follows the given statement in the table. In 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.


PostgreSQL: Function and its uses

The function is the set of PostgreSQL statements that stored on the database server and can be invoked using the SQL interface. PostgreSQL ...