Sybase ASE to PostgreSQL Migration

SQLines tools help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from SAP Sybase Adaptive Server Enterprise (Sybase ASE) to PostgreSQL.

Databases:

  • Sybase Adaptive Server Enterprise 16, 15, 12.x and 11.x
  • PostgreSQL 17.x, 16.x, 15.x, 14.x, 13.x, 12.x, 11.x, 10.x and 9.x

See also Sybase SQL Anywhere to PostgreSQL Migration.

Migration Reference

SQL Language Elements

Converting SQL language elements:

Sybase ASE PostgreSQL
1 string1 + string2 String concatenation string1 || string2
2 exp != NULL Non SQL-standard check for NOT NULL exp IS NOT NULL
exp <> NULL
exp = NULL Non SQL-standard check for NULL exp IS NULL
3 @@ROWCOUNT Get the number of affected rows ROW_COUNT

Data Types

Converting character data types:

Sybase ASE PostgreSQL
1 CHAR(n) Fixed-length string CHAR(n)
2 NCHAR(n) Fixed-length national character string CHAR(n)
3 NVARCHAR(n) Variable-length national character string VARCHAR(n)
4 TEXT Variable-length character data, ⇐ 2 GB TEXT
5 UNICHAR(n) Fixed-length Unicode UTF-16 string CHAR(n)
6 UNITEXT Variable-length Unicode UTF-16 data, ⇐ 1 GB TEXT
7 UNIVARCHAR(n) Variable-length Unicode UTF-16 string VARCHAR(n)
8 VARCHAR(n) Variable-length string VARCHAR(n)

Converting other data types:

Sybase ASE PostgreSQL
1 BIGDATETIME Date and time with fraction TIMESTAMP
2 BIGINT 64-bit integer BIGINT
3 BIGTIME Time (Hour, minute, second and fraction) TIME
4 BINARY(n) Fixed-length binary data, 1 ⇐ npagesize BYTEA
5 BIT 0 or 1; NULL is not allowed BOOLEAN
6 DATE Date (year, month and day) DATE
7 DATETIME Date and time with fraction TIMESTAMP
8 DECIMAL(p,s), DEC(p,s) Fixed-point number DECIMAL(p,s), DEC(p,s)
9 DOUBLE PRECISION Double-precision floating-point number DOUBLE PRECISION
10 FLOAT(p) Floating-point number DOUBLE PRECISION
11 IMAGE Variable-length binary data, ⇐ 2G BYTEA
12 INT, INTEGER 32-bit integer INT, INTEGER
13 MONEY 64-bit currency amount MONEY
14 NUMERIC(p,s) Fixed-point number NUMERIC(p,s)
15 REAL Single-precision floating-point number REAL
16 SMALLDATETIME Date and time TIMESTAMP(0)
17 SMALLINT 16-bit integer SMALLINT
18 SMALLMONEY 32-bit currency amount MONEY
19 TIME Time (Hour, minute, second and fraction) TIME
20 TINYINT 8-bit unsigned integer, 0 to 255 SMALLINT
21 UNSIGNED BIGINT 64-bit unsigned integer NUMERIC(20)
22 UNSIGNED INT 32-bit unsigned integer NUMERIC(10)
23 UNSIGNED SMALLINT 16-bit unsigned integer NUMERIC(5)
24 VARBINARY(n) Variable-length binary string BYTEA

Built-in SQL Functions

Converting string functions:

Sybase ASE PostgreSQL
1 CHAR_LENGTH(string) Number of characters in string CHAR_LENGTH(string)
2 CHARINDEX(substring, string) Get substring position in string POSITION(substring IN string)
3 CONVERT(VARCHAR, datetime, style) Convert datetime to string TO_CHAR(datetime, format)
4 LEN(string) Length in characters LENGTH(string)
5 STR_REPLACE(str, substring, with) Replace substring REPLACE(str, substring, with)
6 SUBSTRING(string, start, length) Return substring SUBSTRING(string, start, length)

Converting datetime functions:

Sybase ASE PostgreSQL
1 CONVERT(DATETIME, expr, style) Converts expr to datetime TO_TIMESTAMP(expr, format)
2 CONVERT(TIME, expr) Converts expr to TIME CAST(expr AS TIME)
3 DATEADD(dd, int, datetime) Add days to datetime datetime + INTERVAL 'int DAY'
DATEADD(dd, exp, datetime) datetime + exp * INTERVAL '1 DAY'
4 DATENAME(unit, datetime) Extract unit from datetime TO_CHAR(datetime, format)
5 DAY(datetime) Get the day of datetime EXTRACT(DAY FROM datetime)
6 GETDATE() Get the current date and time NOW()

