Ing Data Types Between PostgreSQL and SQL Server

ArticleDirectory

• Preface

Preface

PostgreSQL 8.1 was launched. I plan to migrate some SQL server applications to PostgreSQL. The first thing I need to migrate is tables, therefore, we must first figure out the ing relationship between the data types in the two databases. I checked the PostgreSQL documentation and made the following table to help you.

 

SQL Server 2000	Microsoft Jet-SQL 4.0	PostgreSQL 8.x
Bigint	Decimal	Bigint
Binary (N)	Binary (N)	Bytea (N)
Bit	Bit	Boolean, bool
Char (N)	Char (N)	Char (N)
Datetime	Datetime	Timestamp (date, time)
Decimal	Decimal	Decimal
Float	Float	Real, float4
Image	Image	Bytea
Int	Int	Integer, Int, int4
Money	Currency	Decimal
Nchar (N)	Nchar (N)	Char (N)
Ntext	Longtext	Text
Numeric	Decimal	Numeric
Nvarchar (N)	Nchar varying (N)	Varchar (N)
Real	Real	Real
Smalldatetime	Datetime	Timestamp (date, time)
Smallint	Smallint	Smallint, int2
Smallmoney	Currency	Decimal
Text	Longtext	Text
Timestamp	Binary	None
Tinyint	Byte	None
Uniqueidentifier	Guid	None
Varbinary (N)	Varbinary (N)	Bytea (N)
Varchar (N)	Varchar (N)	Varchar (N)

Note:The money (currency) Data Type in PostgreSQL is now obsolete and can be replaced by numeric or decimal and the to_char function.

* In SQL ServerDatetime,SmalldatetimeThe data type contains the date and time sections. The difference is that the precision is different. It corresponds toTimestampType (including date and time ). In PostgreSQL, if you only need the date part, you can useDateType, but only time is required.TimeType.

* In PostgreSQLBytea(N) Is always a variable binary byte array, which is equivalentVarbinary(N), If not specifiedByteaThe maximum number of bytes is an unlimited length variable byte array, which is equivalentImageData type.

* The character types in PostgreSQL are not different from those in SQL Server for local text and international text, whether the text data stored in PostgreSQL is internationalized or localized depends on the text encoding settings of the database.
To ensure the internationalization of data storage, we recommend that you set the text encoding method of the database to UTF-8 or Unicode to ensure that the stored text can be viewed and stored by users in different regions and cultures.

* For guid-type data, you can useBytea(16) To save the data. You can use byte array functions to compare the data.