SQL Server & PostgreSQL
SQL Server (Compact Edition)
SQL CE 中 sp_rename 仅支持表的修改
sp_rename 'oldTableName','newTableName';
在 SqlServer 2005 Management Studio 中，您必须使用新名称创建一个新列，然后使用旧列中的值更新它，然后删除旧列。如果列是索引的一部分，那么最后一个操作是困难的。
SQL CE 查询表信息
SELECT table_name_, column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name_='stu';
SQL CE 和其他 SQL 的区别
SQL CE 使用 EFCore 连接并持久化对象：
https://entityframework-extensions.net/efcore-sql-server-compact-provider
确定Firebird SQL版本
SELECT rdb$get_context('SYSTEM', 'ENGINE_VERSION') as version from rdb$database;
不同数据库之间的类型映射关系
- 常用关键字翻译
| T-sql | postgres |
|---|---|
cast(value AS datetime)convert(datetime, value) |
cast(value AS timestamp(3)) |
datepart(day, value) |
extract(day FROM value) |
dateadd(day, value1, value2) |
value2 + value1 * INTERVAL '1 day' |
dateadd(d,-1,dateadd(mm, datediff(m,0,column_name)+1,0))当月的最后一天 |
date_trunc('month', column_name) + INTERVAL '1 month- 1 day' |
len(value) |
char_length(value) |
| + | || |
| IDENTITY (1,1) | GENERATED BY DEFAULT AS IDENTITY |
| ROWVERSION | bytea |
CREATE TABLE table_name (column_name varbinary(46) NOT NULL DEFAULT ((0))) |
CREATE TABLE table_name (column_name bytea NOT NULL DEFAULT E'\x00000000') |
DECLARE @tablename TABLE(column_name1 nvarchar(500), column_name2 nvarchar(500)) |
CREATE TEMPORARY TABLE tablename (column_name1 nvarchar(500), column_name2 nvarchar(500))WITH tablename AS (SELECT …) |
| NOCHECK CONSTRAINT all WITH CHECK CHECK CONSTRAINT all |
DISABLE TRIGGER ALL ENABLE TRIGGER ALL |
- 常用类型映射
| SQL Server | Postgres |
|---|---|
| smallint | smallint, int2 |
| int | integer, int, int4 |
| bigint | bigint, int8 |
| tinyint | 不支持 |
float(n) 1 <= n <= 24, real |
float(n) 1 <= n <= 24, real, float4 |
float(n) 25 <= n <= 53 |
double precision, float(n) 25 <= n <= 53, float8 |
| numeric, decimal | numeric, decimal |
| money, smallmoney | money In SQL Server, money is (19,4) and smallmoney is (10,4) in , but in Postgres money is (19,2) |
| varbinary(n) | bytea with check Postgres uses 'check' to simulate n |
| varbinary(max), image | bytea |
| binary(n) | 不支持 |
| date | date |
| datetime | timestamp(3) without time zone |
| datetime2(n) | timestamp(n) without time zone In SQL Server 0 <= n <= 7, but in Postgres 0 <= n <=6 |
| datetimeoffset(n) | timestamp(n) with time zone, timestamptz In SQL Server 0 <= n <= 7, but in Postgres 0 <= n <=6 |
| bit | bit, bit(1), boolean, bool |
| char(n) | character(n), char(n) |
| nchar(n) | character(n), char(n) For UCS-2 encoding, the storage size is two times n bytes |
| varchar(n) | character varying(n), varchar(n) |
| nvarchar(n) | character varying(n), varchar(n) For UCS-2 encoding, the storage size is two times n bytes |
| text | text |
| ntext | text |
- Postgres pg_stat_statements
Create Extension pg_stat_statements;
Select * from pg_available_extensions where name = 'pg_stat_statements';
ChangeDB
安装ChangeDB
yscorecore/changedb (github.com)
dotnet SDK 6.0
dotnet tool restore
dotnet tool install changedb.consoleapp -g
dotnet tool list -g
运行命令
changedb migration {source-database-type} "{source-connection-string}" {target-database-type} "{target-connection-string}"
sqlserver -> postgres
changedb migration sqlserver "Data Source=(LocalDB)\MSSQLLocalDB;Initial Catalog=<DatabaseName>;Integrated Security=SSPI;"
postgres "Server=127.0.0.1;Port=5432;Database=<PostgresDatabaseName>;User Id=<postgres>;Password=<xxxxxx>;"
sqlce -> sqlserver
ChangeDb migration --max-fetch-bytes 10000 sqlce "Data Source=C:\xxxxxx.myox;Max Database Size=2048; Persist Security Info=False;"
sqlserver "Data Source=(LocalDB)\MSSQLLocalDB;Initial Catalog=<DatabaseName>;Integrated Security=SSPI;"
sqlce -> postgres
ChangeDb migration --max-fetch-bytes 10000 sqlce "Data Source=C:\xxxxxx.myox;Max Database Size=2048; Persist Security Info=False;"
postgres "Server=localhost;port=5432;Database={DBName};User Id=postgres;Password={PASSWORD}"
复制数据库备份
CREATE DATABASE db_backup WITH TEMPLATE "db"
