Function |
Informix 7.30C1 | PostgreSQL 6.5 |
Crash-me information |
crash me safe | | |
crash me version | 1.19a | 1.32 |
crash-me tested on | Windows NT Version 4.0 | Linux 2.2.10 i686 |
ANSI SQL 92 types |
| Informix | PostgreSQL |
Type bit | | |
Type bit(1 arg) | | |
Type bit varying(1 arg) | | |
Type char(1 arg) | | |
Type char varying(1 arg) | | |
Type character(1 arg) | | |
Type character varying(1 arg) | | |
Type date | | |
Type dec(2 arg) | | |
Type decimal(2 arg) | | |
Type double precision | | |
Type float | | |
Type float(1 arg) | | |
Type int | | |
Type integer | | |
Type interval day to second | | |
Type interval year | | |
Type interval year to month | | |
Type national char varying(1 arg) | | |
Type national character(1 arg) | | |
Type national character varying(1 arg) | | |
Type nchar(1 arg) | | |
Type numeric(2 arg) | | |
Type real | | |
Type smallint | | |
Type time | | |
Type timestamp | | |
Type timestamp with time zone | | |
Type varchar(1 arg) | | |
ODBC 3.0 types |
| Informix | PostgreSQL |
Type bigint | | |
Type binary(1 arg) | | |
Type datetime | | |
Type tinyint | | |
Type varbinary(1 arg) | | |
Other types |
| Informix | PostgreSQL |
Type abstime | | |
Type bfile | | |
Type blob | | |
Type bool | | |
Type box | | |
Type byte | | |
Type char(1 arg) binary | | |
Type char16 | | |
Type char2 | | |
Type char4 | | |
Type char8 | | |
Type circle | | |
Type clob | | |
Type datetime | | |
Type double | | |
Type enum(1 arg) | | |
Type float(1 arg) | | |
Type float4 | | |
Type float8 | | |
Type image | | |
Type int(1 arg) zerofill | | |
Type int1 | | |
Type int2 | | |
Type int3 | | |
Type int4 | | |
Type int8 | | |
Type int not null auto_increment | | |
Type int unsigned | | |
Type interval | | |
Type line | | |
Type long | | |
Type long raw | | |
Type long varbinary | | |
Type long varchar(1 arg) | | |
Type lseg | | |
Type mediumint | | |
Type mediumtext | | |
Type middleint | | |
Type mlslabel | | |
Type money | | |
Type nclob | | |
Type number | | |
Type number(1 arg) | | |
Type number(2 arg) | | |
Type nvarchar(2 arg) | | |
Type nvarchar2(1 arg) | | |
Type path | | |
Type point | | |
Type polygon | | |
Type raw(1 arg) | | |
Type reltime | | |
Type rowid | | |
Type serial | | |
Type set(1 arg) | | |
Type smalldatetime | | |
Type smallfloat | | |
Type smallmoney | | |
Type text | | |
Type text(1 arg) | | |
Type timespan | | |
Type uint | | |
Type varchar2(1 arg) | | |
Type year | | |
Constraints |
| Informix | PostgreSQL |
foreign keys | | |
foreign key syntax | | |
ANSI SQL 92 functions |
| Informix | PostgreSQL |
Function +, -, * and / | | |
Function BIT_LENGTH | | |
Function CASE | | |
Function CAST | | |
Function CHAR_LENGTH | | |
Function CHARACTER_LENGTH | | |
Function concatenation with || | | |
Function CURRENT_DATE | | |
Function CURRENT_DATE() | | |
Function CURRENT_TIME | | |
Function CURRENT_TIME() | | |
Function CURRENT_TIMESTAMP | | |
Function CURRENT_TIMESTAMP() | | |
Function LOWER | | |
Function OCTET_LENGTH | | |
Function POSITION | | |
Function SESSION_USER | | |
Function ANSI SQL SUBSTRING | | |
Function SYSTEM_USER | | |
Function TRIM | | |
Function UPPER | | |
ODBC 3.0 functions |
| Informix | PostgreSQL |
Function ABS | | |
Function ACOS | | |
Function ASCII | | |
Function ASIN | | |
Function ATAN | | |
Function ATAN2 | | |
Function CEILING | | |
Function CHAR | | |
Function CONCAT(2 arg) | | |
Function COS | | |
Function COT | | |
Function CURDATE | | |
Function CURTIME | | |
Function DATABASE | | |
Function DAYNAME | | |
Function DAYOFMONTH | | |
Function DAYOFWEEK | | |
Function DAYOFYEAR | | |
Function DEGREES | | |
Function DIFFERENCE() | | |
Function EXP | | |
Function EXTRACT | | |
Function FLOOR | | |
Function ODBC syntax LEFT & RIGHT | | |
Function HOUR | | |
Function ANSI HOUR | | |
Function IFNULL | | |
Function INSERT | | |
Function LCASE | | |
Function LEFT | | |
Function REAL LENGTH | | |
Function ODBC LENGTH | | |
Function LOCATE(2 arg) | | |
Function LOCATE(3 arg) | | |
Function LOG | | |
Function LOG10 | | |
Function LTRIM | | |
Function MINUTE | | |
Function MOD | | |
Function MONTH | | |
Function MONTHNAME | | |
Function NOW | | |
Function PI | | |
Function POWER | | |
Function QUARTER | | |
Function RADIANS | | |
Function RAND | | |
Function REPEAT | | |
Function REPLACE | | |
Function RIGHT | | |
Function ROUND(2 arg) | | |
Function RTRIM | | |
Function SECOND | | |
Function SIGN | | |
Function SIN | | |
Function SOUNDEX | | |
Function SPACE | | |
Function SQRT | | |
Function ODBC SUBSTRING | | |
Function TAN | | |
Function TIMESTAMPADD | | |
Function TIMESTAMPDIFF | | |
Function TRUNCATE | | |
Function UCASE | | |
Function USER | | |
Function USER() | | |
Function WEEK | | |
Function YEAR | | |
Other functions |
| Informix | PostgreSQL |
Function NOT as '!' in SELECT | | |
Function MOD as % | | |
Function & (bitwise and) | | |
Function AND as '&&' | | |
Function <> in SELECT | | |
Function = | | |
Function ADD_MONTHS | | |
Function AND and OR in SELECT | | |
Function ATN2 | | |
Function automatic num->string convert | | |
Function automatic string->num convert | | |
Function BETWEEN in SELECT | | |
Function << and >> (bitwise shifts) | | |
Function BIT_COUNT | | |
Function CEIL | | |
Function CHARINDEX | | |
Function CHR | | |
Function COALESCE | | |
Function concatenation with + | | |
Function CONCAT(list) | | |
Function CONVERT | | |
Function COSH | | |
Function DATE_FORMAT | | |
Function DATEADD | | |
Function DATEDIFF | | |
Function DATENAME | | |
Function DATEPART | | |
Function ELT | | |
Function ENCRYPT | | |
Function FIELD | | |
Function FORMAT | | |
Function FROM_DAYS | | |
Function FROM_UNIXTIME | | |
Function GETDATE | | |
Function GREATEST | | |
Function IF | | |
Function IN on numbers in SELECT | | |
Function IN on strings in SELECT | | |
Function INITCAP | | |
Function LOCATE as INSTR | | |
Function INSTR (Oracle syntax) | | |
Function INSTRB | | |
Function INTERVAL | | |
Function LAST_DAY | | |
Function LAST_INSERT_ID | | |
Function LEAST | | |
Function LENGTHB | | |
Function LIKE in SELECT | | |
Function LIKE ESCAPE in SELECT | | |
Function LN | | |
Function LOG(m,n) | | |
Function LOGN | | |
Function LPAD | | |
Function MAX on numbers | | |
Function MDY | | |
Function SUBSTRING as MID | | |
Function MIN on numbers | | |
Function MONTHS_BETWEEN | | |
Function NOT in SELECT | | |
Function NOT BETWEEN in SELECT | | |
Function NOT LIKE in SELECT | | |
Function ODBC CONVERT | | |
Function PASSWORD | | |
Function PATINDEX | | |
Function PERIOD_ADD | | |
Function PERIOD_DIFF | | |
Function POW | | |
Function RANGE | | |
Function REGEXP in SELECT | | |
Function REPLICATE | | |
Function REVERSE | | |
Function ROOT | | |
Function ROUND(1 arg) | | |
Function RPAD | | |
Function SEC_TO_TIME | | |
Function SINH | | |
Function STR | | |
Function STRCMP | | |
Function STUFF | | |
Function SUBSTRB | | |
Function SUBSTRING_INDEX | | |
Function SYSDATE | | |
Function TANH | | |
Function TIME_TO_SEC | | |
Function TO_DAYS | | |
Function TRANSLATE | | |
Function TRIM; Many char extension | | |
Function TRIM; Substring extension | | |
Function TRUNC | | |
Function UID | | |
Function UNIX_TIMESTAMP | | |
Function USERENV | | |
Function VERSION | | |
Function WEEKDAY | | |
Function | (bitwise or) | | |
Function OR as '||' | | |
Functions in WHERE |
| Informix | PostgreSQL |
Function BETWEEN | | |
Function = ALL | | |
Function = ANY | | |
Function = SOME | | |
Function EXISTS | | |
Function IN on numbers | | |
Function LIKE | | |
Function LIKE ESCAPE | | |
Function MATCH | | |
Function MATCH UNIQUE | | |
Function MATCHES | | |
Function NOT BETWEEN | | |
Function NOT EXISTS | | |
Function NOT LIKE | | |
Function NOT UNIQUE | | |
Function UNIQUE | | |
ANSI SQL 92 group functions |
| Informix | PostgreSQL |
Group function AVG | | |
Group function COUNT (*) | | |
Group function COUNT column name | | |
Group function COUNT DISTINCT column name | | |
Group function MAX on numbers | | |
Group function MAX on strings | | |
Group function MIN on numbers | | |
Group function MIN on strings | | |
Group function SUM | | |
Other group functions |
| Informix | PostgreSQL |
Group function BIT_AND | | |
Group function BIT_OR | | |
Group function COUNT(DISTINCT expr,expr,...) | | |
Group function STD | | |
Group function STDDEV | | |
Group function VARIANCE | | |
Function use |
| Informix | PostgreSQL |
update of column= -column | | |
column LIKE column | | |
LIKE on numbers | | |
Calculate 1--1 | | |
Is 'a' || NULL = NULL | | |
Is 1+NULL = NULL | | |
Order by and group by |
| Informix | PostgreSQL |
number of columns in group by | | +64 |
number of columns in order by | | +64 |
Group by | | |
Group by alias | | |
Test nulls in group by | | |
Group by position | | |
Having | | |
Having on alias | | |
Having with group function | | |
Order by | | |
Order by alias | | |
Order by function | | |
Order by position | | |
Order by DESC is remembered | | |
Join methods |
| Informix | PostgreSQL |
cross join (same as from a,b) | | |
full outer join | | |
tables in join | +64 | +64 |
left outer join | | |
left outer join using | | |
DELETE FROM table1,table2... | | |
Update with many tables | | |
natural join | | |
natural left outer join | | |
left outer join odbc style | | |
recursive subqueries | 10 | 25 |
right outer join | | |
Update with sub select | | |
ANSI SQL simple joins | | |
subqueries | | |
String handling |
| Informix | PostgreSQL |
case insensitive compare | | |
String functions on date columns | | |
Double '' as ' in strings | | |
ignore end space in compare | | |
insert empty string | | |
Multiple line strings | | |
Allows ' and " as string markers | | |
Remembers end space in char() | | |
Remembers end space in varchar() | | |
Select constants | | |
constant string size in SELECT | 256 | 16373 |
constant string size in where | 32452 | 8104 |
Quoting |
| Informix | PostgreSQL |
Name limits |
| Informix | PostgreSQL |
Alter table rename table | | |
different namespace for index | | |
column name length | 18 | 227 |
index name length | 18 | +512 |
select alias name length | 18 | +512 |
table alias name length | 18 | +512 |
table name length | 18 | +512 |
case independent table names | | |
Index limits |
| Informix | PostgreSQL |
create index | | |
drop index | | |
index in create table | | |
index on column part (extension) | | |
max index | +64 | +64 |
index length | 255 | 8104 |
max index part length | 255 | 8104 |
index parts | 15 | 7 |
index varchar part length | 254 | 8100 |
unique indexes | +64 | +64 |
Tables without primary key | | |
null in index | | |
null in unique | | |
primary key in create table | | |
unique in create table | | |
unique null in create | | |
Type limits |
| Informix | PostgreSQL |
atomic updates | | |
binary items (0x41) | | |
char are space filled | | |
Supports YY-MM-DD dates | | |
Supports 0000-00-00 dates | | |
mixing of integer and float in expression | | |
max char() size | 256 | 8104 |
max text or blob size | | 8104 |
max varchar() size | 255 | 8100 |
Expression limits |
| Informix | PostgreSQL |
big expressions | 1 | 1 |
OR and AND in WHERE | 1204 | 7 |
simple expressions | 16372 | 8187 |
stacked expressions | 47 | 3331 |
SELECT with LIMIT | | |
SELECT with LIMIT #,# | | |
Comments |
| Informix | PostgreSQL |
# as comment | | |
-- as comment | | |
/* */ as comment | | |
Other features |
| Informix | PostgreSQL |
Alter table add column | | |
Alter table add many columns | | |
Alter table alter column | | |
Alter table change column | | |
Alter table drop column | | |
Alter table modify column | | |
Column alias | | |
Compute | | |
default value for column | | |
default value function for column | | |
create table if not exists | | |
create table from select | | |
drop table if exists | | |
allows end ';' | | |
except | | |
except all | | |
insert INTO ... SELECT ... | | |
INSERT with set syntax | | |
intersect | | |
intersect all | | |
lock table | | |
Value of logical operation (1=1) | not supported | 1 |
minus | | |
many tables to drop table | | |
Value lists in INSERT | | |
Correct rounding when storing float values | | |
Type for row id | | oid |
SELECT without FROM | | |
Table alias | | |
Select table_name.* | | |
temporary tables | | |
transactions | | |
union | | |
union all | | |
views | | |
Other limits |
| Informix | PostgreSQL |
Simultaneous connections | 269 | 32 |
Columns in table | 994 | 1248 |
max table row length (without blobs) | 32356 | 7949 |
table row length with nulls (without blobs) | 32356 | 7949 |
query size | 32766 | 16382 |