Login
Register
Actian.comNavigation
Learn About
Developing With
Ingres Talk
Information
Toolbox
Views
Ingres Cheatsheets
From Ingres Community Wiki
Jump to: navigation, search
Contents |
[edit]
Ingres Cheat Sheets
[edit]
Oracle to Ingres Terminology
| Oracle term or concept | Ingres Term or Concept | Description |
| backup, consistent | checkpoint, offline | a backup taken while the database is shut to users, and does not need to have any incomplete transactions "reapplied" or "undone" . |
| no direct equivalent | checkpoint, online | a backup taken while the database is in use. |
| backup, inconsistent | no direct equivalent | a backup taken while the database is in use, and may be in need of further recovery action to bring it back to a consistent state. |
| checkpoint | consistency point | Ingres will flush the buffers and "archive out" committed transactions into journals. |
| cluster | no direct equivalent | Ingres does not group files with similar/shared column definitions. |
| control file | configuration file | Ingres has a configuration file for each database - always "aaaaaaaa.cnf". |
| data dictionary | system catalog | Definition of all schema objects in the respective databases. |
| datafile | datafiles | the physical storage space in an Oracle database. Ingres stores all its data in the physical storage space. |
| initialisation parameter file | config.dat | Configuration file - Do NOT edit the "config.dat" file directly - use Configuration-By-Forms (cbf )or the visual equivalent (vcbf) . |
| redo log | transaction log | Ingres uses the one circular transaction log that is read by both the Archiver and the Recovery Server to maintain its integrity. |
| redo log, archived | journals | Ingres copies transactions from the transaction log and writes them to journal files. |
| System Change Number | Log Sequence Number | Unique reference number for each transaction. |
| tablespace | no direct equivalent | Defines the logical storage space in an Oracle database. The Ingres equivalent is (loosely) the data "location". |
| tablespace, system | no direct equivalent | Arguably, Ingres uses “iidbdb” to perform similar functions to the Oracle System tablespace |
| tablespace, temporary | II_WORK | Oracle can have dedicated "tablespace" allocated for its working storage. Ingres achieves this by using the II_WORK variable assigned to disk space. |
| tablespace, undo | no direct equivalent | "Undo" information is stored in the Ingres transaction log until either committed, or rolled back. |
| no direct equivalent | dump files | Ingres uses the dump file space to record transactions that are logged whilst doing online backups (online checkpoints) |
[edit]
SQL Server to Ingres Terminology
| SQL Server Term or Concept | Ingres Term or Concept | Description |
| Database Backup | checkpoint, offline | A backup taken while the database is shut to users, and does not need to have any incomplete transactions "reapplied" or "undone". |
| checkpoint | consistency point | Ingres will flush the buffers and "archive out" committed transactions into journals. |
| system table | system catalog | Definition of all schema objects in the respective databases. |
| data files | datafiles | The physical storage space. Ingres stores all its data in the physical storage space. |
| Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files | error log | errlog.log |
| SQL Server Configuration Manager | config.dat | Ingres uses a configuration file "config.dat" Do NOT edit the "config.dat" file directly - use Configuration-By-Forms (cbf )or the visual equivalent (vcbf). In SQLServer the Configuration Manager is used. |
| transaction log | transaction log | Ingres uses the one circular transaction log that is read by both the Archiver and the Recovery Server to maintain its integrity. SQLServer has 1 transaction log per database. |
| Change tracking | journals | Ingres removes committed transactions from the transaction log and writes them to journal files. |
| Log Sequence Number | Log Sequence Number | Unique reference number for each transaction. |
| tempdb | II_WORK | SQLServer uses a database called tempdb for all the temporary space it needs. Ingres achieves this by using the II_WORK variable assigned to disk space. |
| filegroup | DB Location | - |
| master | iidbdb | The system database that keeps information about users, databases, locations... |
| Mirroring, Replication | Replication | - |
| sqlcmd | sql | Command line tool for running sql queries (and sql scripts). |
[edit]
Oracle and Ingres Datatypes
| Oracle datatype | Ingres datatype | Ingres comment |
| BFILE | LONG BYTE | [ VW Limitation: Not supported ] - |
| BINARY_DOUBLE | FLOAT8 (FLOAT, DOUBLE PRECISION, F8 ) | This value range mapping is not an exact match but is as close as we can get, so user will need to validate that only numbers within the range of Ingres FLOAT8 will be stored. We can use Decimal if precision and scale are known.
Range: FLOAT8: -1.0e+38 to +1.0e+38 BINARY_DOUBLE: 2.22507485850720E-308 to 1.79769313486231E+308 |
| BINARY_FLOAT | FLOAT4, (F4, REAL) | Are these equivalent? - |
| BINARY_INTEGER | INTEGER (INTEGER4) | - |
| BLOB | LONG BYTE | [VW Limitation: Not supported] |
| CHAR | CHAR | - |
| CHAR VARYING(n) | VARCHAR(n) | - |
| CHAR(n BYTE) | CHAR(n) | In Oracle, 'n BYTE' specifies the number of bytes that can be stored. The 'n' in the Ingres type however refers to the number of characters. [ VW Limitation: Not supported ] |
| CHAR(n CHAR) | CHAR(n) | - |
| CHAR(n) | CHAR(n) | - |
| CHARACTER VARYING(n) | VARCHAR(n) | - |
| CHARACTER(n) | CHAR(n) | - |
| CLOB | LONG VARCHAR | - |
| DATE | TIMESTAMP | (use ANSIDATE if only no time part is required) |
| DEC(p,s) | DECIMAL(p,s) | DEC(p) maps tp DECIMAL(p,0), DEC maps to DECIMAL(39,0). |
| DECIMAL(p,s) | DECIMAL(p,s) | DECIMAL(p) maps tp DECIMAL(p,0), DECIMAL maps to DECIMAL(39,0). |
| DOUBLE_PRECISION | DOUBLE PRECISION (FLOAT8, FLOAT, F8) | This value range mapping is not an exact match but is as close as we can get, so user will need to validate that only numbers within the range of Ingres FLOAT8 will be stored. We can use Decimal if precision and scale are known.
Range: FLOAT4: -1.0e+38 to +1.0e+38 FLOAT8: 2.2E-306 to 1.79E+307 DOUBLE_PRECISION: 2.22507485850720E-308 to 1.79769313486231E+308 |
| FLOAT | DECIMAL(39,0) (FLOAT4) | Adjust the second parameter to add scale, 0 is quivalent to integer values. |
| FLOAT(0) - FLOAT(23) | FLOAT4 | - |
| FLOAT(24) - FLOAT(53) | FLOAT (FLOAT8) | - |
| FLOAT(54) - FLOAT(126) | DECIMAL(39,0) | Adjust the second parameter to add scale, 0 is quivalent to integer values |
| INT | INTEGER (DECIMAL(39,0) | This should ve verified as Ingres suports I1 - I8 (TINYINT, SMALLINT, INTEGER and BIGINT) |
| INTEGER | INTEGER | - |
| INTERVAL DAY TO SECOND | INTERVAL DAY TO SECOND | - |
| INTERVAL DAY TO SECOND(m) | INTERVAL DAY TO SECOND(m) | - |
| INTERVAL DAY(n) TO SECOND | INTERVAL DAY TO SECOND | - |
| INTERVAL DAY(n) TO SECOND(m) | INTERVAL DAY TO SECOND | - |
| INTERVAL YEAR TO MONTH | INTERVAL YEAR TO MONTH | - |
| INTERVAL YEAR(n) TO MONTH | INTERVAL YEAR TO MONTH | - |
| LONG | LONG VARCHAR | [ VW Limitation: Not supported ] |
| LONG RAW | LONG BYTE | [ VW Limitation: Not supported ] |
| LONG VARCHAR(n) | LONG VARCHAR | [ VW Limitation: Not supported ] |
| NATIONAL CHAR VARYING(n) | NVARCHAR(n) | - |
| NATIONAL CHAR(n) | NCHAR(n) | - |
| NATIONAL CHARACTER VARYING(n) | NVARCHAR(n) | - |
| NATIONAL CHARACTER(n) | NCHAR(n) | - |
| NATURAL | BIGINT (INTEGER8) | - |
| NATURALN | BIGINT (INTEGER8) | - |
| NCHAR | NCHAR | - |
| NCHAR VARYING(n) | NVARCHAR(n) | - |
| NCHAR(n) | NCHAR(n) | - |
| NCLOB | LONG NVARCHAR | [ VW Limitation: Not supported ] |
| NUMBER | DECIMAL(39,0) | - |
| NUMBER(p) | DECIMAL(p,0) | - |
| NUMBER(p,s) | DECIMAL(p,s) | where p is between 1 and 38 and s is less than or equal to p, If p = s then the maximum value that can be stored is 0.9... depending on the size of p. |
| NUMERIC(p,s) | DECIMAL(p,s) | NUMERIC(p) maps tp DECIMAL(p,0), NUMERIC maps to DECIMAL(39,0). |
| NVARCHAR2(n) | NVARCHAR(n) | - |
| PLS_INTEGER | INTEGER (INTEGER4) | - |
| POSITIVE | BIGINT (INTEGER8) | Ingres has no unsigned types - what are the range differences here ?- |
| POSITIVEN | BIGINT (INTEGER8) | - |
| RAW(n) | BYTE VARYING(n) | [ VW Limitation: Not supported ] |
| REAL | FLOAT8 | Ingres has a REAL datatype whichis equivalent to float4, what is the size of the Oracle REAL datatype- |
| SIGNTYPE | TINYINT | The Ingres TINYINT has the range -127 to + 127 - |
| SMALLINT | DECIMAL(39,0) (SMALLINT, I2, INT2) | Does the Oracle datatype have signed and unsigned attributes ?- |
| TIMESTAMP | TIMESTAMP | - |
| TIMESTAMP WITH LOCAL TIME ZONE | TIMESTAMP WITH LOCAL TIME ZONE | [ VW Limitation: Not supported ] |
| TIMESTAMP WITH TIME ZONE | TIMESTAMP WITH TIME ZONE | [ VW Limitation: Not supported ] |
| TIMESTAMP(n) | TIMESTAMP | [ VW Limitation: TIMESTAMP(n) where n is 0, 3 or 6 ] |
| TIMESTAMP(n) WITH LOCAL TIME ZONE | TIMESTAMP(n) WITH LOCAL TIME ZONE | [ VW Limitation: Not supported ] |
| TIMESTAMP(n) WITH TIME ZONE | TIMESTAMP(n) WITH TIME ZONE | [ VW Limitation: Not supported ] |
| VARCHAR(n) | VARCHAR(n) | - |
| VARCHAR2(n BYTE) | VARCHAR(n) | In Oracle, 'n BYTE' specifies the number of bytes that can be stored. The 'n' in the Ingres type however refers to the number of characters. |
| VARCHAR2(n CHAR) | VARCHAR(n) | - |
| VARCHAR2(n) | VARCHAR(n) | - |
[edit]
Sybase and Ingres Datatypes
| Sybase datatype | Ingres datatype | Ingres comment |
| BIGINT | BIGINT (INT8, INTEGER8, I8) | The range for signed BIGINT values is -2**63 to 2**63 - 1, or -9223372036854775808 to 9223372036854775807 - |
| BINARY | BYTE | - |
| BINARY(n) | BYTE(n) | - |
| BIT | TINYINT (INTEGER1, I1, INT1) | - |
| CHAR | CHAR | - |
| CHAR(n) | CHAR(n) | - |
| DATE | DATE | - |
| DATETIME | TIMESTAMP | TIMESTAMP can have optional elements [WITH[OUT] TIME ZONE] |
| DECIMAL | DECIMAL(18,0) | - |
| DECIMAL(p) | DECIMAL(p,0) | - |
| DECIMAL(p,s) | DECIMAL(p,s) | - |
| DOUBLE PRECISION | FLOAT8, DOUBLE PRECISION | - |
| FLOAT | FLOAT4 (F4, REAL) | - |
| FLOAT(p) | FLOAT(p) | - |
| IMAGE | LONG BYTE | - |
| INT | INT (INTEGER, INT4, INTEGER4, I4) | see INTEGER - |
| INTEGER | INTEGER (INT, INT4, INTEGER4, I4) | The range for signed integers is -2**31 to 2**31 - 1, or -2147483648 to 2147483647. - |
| MONEY | DECIMAL(19,4) | - |
| NCHAR | CHAR | Unlike SQL Server or Oracle where NCHAR columns use a character set that can be different than the database character set (UCS2 for SQL Server and typically UTF-8 or UTF-16 for Oracle), NCHAR columns in Sybase use the default database character set and therefore translate to the CHAR datatype in Ingres. |
| NCHAR(n) | CHAR(n) | See CHAR |
| NUMERIC | DECIMAL(18,0), NUMERIC(18,0) | - |
| NUMERIC(p) | DECIMAL(p,0), NUMERIC(p,0) | - |
| NUMERIC(p,s) | DECIMAL(p,s), NUMERIC(p,s) | - |
| NVARCHAR | VARCHAR | See CHAR |
| NVARCHAR(n) | VARCHAR(n) | See CHAR |
| REAL | REAL (F4, FLOAT4) | - |
| SMALLDATETIME | TIMESTAMP | - |
| SMALLINT | SMALLINT (I2, INT2, INTEGER2) | The range for signed SMALLINT values is -2**15 to 2**15 - 1, or -32768 to 32767. - |
| SMALLMONEY | DECIMAL(11,4) | - |
| TEXT | LONG VARCHAR | - If TEXT is known to less than 16000 (UTF-8) or 16384 (Singlebyte) characters, then a VARCHAR type may be more appropriate. Beware difference in behaviour: TEXT with no length specifier is equivalent to TEXT(1) |
| TIME | TIME(4) WITHOUT TIME ZONE | - |
| TIMESTAMP | TIMESTAMP | Options: TIMESTAMP(6) WITH TIME ZONE, TIMESTAMP(4) WITHOUT TIME ZONE, TIMESTAMP(9) WITH LOCAL TIME ZONE, The value in brackets is optional and is in the range 0-9 [VW limitation: 0-6] [VW Limitation : WITH TIME ZONE is not supported ]. - |
| TINYINT | TINYINT (I1, INT1) | The SYBASE range for TINYINT values is 0 to 2*8 - 1, or 0 to 255. The range for Ingres TINYINT values is -2**7 to 2**7 - 1, or -127 to 127. Since Ingres has no unsigned data type if your require to hold the range 2**7 to 2**8, or 128 to 255. you will need to use an INTEGER - |
| UNICHAR | NCHAR(1) | - |
| UNICHAR(n) | NCHAR(n) | - |
| UNITEXT | LONG NVARCHAR [V limitation LONG datatypes are not supported - use NVARCHAR[n] | - |
| UNIVARCHAR | NVARCHAR(1) | - |
| UNIVARCHAR(n) | NVARCHAR(n) | - |
| UNSIGNED BIGINT | BIGINT | User will need to validate that only numbers within the range of Ingres BIGINT will be stored. An alternative is to use DECIMAL for large positive integer values greater than 2**31-1
Range: Sybase BIGINT: 0 to 2**64 - 1 or 0 - 18446744073709551615 Ingres BIGINT -263 to 263 - 1, or -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807 |
| UNSIGNED INT | INTEGER | The range for unsigned SMALLINT values is 0 to 2**32 - 1, or 0 to 4294967295. Since Ingres has no unsigned data type if your require to hold the range 2**31 to 2**32-1, or 2147483648 to 4294967295. you will need to use an BIGINT - |
| UNSIGNED INTEGER | INTEGER | see UNSIGNED INT - |
| UNSIGNED SMALLINT | SMALLINT | The range for unsigned SMALLINT values is 0 to 2**16 - 1, or 0 to 65535. Since Ingres has no unsigned data type if your require to hold the range 32768 to 65535 you will need to use an INTEGER - |
| VARBINARY | BYTE VARYING [VW Limitation: Not supported] | - |
| VARBINARY(n) | BYTE VARYING(n) [VW Limitation: Not supported] | - |
| VARCHAR | VARCHAR | - |
| VARCHAR(n) | VARCHAR(n) | - |
[edit]
SQL Server and Ingres Datatypes
| SQL Server datatype | Ingres datatype | Ingres comment |
| BIGINT | BIGINT | - |
| BINARY | BYTE | BYTE(30) If used in a CAST function. |
| BINARY(n) | BYTE(n) | - |
| BIT | INTEGER | - |
| CHAR | CHAR | CHAR(30) If used in a CAST or CONVERT function. |
| CHAR(n) | CHAR(n) | - |
| CURSOR | CURSOR | - |
| DATETIME | DATE | - |
| DECIMAL | DECIMAL(39,0) | - |
| DECIMAL(p,s) | DECIMAL(p,s) | - |
| FLOAT | FLOAT(53) | - |
| FLOAT(n) | FLOAT(p) | - |
| IMAGE | LONG BYTE | - |
| INT | INTEGER (INTEGER4) | - |
| MONEY | DECIMAL(19,4) | - |
| NCHAR | NCHAR | NCHAR(30) If used in a CAST function. |
| NCHAR(n) | NCHAR(n) | - |
| NTEXT | LONG NVARCHAR | - |
| NUMERIC | DECIMAL(39,0) | - |
| NUMERIC(p,s) | DECIMAL(p,s) | - |
| NVARCHAR | NVARCHAR | NVARCHAR(30) If used in a CAST function. |
| NVARCHAR(MAX) | LONG NVARCHAR | - |
| NVARCHAR(n) | NVARCHAR(n) | - |
| REAL | FLOAT4 | - |
| SMALLDATETIME | DATE | - |
| SMALLINT | SMALLINT (INTEGER2) | - |
| SMALLMONEY | DECIMAL(11,4) | - |
| TEXT | LONG VARCHAR | - If TEXT is known to be less than 16000 (UTF-8) or 16384 (Singlebyte) characters, then a VARCHAR type may be more appropriate. Beware difference in behaviour: TEXT with no length specifier is equivalent to TEXT(1) |
| TIMESTAMP | TIMESTAMP | - |
| TINYINT | SMALLINT (INTEGER2) | - |
| VARBINARY | BYTE VARYING | BYTE VARYING(30) If used in a CAST function. |
| VARBINARY(MAX) | LONG BYTE | - |
| VARBINARY(n) | BYTE VARYING(n) | - |
| VARCHAR | VARCHAR | VARCHAR(30) If used in a CAST or CONVERT function. |
| VARCHAR(MAX) | LONG VARCHAR | - |
| VARCHAR(n) | VARCHAR(n) | - |
| UNIQUEIDENTIFIER | BYTE(16) | Ingres has a UUID which can be represented in 16 bytes and is equivalent to the Microsoft UUID |
| SQL_VARIANT | - | No direct Ingres equivalent |
| XML | - | No direct Ingres equivalent - use Varbyte / LOB types. |
[edit]
MySQL (v5.4) and Ingres Datatypes
| MySQL datatype | Ingres datatype | Ingres comment |
| BIGINT | BIGINT (INTEGER8) | - |
| BINARY(M) | BYTE(M) | For MySQL the data type BINARY is similar to 'CHAR CHARACTER SET binary'. M specifies the column length in bytes. |
| BIT[(M)] | BIGINT (INTEGER8) | In MySQL M (1..64) is the number of bits per value. Before MySQL v5.0.3 BIT was interpreted as TINYINT. After v5.0.3 it can be used to store bit-field-values. The notation in MySQL for such bit-field-value is b'value' (e.G. value=10111001). The data type to choose in Ingres depends on M. If M has it's maximum allowed size of 64 Bit (8 Byte) then the Ingres data type BIGINT should be used. The Ingres function BIT('value') can be used to generate a bit string. |
| BLOB[(M)] | LONG BYTE (BLOB) | In MySQL a BLOB has a maximum size of 64KB. M can be used to limit the size of the BLOB. For Ingres the long byte data type has the same characteristics as the byte varying data type, but can accommodate binary data up to 2 GB in length. |
| BOOL | TINYINT (INTEGER1) | The BOOL and BOOLEAN data types in MySQL are synonyms for TINYINT(1). Ingres has no BOOLEAN data type but also TINYINT can be used. The integer value '0' is similar to 'false' and '1' is similar to 'true'. |
| BOOLEAN | TINYINT (INTEGER1) | - |
| CHAR[(M)] | CHAR[(M)] | M is the column length. |
| CHAR[(M)] CHARACTER SET utf8 | NCHAR(M) | To store Unicode data. |
| DATE | ANSIDATE | The default format for MySQL is 'yyyy-mm-dd'. The Ingres data type ANSIDATE also stores only the date information. |
| DATETIME | INGRESDATE | The default format for MySQL is 'yyyy-mm-dd hh:mm:ss'. The Ingres data type INGRESDATE can store the combined date and time as well as time intervals. |
| DEC[(M[,D])] | DECIMAL(M,D) | M is the total number of digits and D is number of digits after the decimal point. D is optional for MySQL. For Ingres it must be set to 0 for no digits to the right of the decimal point. |
| DECIMAL[(M[,D])] | DECIMAL(M,D) | - |
| DOUBLE[(M,D)] | FLOAT (FLOAT8,DOUBLE PRECISION) | - |
| DOUBLE PRECISION[(M,D)] | FLOAT (FLOAT8,DOUBLE PRECISION) | - |
| ENUM('value1','value2',...) | - | - |
| FLOAT[(M,D)] | FLOAT4 (REAL) | In MySQL M is the total number of digits. D is the number following the decimal point. |
| FLOAT(p) | FLOAT(p) | For MySQL and Ingres p represents the precission of a FLOAT in bits. So it is an alias for a FLOAT with a single or double precission dependent on which value p has. |
| LONGBLOB | LONG BYTE (BLOB) | In MySQL a LONGBLOB is a BLOB with a maximum size of 4GB. Ingres can store up to 2GB of data inside a BLOB. |
| LONGTEXT | LONG VARCHAR (CLOB) | In MySQL a LONGTEXT typed value is a like a TEXT typed value but with a size of 4,294,967,295 characters. In Ingres CLOB can accommodate strings up to 2 GB in length. |
| MEDIUMBLOB | LONG BYTE (BLOB) | In MySQL a MEDIUMBLOB is a BLOB with a maximum size of 16MB. |
| MEDIUMINT | INTEGER | - |
| MEDIUMTEXT | LONG VARCHAR (CLOB) | In MySQL a MEDIUMTEXT typed value is like a TEXT typed value but with a size of 16,777,215 characters. In Ingres CLOB can accommodate strings up to 2 GB in length. |
| INT | INTEGER (INTEGER4) | - |
| INTEGER | INTEGER (INTEGER4) | - |
| SERIAL | BIGINT ALWAYS AS IDENTITY | In MySQL SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. For Ingres the clause 'ALWAYS AS IDENITY' is used to create that kind of auto incrementing column. The default start value is '1' and the increment value is '1' as well. |
| SET('value1','value2',...) | - | - |
| SMALLINT | SMALLINT (INTEGER2) | - |
| TEXT[(M)] | LONG VARCHAR (CLOB) | A TEXT value can have a maximum of 65,535 characters in MySQL. The parameter M controls which TEXT type is used. Dependent on M the smallest TEXT type (e.G. TINYTEXT) is used. Ingres can store up to 32000 characters inside a VARCHAR. So if less than 32000 characters should be stored then VARCHAR may be the better choice. |
| TIME | TIME [(time_precision)] | The default format in MySQL is 'hh:mm:ss'. In Ingres the optional parameter named time_precision indicates the number of digits of precision in the fractions of seconds. The default precission is set to 0 if no time precission is supplied. |
| TIMESTAMP | TIMESTAMP [(timestamp_precision)] | The default format for MySQL is 'yyyy-mm-dd hh:mm:ss'. In Ingres the optional parameter named time_precision indicates the number of digits of precision in the fractions of seconds. When no time precision is supplied, the value of time_precision is set to 6 by default. |
| TINYBLOB | BYTE VARYING(255) | In MySQL this is a BLOB with a maximum length of 255 bytes. |
| TINYINT | TINYINT (INTEGER1) | - |
| VARBINARY(M) | BYTE VARYING(M) | For MySQL the data type VARBINARY is similar to 'VARCHAR CHARACTER SET binary'. M specifies the maximum column length in bytes. |
| VARCHAR(M) | VARCHAR(M) | M is the maximum column length. |
| VARCHAR(M) CHARACTER SET utf8 | NVARCHAR(M) | To store Unicode data. |
| YEAR[(N)] | ANSIDATE | In MySQL the year can have a 2 or 4 digit format. Ingres has no YEAR data type, but the information can be stored in an ANSIDATE. |
[edit]
PostgreSQL (v8.4) and Ingres Data Types
| PostgreSQL | Ingres | Ingres Comment |
| ARRAY | - | |
| BIGINT | BIGINT | |
| BIGSERIAL | BIGINT | See: Ingres IDENTITY column specification |
| BIT | INTEGER | Data type depends on the width of the field ranging from INTEGER1 to INTEGER8 for 1 - 64 bits. Fields wider than 64 bits may consider using BYTE(N). |
| BIT VARYING | INTEGER | See: BIT |
| BOOLEAN | TINYINT (INTEGER1) | |
| BOX | - | |
| BYTEA | BYTE(N), BYTE VARYING(N), LONG BYTE | |
| CHARACTER(N) | CHARACTER(N) | |
| CHAR(N) | CHAR(N) | |
| CHARACTER VARYING(N) | CHARACTER VARYING(N) | Beware difference in behaviour: CHARACTER VARYING with no length specifier is equivalent to CHARACTER VARYING(1) |
| CIDR | INTEGER | The internal function ii_ipaddr and the hex function provides some limited functionality |
| CIRCLE | - | |
| COMPOSITE | - | |
| DATE | ANSIDATE | |
| DECIMAL | DECIMAL | |
| DOUBLE PRECISION | DOUBLE PRECISION | |
| ENUM('value1', 'value2',...) | - | |
| INET | INTEGER | See CIDR |
| INTEGER | INTEGER | |
| INTERVAL [ (P) ] | INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND | PostgreSQL supports more interval qualifiers that do not map to the two supported by Ingres. |
| LINE | - | |
| LSEG | - | |
| MACADDR | - | |
| MONEY | MONEY | |
| NUMERIC | NUMERIC | Synonym for DECIMAL |
| PATH (Closed) | - | |
| PATH (Open) | - | |
| POINT | - | |
| POLYGON | - | |
| REAL | REAL | Synonym for FLOAT |
| SERIAL | INTEGER | See: Ingres IDENTITY column specification |
| SMALLINT | SMALLINT (INTEGER2) | |
| TEXT | TEXT | Beware difference in behaviour: TEXT is a synonym for VARCHAR and no length specifier is equivalent to VARCHAR(1) |
| TIME [ (P) [ WITHOUT TIME ZONE ]] | TIME WITHOUT TIME ZONE | |
| TIME [ (P) WITH TIME ZONE] | TIME WITH TIME ZONE | |
| TIMESTAMP [ (P) [ WITHOUT TIME ZONE ]] | TIMESTAMP WITHOUT TIME ZONE | |
| TIMESTAMP [ (P) WITH TIME ZONE] | TIMESTAMP WITH TIME ZONE | |
| TSVECTOR | - | |
| TSQUERY | - | |
| UUID | BYTE(16) | See: SQL Functions for UUID Implementation |
| VARCHAR(N) | VARCHAR(N) | Beware difference in behaviour: VARCHAR with no length specifier is equivalent to VARCHAR(1) |
| XML | - |
[edit]
Function Mappings
[edit]
Oracle to Ingres Function Mappings
The functions in bold are those common in Oracle, MySQL and SQL Server, but not in Ingres.
| Oracle Function | Ingres Function | Oracle Function Description | Comments |
| ASCII(str) | N/A | Returns the decimal representation in the database character set of the first character of str. | Ingres has ASCII function but it returns the character equivalent of str, which is an expression of either character or numeric type. |
| ADD_MONTHS(date, integer) | DATE('date') + DATE('integer months') | Returns the date 'date' plus 'integer' months. | - |
| CONVERT(char, dest_char_set[, source_char_set ]) | N/A | Converts a character string from one character set to another. | No alternative found. |
| COSH(n) | N/A | - | No alternative found. |
| CURRENT_DATE | DATE('today') | - | - |
| INITCAP(STRING_LIT) / NLS_INITCAP(char [, 'nlsparam' ]) | See here for a substitute INITCAP function in Ingres. | Gives uppercase to the title (Title case). 'nlsparam' specifies a sort sequence to be used. | NLS_INITCAP does not have an exact equivalent in Ingres. |
| INSTR | See here for a substitute CHARINDEX function in Ingres. | Used to find the location of a sub string or a character inside a string. | - |
| LAST_DAY(date) | DATE_TRUNC('MONTH', DATE(date) + '1 MONTH') - DATE('1 DAYS') | Returns the date of the last day of the month that contains date. | - |
| LTRIM(char ) | LTRIM(expr) | LTRIM removes all blanks from the left end of char. | - |
| LTRIM(char, set ) | N/A | LTRIM removes from the left end of char all of the characters contained in set. | - |
| NANVL(n2, n1) | N/A | Returns n2 if n2 is a number, else returns n1. | No alternative found. |
| NLS_CHARSET_DECL_LEN NLS_CHARSET_ID NLS_CHARSET_NAME | N/A | Return information about the character set. | - |
| NLS_LOWER(char [, 'nlsparam' ]) NLS_UPPER(char [, 'nlsparam' ]) | LOWER(c1) UPPER(c1) | Converts all characters to lower or upper case respectively. 'nlsparam' is same as for NLS_INITCAP. | The 'nlsparam' parameter does not have an Ingres equivalent. |
| NLSSORT(char [, 'nlsparam' ]) | N/A | Returns the string of bytes used to sort char. | No alternative found. |
| NVL(expr1, expr2) | NVL | If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1. | Added for Ingres 10.0. |
| NVL2(expr1, expr2, expr3) | N/A | If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3. | Added for Ingres 10.0. |
| REGEXP_INSTR (source_char, pattern [, position [, occurrence [, return_option [, match_parameter ] ] ] ] ) | N/A | Extends the functionality of the INSTR function by letting you search a string for a regular expression pattern. | - |
| REGEXP_REPLACE(source_char, pattern [, replace_string [, position [, occurrence [, match_parameter ] ] ] ] ) | N/A | Returns source_char with every occurrence of the regular expression pattern replaced with replace_string. | Similar to REPLACE in Ingres, but REPLACE doesn't match regular expressions, only strings. |
| REGEXP_SUBSTR(source_char, pattern [, position [, occurrence [, match_parameter ] ] ] ] ) | N/A | Searches a string for a regular expression pattern and returns the substring. | Similar to SUBSTRING in Ingres, but SUBSTRING doesn't match regular expressions, only strings. |
| REMAINDER(n2, n1) | MOD(n, b) | Returns the remainder of n2 divided by n1. | - |
| REPLACE(str1, str2 [, str3]) | REPLACE(expr1, expr2, expr3) | Returns str1 with every occurrence of str2 replaced with str3. | str3 is optional in Oracle, but not so in Ingres. In Oracle, if str3 is null, or not provided, all occurances of str2 are removed. |
| RTRIM(char ) | RTRIM(expr) | RTRIM removes all blanks from the right end of char. | - |
| RTRIM(char, set ) | N/A | RTRIM removes from the right end of char all of the characters contained in set. | - |
| SINH(n) | N/A | - | No alternative found. |
| SUBSTR(str, position [, substring_length ]) | SUBSTR(str FROM position [FOR substring_length ]) Note: Use CASE to handle negative values for position and substr_length. | Returns substring starting at position. | - |
| SYSDATE | DATE('now') | Returns the current date and time set for the operating system on which the database resides. | - |
| TANH(n) | N/A | - | No alternative found. |
| TO_DATE(str [, fmt [, 'nlsparam' ] ]) | N/A | Converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype. fmt specifies the format of str. | The DATE() function may be useful in some cases, but not all. |
| TO_NUMBER(expr [, format [, 'nlsparam' ] ]) | DECIMAL(expr [,precision[,scale]]) | Converts expression (of type BINARY_FLOAT, BINARY_DOUBLE, CHAR, VARCHAR2, NCHAR, or NVARCHAR2 in Oracle) to a value of NUMBER datatype. Since NUMBER type corresponds to DECIMAL in Ingres, the DECIMAL function is used, to convert to DECIMAL. | - |
| TRANSLATE | N/A (replace function can be used for partial simulation) | Translate a character string. Used for encryption. | Oracle and DB/2 have different and incompatible semantics for TRANSLATE. DATAllegro submitted code (not presently public) had a function_compat config param and OME functions to emulate one or the other. |
| TREAT | N/A | Changes the declared type of an expression. | - |
| WIDTH_BUCKET(expr, min_value, max_value, num_buckets) | FLOOR(((expr* num_buckets)/(max_value - min_value)) + 1 )
| Construct equiwidth buckets, in which the bucket range is divided into intervals that have identical size. For a given expression, WIDTH_BUCKET returns the bucket number into which the value of this expression would fall after being evaluated. | - |
Retrieved from "http://community.actian.com/wiki/Ingres_Cheatsheets"
