You can connect to an ApsaraDB RDS for PostgreSQL instance for which Babelfish is enabled over the Tabular Data Stream (TDS) port. This topic describes the common operations and compatibility of SQL statements of the RDS instance after the connection.

Common operations

Category Sample SQL statement
System query Query the version of a database. 
SELECT @@version;
Query the information of a database. 
SELECT * FROM sys.databases;
Database-related operations Create a database. 
CREATE DATABASE testdb;
Note If you use the Single-DB migration mode, you can create only one database. If you have created a database, you cannot create another database.
Query a database. 
SELECT * FROM sys.databases WHERE name = 'testdb';
Switch to a different database. 
USE testdb
GO
SELECT db_name();
Delete a database. 
DROP DATABASE testdb;
Schema-related operations Create a schema. 
CREATE SCHEMA sch_demo;
View a schema. 
SELECT * FROM sys.schemas AS sch WHERE sch.name = 'sch_demo';
Use a schema to create a table. 
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';
Delete a schema.
Note If a table is created by using a schema, you must delete the table before you delete the schema. 
DROP TABLE sch_demo.tb_demo;
GO

DROP SCHEMA sch_demo;
GO
Table-related operations Create a table. 
USE testdb
GO

CREATE TABLE dbo.tb_test(
    id int not null IDENTITY(1,1) PRIMARY KEY,
    name varchar(50))
GO
Query a table. 
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
Create a field. 
ALTER TABLE dbo.tb_test ADD col_added bigint null;
GO
Modify a field in a table. 
ALTER TABLE dbo.tb_test ALTER column col_added varchar(50);
GO
Delete a field from a table. 
ALTER TABLE dbo.tb_test DROP column col_added;
GO
Create an index. 
CREATE INDEX ix_tb_test_name ON tb_test(name);
GO
Delete an index. 
DROP INDEX ix_tb_test_name ON tb_test;
GO
Database-related operations 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;
Operations related to a stored procedure Create a stored procedure. 
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
View a stored procedure. 
SELECT *
FROM sys.procedures
WHERE name = 'up_getdemodata';
Execute a stored procedure. 
EXEC dbo.UP_getDemoData @id = 7;
GO
Delete a stored procedure. 
USE testdb
GO
DROP PROC dbo.UP_getDemoData
GO

Compatibility

Note This section provides only common incompatibility scenarios. For more information, see Babelfish for PostgreSQL official documentation.
The following SQL statements are not supported for an RDS instance for which Babelfish is enabled:
  • View the schema of a table. Example: 
    EXEC sp_help 'dbo.tb_test'
  • When you modify a field in a table, set the default value to null. Example: 
    ALTER TABLE dbo.tb_test ALTER column col_added varchar(50) null;
GO
  • Recreate an index. We recommend that you delete an index and then create an index. Example: 
    ALTER INDEX ix_tb_test_name ON tb_test REBUILD;
GO
  • Modify a stored procedure. We recommend that you delete a stored procedure and then create a stored procedure. Example: 
    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
  • Query an execution plan. Example: 
    SET showplan_xml ON
SELECT * from tb_test;