PostgreSQL 8.0.2 application report)

Overview

PostgreSQL 8.0.2 has been officially released. This version is widely recommended and has been directly supported by the windows2k/xp platform since 8.0, the pgadmin III Graphic Interface database management tool (open-source) is attached with the installation package. You can use this graphic management interface to create a data base, table space, and schema) and other database objects.Note:PostgreSQL mentioned in this Article refers to 8.0.2 for Windows.

Install

Installing the PostgreSQL server for Windows is very simple, first download the installation package from the official website, and then execute the postgresql-8.0.msi Installation File, followed by the prompt step by step on it. You need to set the PostgreSQL Windows Service password and the Super User's Postgres password. The password cannot be blank!In addition, PostgreSQL 8.0.2 must be installed in NTFS partitions.

Use

After the installation is complete, the postgresql service is started by default.Start"-"Program"-"PostgreSQL 8.0"Run"Pgadmin IIIYou can use the graphical management interface to perform various database operations.

Development

1. Support for Chinese

When you create a database, we recommend that you specify the encoding method as Unicode (UTF-8), explicitly specify encoding = Unicode in the client connection string, otherwise there may be garbled characters when reading and writing multi-byte characters.

Ii. Case sensitivity

PostgreSQL is case-sensitive and converts the database object name in SQL statements to lowercase by default. Therefore, if you specify a name that is case-insensitive when creating a database object, when you access these objects through SQL statements, you must use double quotation marks (") to enclose the database objects, prompting that PostgreSQL does not need to convert the object name to lowercase. Otherwise"XXXXX object does not exist"For example, if your database contains a table named Tuser, you must use a query statement similar to this in the graphical query tool provided by PostgreSQL to correctly execute: Select * from "Tuser ", of course, it is not case sensitive to the reserved words and keywords in the SQL standard, so it is completely possible to write the statement as select * from "Tuser.

In addition, PostgreSQL is case sensitive to data, which is different from sqlserver (sqlserver is not sensitive by default). For example, the Tuser table contains the field name, there is a record with the name field value "Tony Tang ".

Select * from "Tuser" where "name" like '% Tony % ';

This record cannot be found, but you can write it as follows:

Select * from "Tuser" where upper ("name") like '% Tony % ';

 

Oh, do you think this is not very nice, and you are worried that the performance will be affected? Fortunately, PostgreSQL provides the keyword Ilike to help us solve this problem. This is really a very interesting keyword (I like ).LikeReplaceIlikeYou can.

Finally, we recommend that you use single quotes as the String constant identifier when writing SQL scripts, although double quotes and single quotes in the MS sqlserver can be used as the identifier of a String constant, however, PostgreSQL strictly distinguishes this feature from each other (double quotation marks cannot be used as the identifier of a String constant). To be secure, we recommend that you use double quotation marks (") for all database object names (") explicit conventions to ensure strict matching of case.

 

Iii.. Net Development Problems

First, you can download the PostgreSQL. Net Data Provider Program from here. The demo provided in this article is developed based on npgsql0.7beta5. Note that to publish a client program, onlyNpgsql. dllAndMono. Security. Protocol. TLS. dllYou can use these two dynamic libraries without installing any other PostgreSQL client class libraries! ReallyGreenAh ~

In. NET through ado.net to access PostgreSQL database is simply a kind of enjoyment, especially those who are used to using the MS-SQL server developers, Because regardless of the preparation of postgresql functions (stored procedures) still, calling these functions is roughly the same as that of sqlserver. Especially, the encoding method for the returned dataset is exactly the same as that of sqlserver. It is not as troublesome as accessing the dataset returned by the Oracle stored procedure to use the referenced cursor, therefore, it is very easy to port these two types of database access code!

The SQL operation that returns multiple tables by calling the fill method of the dataadapter object, it also automatically fills in the corresponding multiple able into the dataset, which is exactly the same as operating the MS-SQL server.This article provides an example of using. Net to operate PostgreSQL. Download the example from this article.

Others

You can see that multiple zookeeper s.exe processes have been started through the supervisor. Do not be alarmed. At least four of them are the processes of the PostgreSQL system service. Currently, PostgreSQL in Windows is due to code porting problems, multiple processes will be created when multiple users connect, but I personally think this feature may be improved in later versions (after all, for Windows is just released ), if you want to apply the database to commercial products now, we recommend that you use the Linux/Unix Version.

By default, you can only use 127.0.0.1 as the address to connect to the server. To connect from another machine, you must modify pg_hba.conf and PostgreSQL on the PostgreSQL server. the content in the conf configuration file is located in$ Postgressql $\ 8.0 \ data.

• Open the PostgreSQL. conf file in notepad and release the listen_addresses configuration item to: listen_addresses = '*'
• Open the pg_hba.conf file in notepad and add the following lines under # IPv4 local connections:
  Host All all 192.168.0.0/24 MD5

  This means that PostgreSQL allows any host with the IP address 192.168.0. * to connect to all databases on the server.

 

Summary

PostgreSQL's noble origins and brilliant history, coupled with its inherent characteristics of many enterprise databases, make it the most commercially available open-source database, another important thing is that PostgreSQL's many development interfaces are very helpful for developers to choose from. The Open Source Wind has already penetrated the core region of the closed enterprise application of the database. For example, the open source trend of database products highlights the pressure on IBM oracle, enterprise applications have begun to consider cross-platform (for example, the third option of ERP.

Whether cross-platform and open-source databases reduce the total cost of enterprise applications, but at least one option is provided to customers, from the perspective of market development and application adaptation, they are all very positive.Imagine how exciting a combination of Linux + mono/. NET + PostgreSQL = ERP/Any enterprise applications!

Download example

 

1. Sample Database: http://www.Zongsoft.com/Download/PostgreSQL_Demo_db.rar
2. Execute File Download: http://www.Zongsoft.com/Download/PostgreSQL_Demo_bin.rar
3. Source code download: http://www.Zongsoft.com/Download/PostgreSQL_Demo_src.rar

Createdb contained in the postgresql_demo_db.rar file. SQL is the SQL script file of the database required to create the sample program. You can execute the following command in Psql: \ I createdb. or run createdb. BAT to automatically create.

 

Resource Link

 

1. PostgreSQL Official Website: http://www.postgreSQL.org
2. PostgreSQL Chinese site: http://www.pgsqldb.org
3. PostgreSQL Chinese Forum: http://bbs.pgsqldb.com/index.php
4. Why PostgreSQL instead of Oracle?