Numeric functions:

Sybase ASE PostgreSQL
1 CONVERT(BIGINT, exp) Convert to integer CAST(exp AS BIGINT)
CONVERT(INT | INTEGER, exp) CAST(exp AS INT | INTEGER)
CONVERT(SMALLINT, exp) CAST(exp AS SMALLINT)
CONVERT(TINYINT, exp) CAST(exp AS SMALLINT)
2 CONVERT(NUMERIC(p,s), exp) Convert to number CAST(exp AS NUMERIC(p,s))

NULL handling functions:

Sybase ASE PostgreSQL
1 ISNULL(exp, replacement) Replace NULL with the specified value COALESCE(exp, replacement)

SELECT Statement

Converting SQL queries:

Sybase ASE PostgreSQL
1 SELECT TOP n … FROM … Select n rows only SELECT … FROM … LIMIT n
2 SELECT … INTO #tmp_table Create a temporary table using SELECT SELECT … INTO TEMPORARY tmp_table
3 SELECT alias = expr Non-standard column alias form SELECT expr alias
4 *= and =* Legacy outer joins LEFT OUTER and RIGHT OUTER

DELETE Statement

Converting DELETE statement:

Sybase ASE PostgreSQL
1 DELETE [FROM] tab Delete rows DELETE FROM tab FROM keyword is required

CREATE TABLE Statement

Converting CREATE TABLE statement keywords and clauses:

Sybase ASE PostgreSQL
1 IDENTITY Identity column (no start and increment allowed) GENERATED ALWAYS AS IDENTITY
(START WITH start INCREMENT BY increment)
2 IDENTITY can be defined on DECIMAL, NUMERIC columns with 0 scale Integer columns must be used

Temporary tables:

Sybase ASE PostgreSQL
1 CREATE TABLE #name Temporary table name starts with # CREATE TEMPORARY TABLE name

CREATE PROCEDURE Statement

Converting stored procedures from Sybase ASE to PostgreSQL:

Sybase ASE PostgreSQL
1 CREATE PROCEDURE name CREATE OR REPLACE PROCEDURE name
2 @param [AS] datatype = default OUT | OUTPUT p_param IN | INOUT datatype = default
3 Optional () for procedure parameters () required
4 AS Changed AS $$
5 Declarations inside BEGIN block DECLARE block is between AS and BEGIN clauses
6 END End of procedure block END; $$ LANGUAGE plpgsql;

Transact-SQL Statements

Converting procedural Transact-SQL statements used in stored procedures, functions and triggers from Sybase ASE to PostgreSQL:

Variable declaration and assignment:

Sybase ASE PostgreSQL
1 DECLARE @var [AS] datatype(len) [= default] Variable declaration var datatype(len) [:= default];

Flow-of-control statements:

Sybase ASE PostgreSQL
1 IF condition BEGIN … END IF statement IF condition THEN … END IF;
2 WHILE condition BEGIN … END WHILE loop WHILE condition LOOP … END LOOP;

Cursors operations and attributes:

Sybase ASE PostgreSQL
1 CLOSE cur
DEALLOCATE [CURSOR] cur
Close a cursor CLOSE cur;
2 @@SQLSTATUS = 0 Fetch was successful FOUND
@@SQLSTATUS != 2

Stored procedure calls:

Sybase ASE PostgreSQL
1 EXEC sp_name @param1 = value1,… Execute a procedure CALL sp_name(p_param1 => value1,…)

System Procedures

Converting system stored procedures from Sybase ASE to PostgreSQL:

Sybase ASE PostgreSQL
1 sp_addtype name, "basetype(len)",
"not null"
Create a user-defined type CREATE DOMAIN name AS basetype(len)
NOT NULL
2 sp_bindrule name, "table.column" Assign a rule to a table column ALTER TABLE table ADD CHECK rule_condition

SQL Statements

Converting SQL statements from Sybase ASE to PostgreSQL:

Sybase ASE PostgreSQL
1 CREATE RULE name AS condition Create a domain of acceptable values Converted to a CHECK constraint
2 USE name Change the database SET SCHEMA 'name' If databases are mapped to schemas