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

Recently prepared for PostgreSQL database development related knowledge, this article summarizes the PPT content through the blog record sharing, this essay's main content is to introduce the PostgreSQL database basic information, and how to use the PostgreSQL database in our development framework, I hope you will make a lot of suggestions.

1. PostgreSQL Database Introduction

PostgreSQL, developed by the computer department at the University of California, Berkeley, has now been renamed PostgreSQL POSTGRES. It has a lot of good features:

Open Source : PostgreSQL is a free object-relational database server (database management system), which is distributed under a flexible bsd-style license.

Good compatibility : The features of PostgreSQL cover sql-2/sql-92 and sql-3/sql-99.

High Reliability : PostgreSQL advertises itself as the most advanced open source database in the world. Reliability is the highest priority for PostgreSQL. It is known for rock-solid quality and good engineering, supporting high-transaction, mission-critical applications.

widely used : Since MySQL was acquired by Oracle, PostgreSQL has become the first choice for open source relational databases and is functionally an open-source database that transcends MySQL.

Good support : PostgreSQL support for the interface is also very rich, almost all types of database client connection, the most abundant data types of support, some data types can be said that even commercial databases are not available, such as IP type and geometry type;

2. Installation database and management tools

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

Database Administration Tools
Navicat for PostgreSQL (e.g. V11.1)

Database modeling Tools
PowerDesigner (e.g. V16.5)

Database operations:

1) Installing the PostgreSQL database
2) Installing the NAVICAT for PostgreSQL Management database tool

3) Installing the PowerDesigner modeling tool

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

3. PostgreSQL Database syntax

General processing of SQL, same as other databases, such as creating, deleting tables and other general operations. Here are some common actions on PostgreSQL that are excerpted from the Web.

1) Definition of the table:

1. Create a table:

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


   

	
    


   


  

	
   


  

2. Delete the 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:
A check constraint is the most common type of constraint in a table, and it allows you to declare that a value in a field must satisfy a Boolean expression. Not only that, we can also declare check constraints at the table level.

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 constraint is a non-null constraint, that is, the field of the constraint cannot insert a null value, or the existing data is updated to a null value.

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

5. Primary key and foreign key:
Technically, a PRIMARY KEY constraint is only a combination of a unique constraint and a non-null constraint.

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
); 

As with uniqueness constraints, a primary key can act on multiple fields at the same time, forming a federated primary key:

 
CREATE TABLE example (
a integer,
b integer,
c integer, PRIMAR

A FOREIGN key constraint declares that the numeric value of a field (or a set of fields) must match the value of some rows in another table. We call this behavior the referential integrity between the 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
); 

When a referential constraint relationship exists between multiple tables, if you want to delete a row record in the referenced table (primary key table), the delete operation will fail because the row record's primary key field value may be associated with a record in its reference table (foreign key table). If you want to do this, one obvious way is to delete the reference table and the row associated with the record before deleting the row record in the referenced table. It should be explained, however, that PostgreSQL provides a more convenient way for us to do this.

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)
     ); 

Restrictions and cascading deletions are the two most common options. RESTRICT suppresses the deletion of referenced rows. The no action means that if there are any references to the row when checking the constraint, an error is thrown, and if you do not declare anything, then it is the default behavior. (The actual difference between the two choices is that no ACTION allows the constraint check to be deferred until later in the transaction, and RESTRICT not.) Cascade declares that when a referenced row is deleted, the row referencing it is automatically deleted. There are also two options for actions on foreign key fields: Set NULL and set DEFAULT. This causes the fields referencing them to be set to either empty or default values when the referenced rows are deleted. Please note that these options do not allow you to escape being observed and constrained. For example, if an action declares SET default, but the default value does not satisfy the foreign key, then the action fails. Like on DELETE, there is the on Update option, which is called when the referenced field is modified (updated). The available actions are the same.

2) Modification of the table:
1. Add a field:

 
ALTER TABLE products ADD COLUMN description text;

The new field is initially populated with the given default values for rows that already exist in the table (the default is null if you do not declare the default clause).

