PostgreSQL introduction and how to use the PostgreSQL database in the development framework

PostgreSQL introduction and how to use the PostgreSQL database in the development framework

Recently, I have prepared some knowledge about PostgreSQL database development. This article will record and share the summary of the PPT content through a blog. The main content of this article is to introduce the basic information of PostgreSQL database, and how to use the PostgreSQL database in our development framework, I hope you will give more comments.

1. PostgreSQL database Introduction

PostgreSQL is developed by the Department of Computer Science at UC Berkeley and is now renamed PostgreSQL. It has many good features:

Open Source: PostgreSQL is a free object-relational database server (Database Management System), which is issued under a flexible BSD-style license.

Good compatibility: PostgreSQL features cover SQL-2/SQL-92 and SQL-3/SQL-99.

High reliability: PostgreSQL advertised itself as the world's most advanced open-source database. Reliability is the highest priority of PostgreSQL. It is famous for its rock-solid quality and excellent engineering, and supports highly transactional and mission-critical applications.

Widely used: Since MySQL was acquired by Oracle, PostgreSQL has gradually become the first choice for open-source relational databases, and is an open-source database that fully surpasses MySQL in terms of functions.

Good support: PostgreSQL also supports a wide range of interfaces. It supports almost all types of database clients, and supports the most abundant data types, some data types are not available in commercial databases, such as IP type and geometric type;

2. Install database and management tools

Database Installation
PostgreSQL (https://www.postgresql.org/download/windows)

Database Management Tools
Navicat for PostgreSQL (such as V11.1)

Database Modeling Tools
PowerDesigner (for example, V16.5)

 

Database Operations:

1) install the PostgreSQL database
2) install the Navicat for PostgreSQL database management tool

3) install the PowerDesigner modeling tool

4) create a simple table in PowerDesigner, obtain the table SQL, execute the script in Navicat to create the table, insert data, and so on.

3. PostgreSQL database syntax

The SQL statement is processed normally, which is the same as other databases, such as creating or deleting tables. Below we will extract some common operations on PostgreSQL from the network.

1) table definition:

1. Create a table:

CREATE TABLE products (
product_no integer,
name text,
price numeric
);

2. delete a table:

DROP TABLE products;

3. Create a table with default values:

CREATE TABLE products (
product_no integer,
name text,
price numeric DEFAULT 9.99 --DEFAULT is the keyword, and the subsequent value 9.99 is the default value of the field price.
);
CREATE TABLE products (
Product_no SERIAL, --SERIAL type field indicates that the field is an auto-increment field, which is completely equivalent to Sequence in Oracle.
name text,
price numeric DEFAULT 9.99
);

4. constraints:
Check constraints are the most common constraints in a table. They allow you to declare that the values in a field must meet a Boolean expression. In addition, we can also declare table-Level Check constraints.

 
CREATE TABLE products (
product_no integer,
name text,
The value of the --price field must be greater than 0, otherwise inserting or modifying the field value will cause a violation error. It should also be noted that the check constraint
-Is an anonymous constraint, that is, the constraint is not explicitly named in the table definition, so that PostgreSQL will be based on the current table name, field name and constraint type
--Automatically name the constraint, such as: products_price_check.
price numeric CHECK (price> 0)
);
CREATE TABLE products (
product_no integer,
name text,
-The check constraint for this field is explicitly named positive_price. The advantage of this is that when the constraint is maintained in the future, it can be operated directly under that name.
price numeric CONSTRAINT positive_price CHECK (price> 0)
);

The following constraints are non-empty constraints, that is, the constraint fields cannot be inserted with null values, or existing data is updated to null values.

CREATE TABLE products (
product_no integer NOT NULL,
name text NOT NULL,
price numeric
);

5. Primary Key and foreign key:
Technically speaking, primary key constraints are only a combination of unique constraints and non-empty constraints.

CREATE TABLE products (
product_no integer PRIMARY KEY, --The field product_no is defined as the unique primary key for the table.
name text,
price numeric
);

