本文已参与「新人创作礼」活动.一起开启掘金创作之路。
Babelfish for RDS PostgreSQL常用操作及兼容性说明
本文介绍通过TDS端口连接Bablefish for RDS PostgreSQL实例后的常见操作，以及SQL的兼容情况说明。
常见操作
| 操作类别 | SQL示例 | |
|---|---|---|
| 系统查询 | 查询版本 | ``` |
| SELECT @@version; |
| 查询数据库信息 | ```
SELECT * FROM sys.databases;
``` | |
| 数据库操作 | 创建数据库 | ```
CREATE DATABASE testdb;
```**说明** 迁移模式为single-db时，只支持创建一个数据库，如果您已创建了一个数据库，则无法再次创建。 |
| 查询数据库 | ```
SELECT * FROM sys.databases WHERE name = 'testdb';
``` | |
| 切换数据库 | ```
USE testdb GO SELECT db_name();
``` | |
| 删除数据库 | ```
DROP DATABASE testdb;
``` | |
| Schema操作 | 创建Schema | ```
CREATE SCHEMA sch_demo;
``` |
| 查看Schema | ```
SELECT * FROM sys.schemas AS sch WHERE sch.name = 'sch_demo';
``` | |
| 创建Schema下表 | ```
CREATE TABLE sch_demo.tb_demo(id int); SELECT sch.name AS schema_name, tb.name AS table_name FROM sys.tables AS tb INNER JOIN sys.schemas AS sch ON tb.schema_id = sch.schema_id WHERE tb.name = 'tb_demo';
``` | |
| 删除Schema | **说明** 如果Schema下存在表，需要先删除表后，再删除Schema。```
DROP TABLE sch_demo.tb_demo; GO DROP SCHEMA sch_demo; GO
``` | |
| 表操作 | 新建表 | ```
USE testdb GO CREATE TABLE dbo.tb_test( id int not null IDENTITY(1,1) PRIMARY KEY, name varchar(50)) GO
``` |
| 查询表 | ```
SELECT sche.name AS schema_name, tb.name AS table_name FROM sys.tables AS tb INNER JOIN sys.schemas AS sche ON tb.schema_id = sche.schema_id WHERE tb.name = 'tb_test'; GO
``` | |
| 新增字段 | ```
ALTER TABLE dbo.tb_test ADD col_added bigint null; GO
``` | |
| 修改表字段 | ```
ALTER TABLE dbo.tb_test ALTER column col_added varchar(50); GO
``` | |
| 删除表字段 | ```
ALTER TABLE dbo.tb_test DROP column col_added; GO
``` | |
| 创建索引 | ```
CREATE INDEX ix_tb_test_name ON tb_test(name); GO
``` | |
| 删除索引 | ```
DROP INDEX ix_tb_test_name ON tb_test; GO
``` | |
| 数据库操作 | INSERT | ```
INSERT INTO dbo.tb_test SELECT 'A' UNION ALL SELECT 'B'; GO
``` |
| SELECT | ```
SELECT * FROM dbo.tb_test;
``` | |
| UPDATE | ```
UPDATE TOP(1) dbo.tb_test SET name = 'A_updated'; GO
``` | |
| DELETE | ```
DELETE TOP(1) FROM dbo.tb_test; GO SELECT * FROM dbo.tb_test;
``` | |
| 存储过程 | 创建存储过程 | ```
USE testdb GO CREATE PROC dbo.UP_getDemoData( @id int ) AS BEGIN SET NOCOUNT ON SELECT * FROM dbo.tb_test WHERE id = @id END; GO
``` |
| 查看存储过程 | ```
SELECT * FROM sys.procedures WHERE name = 'up_getdemodata';
``` | |
| 执行存储过程 | ```
EXEC dbo.UP_getDemoData @id = 7; GO
``` | |
| 删除存储过程 | ```
USE testdb GO DROP PROC dbo.UP_getDemoData GO
``` | |
## 兼容性说明
**说明** 本文仅介绍了部分常见不兼容场景，更多信息，请参见[Babelfish for PostgreSQL官方文档](https://babelfishpg.org/docs/usage/limitations-of-babelfish/)。
不支持的SQL操作如下：
- 查看表结构。
```
EXEC sp_help 'dbo.tb_test'
```
- 不支持在修改表字段时设置默认值NULL。
```
ALTER TABLE dbo.tb_test ALTER column col_added varchar(50) null;
GO
```
- 不支持重建索引，建议删除后，重新创建。
```
ALTER INDEX ix_tb_test_name ON tb_test REBUILD;
GO
```
- 不支持修改存储过程，建议删除后，重新创建。
```
USE testdb
GO
ALTER PROC dbo.UP_getDemoData(
@id int
)
AS
BEGIN
SET NOCOUNT ON
SELECT *
FROM dbo.tb_test
WHERE id >= @id
END;
GO
```
- 不支持执行计划（showplan_xml）。
```
SET showplan_xml ON
SELECT * from tb_test;
```