When you add a field, you can assign a constraint to the field at the same time.

 
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 referenced, the delete operation above will fail. You can use the following syntax if you want to delete all of its reference fields when you delete the referenced field.

 
 
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 the constraint:

ALTER TABLE DROP CONSTRAINT Some_name;

For display-named constraints, can be directly deleted according to their name, for the implicit auto-named constraint, you can get the name of the constraint by Psql \d tablename. As with the Delete field, if you want to delete a constraint that has dependencies, you need to use cascade. An example is a FOREIGN key constraint that relies on a unique constraint or a PRIMARY KEY constraint on the referenced field.

3) Permissions:
Only the owner of the table can modify or delete permissions for the table. To give a permission, we use the GRANT command to revoke a permission and use the REVOKE command.
It should be noted that public is a special "user" that can be used to assign permissions to every user in the system. Write all in the location of the claim permission to assign all permissions associated with that object type.

GRANT UPDATE ON table_name TO user;-Give the table 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 Superuser) can assign or revoke permissions to the object. However, we can give a "with GRANT option" permission so that the person who accepts the permission grants the permission to the other person. If the grant option is later revoked, all users who have accepted the permission from the recipient (either directly or through a level-connected authorization) will lose the permission.

4. Database Syntax Differences section

1) Special field correspondence relationship (partial)

2) Pagination Processing
MySQL Paging common notation

 
select * from persons  limit 0,10;

The starting position is 0 and 10 records are queried.

Compatible with the syntax of MySQL and PostgreSQL

 
select * from persons limit 10 offset 0

Query starting from Start 0, return 10 records

3) Other types
GUID generation (SQL Server is NEWID () function)

PostgreSQL starting with version 9.1, perform the following function to create a GUID

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

SQL qualifier
SQL Server is [], such as SELECT [Name] from [User]
PostgreSQL is "", such as select "Name" from User

Self-growth logo
SQL Server for identity (+)
PostgreSQL uses serial instead
SERIAL object is INT4 type (default), bigserial type corresponds to int8 type
A sequence created by a serial type is automatically deleted when its owning 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 (' 2016-10-29 12:27:05 ', ' yyyy-mm-dd HH24:MI:SS ')

...

Current_date Today's date
Current_time the time Now
Current_timestamp the current date and time
LocalTime Today's time
Localtimestamp Date and time
Now () Current date and time (equivalent to Current_timestamp)

Sequence manipulation functions
Nextval, Currval, Lastval, Setval
Other functions
NULLIF: Nullif returns NULL when and only if value1 and value2 are equal. Otherwise it returns value1.
The greatest and least:greatest and least functions select the maximum or minimum value from an arbitrary number expression list.

System Information functions
Current_database () The name of the current database
Current_schema () The name of the current mode
Current_User user name in the current execution environment
Version () PostgreSQL release information
......

5. Using the PostgreSQL database in the development framework

This is one of the multi-database support, I have in the previous essay, "Using Microsoft Enterprise Library in database Access projects to achieve a variety of database support" has a simple introduction to the use of multiple databases, but if it is based on multi-data implementation layer processing, Then it needs to be implemented in a hierarchical way, through the rapid generation of the Code generation tool Databasse2sharp, the whole development work becomes more efficient and faster, and the various hierarchical catalogs are unified and understandable.

We use PostgreSQL database support as an example to support this development on the development framework, with the following characteristics:

1) The framework uses Microsoft Enterprise Library as the underlying database access module
2) Multi-database support, unified use of Enterprise library database Access objects, consistent operation
3) PostgreSQL development is similar to the SQL Server development process
4) configuration file modification Componentdbtype to Npgsql can be configured as PostgreSQL
5) database pointing is the switch when the Basebll object is built

Above is the entire framework of the data access layer of the overall design and inheritance relationship, we in the actual development, we can use the Code generation tool DATABASE2SHARP for rapid development, can be generated framework of the underlying and Winfrom interface or Web interface and other content, This allows for rapid integration.

When we develop a PostgreSQL data-tier object, we can construct the PostgreSQL layer by putting the other database tiers (such as SQL Server) and then modifying the corresponding data access layer base classes and namespaces.

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