Like the uniqueness constraint, a primary key can act on multiple fields at the same time to form a joint primary key:

CREATE TABLE example (
a integer,
b integer,
c integer,
PRIMARY KEY (b, c)
);

The value of a field (or a set of fields) declared by the foreign key constraint must match the value of some rows in another table. We define this behavior as the reference integrity between two related tables.

CREATE TABLE orders (
order_id integer PRIMARY KEY, --The table can also have its own primary key.
-The product_no field of this table is the foreign key to the primary key (product_no) of the products table above.
product_no integer REFERENCES products (product_no),
quantity integer
);

If you want to delete a row record in the referenced table (primary key table, because the primary key field value of the row record may be associated with a record in the referenced table (foreign key table), the deletion operation will fail. To complete this operation, you can delete the rows associated with the record in the referenced table before deleting the Row Records in the referenced table. However, it should be noted that PostgreSQL provides us with a more convenient way to complete such operations.

CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);

CREATE TABLE orders (
order_id integer PRIMARY KEY,
shipping_address text
);
  CREATE TABLE order_items (
         product_no integer REFERENCES products ON DELETE RESTRICT,-limit option
         order_id integer REFERENCES orders ON DELETE CASCADE,-
         quantity integer,
         PRIMARY KEY (product_no, order_id)
     );

Restriction and cascading deletion are two of the most common options. RESTRICT prohibits the deletion of referenced rows. No action indicates that an error is thrown if any reference row exists while checking the constraints. If you do not declare anything, it is the default ACTION. (The actual difference between the two options is that no action allows the constraints to be checked to be postponed until the transaction is later, but RESTRICT does not work .) CASCADE declares that when a referenced row is deleted, the row that references it will also be deleted automatically. The action on the foreign key field has two options: set null and set default. In this way, when the referenced rows are deleted, the fields that reference them are set to null or the default values. Note that these options do not allow you to escape observation and constraints. For example, if an action declares set default, but the DEFAULT value does not meet the foreign key, the action fails. Similar to on delete and the on update option, it is called when the referenced field is modified (updated. The available actions are the same.

2) table modification:
1. Add a field:

ALTER TABLE products ADD COLUMN description text;

The new field will first fill in the given DEFAULT value for existing rows in the table (if you do not declare the DEFAULT clause, the DEFAULT value is null ).

When adding a field, you can specify constraints for the field.

ALTER TABLE products ADD COLUMN description text CHECK(description <> '');

2. delete a field:

ALTER TABLE products DROP COLUMN description;

If the table is a referenced table and the field is a referenced field, the above deletion operation will fail. If you want to cascade the deletion of all referenced fields while deleting the referenced fields, you can use the following syntax.

ALTER TABLE products DROP COLUMN description CASCADE;

3. Add constraints:

ALTER TABLE products ADD CHECK (name <> ''); --Add a table-level constraint
ALTER TABLE products ADD CONSTRAINT same_name UNIQUE (product_no);-Increase the unique constraint of the name.
ALTER TABLE products ADD FOREIGN KEY (pdt_grp_id) REFERENCES pdt_grps;-Increase foreign key constraints.
ALTER TABLE products ALTER COLUMN product_no SET NOT NULL; --Add a non-null constraint.

4. Delete constraints:

ALTER TABLE products DROP CONSTRAINT some_name;

For display naming constraints, you can directly delete them based on their names. For implicit naming constraints, you can use psql's \ d tablename to obtain the name of the constraint. Like deleting a field, if you want to delete a constraint with a dependency, you need to use CASCADE. In one example, a foreign key constraint depends on the unique constraint or primary key constraint on the referenced field.

3) permissions:
Only the table owner can modify or delete the table. To GRANT a permission, we use the GRANT command to REVOKE the permission and use the REVOKE command.
PUBLIC is a special "user" that can be used to grant permissions to every user in the system. Write ALL at the declared permission location to grant ALL permissions related to the object type.

GRANT UPDATE ON table_name TO user;-Grant the table's update authority to the specified user.
GRANT SELECT ON table_name TO GROUP group;-Give the select permission of the table to the specified group.
REVOKE ALL ON table_name FROM PUBLIC; --Revoke all permissions on the table from Public. 

Initially, only the object owner (or Super User) can grant or revoke the object's permissions. However, we can assign a "with grant option" permission to the person who accepts the permission to grant the permission to others. If the granted option is subsequently revoked, all users who have received the permission from the recipient (directly or through the level-connected authorization) will lose this permission.

4. Database syntax differences

1) Special Field ing (partial)

2) Paging
MySQL paging common syntax

select * from persons  limit 0,10;

The start point is 0 and 10 records are queried.

Compatible with MySQL and postgreSQL

select * from persons limit 10 offset 0

Query starts from 0 and returns 10 records.

 

3) Other types
Guid generation(SQLServer is the newid () function)

PostgreSQL starts from version 9.1 and executes the following functions that can create guids:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";select uuid_generate_v4()

 

 

SQL qualifier
Sqlserver is [], for example, Select [Name] from [User]
PostgreSQL is "", for example, Select "Name" from User

Auto-increment ID
SQLserver is identity (1, 1)
Use SERIAL instead of PostgreSQL
The SERIAL object is of the int4 type (default). The bigserial type corresponds to the int8 type.
A sequence created by the serial type is automatically deleted when its field is deleted.

Function
String | string
'Post' | 'gresql'
Lower (string)
Lower ('Tom ')
Upper (string)
Upper ('Tom ')
Substring (string [from int] [for int])
Substring ('Thomas 'from 2 for 3)
To_char (timestamp, text)
To_char (current_timestamp, 'hh12: MI: ss ')
To_timestamp (text, text)
To_timestamp ('2017-10-29 12:27:05 ', 'yyyy-MM-DD HH24: MI: ss ')

...

Current_date today's date
Current Time of current_time
Current_timestamp current date and time
Localtime today's time
Localtimestamp Date and Time
Now () current date and time (equivalent to current_timestamp)

 

Sequential operation functions
Nextval, currval, lastval, setval
Other functions
NULLIF: NULLIF returns NULL only when value1 and value2 are equal. Otherwise, it returns value1.
GREATEST and LEAST: GREATEST and LEAST functions select the maximum or minimum value from a list of any numeric expressions.

System information functions
Current_database () Name of the current database
Current_schema () Name of the Current Mode
Current_user user name in the current execution environment
Version () PostgreSQL version information
......

5. Use the PostgreSQL database in the development framework

This is one of the multiple databases supported. In my previous article, I used the Microsoft Enterprise Library for database access projects, implementing the support for multiple databases is a simple introduction to the use of multiple databases. However, if the processing is based on the multi-data Implementation layer, it needs to be implemented in a framework layered manner, with the rapid generation of code generation tool Databasse2Sharp, the entire development process becomes more efficient and fast, and the hierarchical directories are easy to understand.

Taking PostgreSQL database support as an example, the Development Framework supports the following features:

1) the underlying framework uses the Microsoft Enterprise Library as the underlying database access module.
2) multi-database support, unified use of database access objects for enterprise databases, consistent operations
3) The development process of PostgreSQL is similar to that of SQL Server.
4) in the configuration file, modify ComponentDbType to npgsql to set it to PostgreSQL.
5) The database points to switch when the BaseBLL object is built.

The above is the overall design and inheritance relationship of the data access layer of the entire framework. during actual development, we can use the code generation tool Database2Sharp for rapid development, you can generate the underlying content of the framework, such as the WInfrom interface or Web interface, to achieve rapid integration.

When developing and implementing PostgreSQL data layer objects, we can set other database layers (such as those of SQLServer ), then, modify the base class and namespace of the corresponding data access layer to construct the PostgreSQL layer.