Information Functions

CHARSET

CHARSET(expr)

This function returns the character set of expr.

Parameters:

expr – Target expression to get the character set.

Return type:

STRING

SELECT CHARSET('abc');

'iso88591'

SELECT CHARSET(_utf8'abc');

'utf8'

SET NAMES utf8;
SELECT CHARSET('abc');

'utf8'

COERCIBILITY

COERCIBILITY(expr)

This function returns the collation coercibility level of expr. The collation coercibility level determines which collation or charset should be used when each column(expression) has different collation or charset. For more details, please see Collation Coercibility.

Parameters:

expr – Target expression to get the collation coercibility level.

Return type:

INT

SELECT COERCIBILITY(USER());

7

SELECT COERCIBILITY(_utf8'abc');

10

COLLATION

COLLATION(expr)

This function returns the collation of expr.

Parameters:

expr – Target expression to get the collation.

Return type:

STRING

SELECT COLLATION('abc');

'iso88591_bin'

SELECT COLLATION(_utf8'abc');

'utf8_bin'

CURRENT_USER, USER

CURRENT_USER
USER

CURRENT_USER and USER are pseudo-columns and can be used interchangeably. They return the user name that is currently logged in to the database as a string.

Please note that SYSTEM_USER() and USER() functions return the user name with a host name.

Return type:

STRING

--selecting the current user on the session
SELECT USER;

   CURRENT_USER
======================
  'PUBLIC'

SELECT USER(), CURRENT_USER;

   user()                CURRENT_USER
============================================
  'PUBLIC@cdbs006.cub'  'PUBLIC'

--selecting all users of the current database from the system table
SELECT name, id, password FROM db_user;

  name                           id  password
=========================================================
  'DBA'                        NULL  NULL
  'PUBLIC'                     NULL  NULL
  'SELECT_ONLY_USER'           NULL  db_password
  'ALMOST_DBA_USER'            NULL  db_password
  'SELECT_ONLY_USER2'          NULL  NULL

DATABASE, SCHEMA

DATABASE()
SCHEMA()

The functions DATABASE and SCHEMA are used interchangeably. They return the name of currently-connected database as a VARCHAR type.

Return type:

STRING

SELECT DATABASE(), SCHEMA();

   database()            schema()
============================================
  'demodb'              'demodb'

DBTIMEZONE

DBTIMEZONE()

Prints out a timezone of database server (offset or region name) as a string. (e.g. ‘-05:00’, or ‘Europe/Vienna’).

SELECT DBTIMEZONE();

  dbtimezone
======================
  'Asia/Seoul'

See also

SESSIONTIMEZONE(), FROM_TZ(), NEW_TIME(), TZ_OFFSET()

DEFAULT

DEFAULT(column_name)
DEFAULT

The DEFAULT and the DEFAULT function returns a default value defined for a column. If a default value is not specified for the column, NULL or an error is output. DEFAULT has no parameter, however, the DEFAULT function uses the column name as the input parameter. DEFAULT can be used for the input data of the INSERT statement and the SET clause of the UPDATE statement and the DEFAULT function can be used anywhere.

If any of constraints is not defined or the UNIQUE constraint is defined for the column where a default value is not defined, NULL is returned. If NOT NULL or PRIMARY KEY constraint is defined, an error is returned.

CREATE TABLE info_tbl(id INT DEFAULT 0, name VARCHAR);
INSERT INTO info_tbl VALUES (1,'a'),(2,'b'),(NULL,'c');

SELECT id, DEFAULT(id) FROM info_tbl;

           id   default(id)
=============================
            1             0
            2             0
         NULL             0

UPDATE info_tbl SET id = DEFAULT WHERE id IS NULL;
DELETE FROM info_tbl WHERE id = DEFAULT(id);
INSERT INTO info_tbl VALUES (DEFAULT,'d');

Note

In version lower than CUBRID 9.0, the value at the time of CREATE TABLE has been saved when the value of the DATE, DATETIME, TIME, TIMESTAMP column has been specified as SYS_DATE, SYS_DATETIME, SYS_TIME, SYS_TIMESTAMP while creating a table. Therefore, to enter the value at the time of data INSERT in version lower than CUBRID 9.0, the function should be entered to the VALUES clause of the INSERT syntax.

DISK_SIZE

DISK_SIZE(expr)

This function returns the size in bytes required to store the value of expr after evaluation. Main usage is to get necessary size for storing values in database heap file.

Parameters:

expr – Target expression to get the size.

Return type:

INTEGER

SELECT DISK_SIZE('abc'), DISK_SIZE(1);

   disk_size('abc')   disk_size(1)
==================================
                  7              4

The size depends on the actual content of value, string compression is also taken into account:

CREATE TABLE t1(s1 VARCHAR(10), s2 VARCHAR(300), c1 CHAR(10), c2 CHAR(300));
INSERT INTO t1 VALUES(REPEAT('a', 10), REPEAT('b', 300), REPEAT('c', 10), REPEAT('d', 300));
INSERT INTO t1 VALUES('a', 'b', 'c', 'd');
SELECT DISK_SIZE(s1), DISK_SIZE(s2), DISK_SIZE(c1), DISK_SIZE(c2) FROM t1;

   disk_size(s1)   disk_size(s2)   disk_size(c1)   disk_size(c2)
================================================================
              12              24              10             300
               4               4              10             300

INDEX_CARDINALITY

INDEX_CARDINALITY(table, index, key_pos)

The INDEX_CARDINALITY function returns the index cardinality in a table. The index cardinality is the number of unique values defining the index. The index cardinality can be applied even to the partial key of the multiple column index and displays the number of the unique value for the partial key by specifying the column location with the third parameter. Note that this value is an approximate value.

If you want the updated result from this function, you should run UPDATE STATISTICS statement.

Parameters:

  • table – Table name

  • index – Index name that exists in the table

  • key_pos –

    Partial key location. It key_pos starts from 0 and has a range that is smaller than the number of columns consisting of keys; that is, the key_pos of the first column is 0. For the single column index, it is 0. It can be one of the following types.

    • Character string that can be converted to a numeric type.

    • Numeric type that can be converted to an integer type. The FLOAT or the DOUBLE types will be the value converted by the ROUND function.

Return type:

INT

The return value is 0 or a positive integer and if any of the input parameters is NULL, NULL is returned. If tables or indexes that are input parameters are not found, or key_pos is out of range, NULL is returned.

CREATE TABLE t1( i1 INTEGER ,
i2 INTEGER not null,
i3 INTEGER unique,
s1 VARCHAR(10),
s2 VARCHAR(10),
s3 VARCHAR(10) UNIQUE);

CREATE INDEX i_t1_i1 ON t1(i1 DESC);
CREATE INDEX i_t1_s1 ON t1(s1(7));
CREATE INDEX i_t1_i1_s1 on t1(i1,s1);
CREATE UNIQUE INDEX i_t1_i2_s2 ON t1(i2,s2);

INSERT INTO t1 VALUES (1,1,1,'abc','abc','abc');
INSERT INTO t1 VALUES (2,2,2,'zabc','zabc','zabc');
INSERT INTO t1 VALUES (2,3,3,'+abc','+abc','+abc');

UPDATE STATISTICS ON t1;
SELECT INDEX_CARDINALITY('t1','i_t1_i1_s1',0);

   index_cardinality('t1', 'i_t1_i1_s1', 0)
===========================================
                                          2

SELECT INDEX_CARDINALITY('t1','i_t1_i1_s1',1);

   index_cardinality('t1', 'i_t1_i1_s1', 1)
===========================================
                                          3

SELECT INDEX_CARDINALITY('t1','i_t1_i1_s1',2);

   index_cardinality('t1', 'i_t1_i1_s1', 2)
===========================================
                                       NULL

SELECT INDEX_CARDINALITY('t123','i_t1_i1_s1',1);

  index_cardinality('t123', 'i_t1_i1_s1', 1)
============================================
                                       NULL

INET_ATON

INET_ATON(ip_string)

The INET_ATON function receives the string of an IPv4 address and returns a number. When an IP address string such as ‘a.b.c.d’ is entered, the function returns “a * 256 ^ 3 + b * 256 ^ 2 + c * 256 + d”. The return type is BIGINT.

Parameters:

ip_string – IPv4 address string

Return type:

BIGINT

In the following example, 192.168.0.10 is calculated as “192 * 256 ^ 3 + 168 * 256 ^ 2 + 0 * 256 + 10”.

SELECT INET_ATON('192.168.0.10');

   inet_aton('192.168.0.10')
============================
                  3232235530

INET_NTOA

INET_NTOA(expr)

The INET_NTOA function receives a number and returns an IPv4 address string. The return type is VARCHAR.

Parameters:

expr – Numeric expression

Return type:

STRING

SELECT INET_NTOA(3232235530);

   inet_ntoa(3232235530)
======================
  '192.168.0.10'

LAST_INSERT_ID

LAST_INSERT_ID()

The LAST_INSERT_ID function returns the value that has been most recently inserted to the AUTO_INCREMENT column by a single INSERT statement.

Return type:

BIGINT

The value returned by the LAST_INSERT_ID function has the following characteristics.

  • The latest LAST_INSERT_ID value which was INSERTed successfully will be maintained. If it fails to INSERT, there is no change for LAST_INSERT_ID() value, but AUTO_INCREMENT value is internally increased. Therefore, LAST_INSERT_ID() value after the next INSERT statement’s success reflects the internally increased AUTO_INCREMENT value.

    CREATE TABLE tbl(a INT PRIMARY KEY AUTO_INCREMENT, b INT UNIQUE);
INSERT INTO tbl VALUES (null, 1);
INSERT INTO tbl VALUES (null, 1);

    ERROR: Operation would have caused one or more unique constraint violations.

    INSERT INTO tbl VALUES (null, 1);

    ERROR: Operation would have caused one or more unique constraint violations.

    SELECT LAST_INSERT_ID();

    1

-- In 2008 R4.x or before, above value is 3.

    INSERT INTO tbl VALUES (null, 2);
SELECT LAST_INSERT_ID();

    4

  • In the Multiple-rows INSERT statement(INSERT INTO tbl VALUES (), (), …, ()), LAST_INSERT_ID() returns the firstly inserted AUTO_INCREMENT value. In other words, from the second row, there is no change on LAST_INSERT_ID() value even if the next rows are inserted.

    INSERT INTO tbl VALUES (null, 11), (null, 12), (null, 13);
SELECT LAST_INSERT_ID();

    5

    INSERT INTO tbl VALUES (null, 21);
SELECT LAST_INSERT_ID();

    8

  • If INSERT statement succeeds to execute, LAST_INSERT_ID () value is not recovered to its previous value even if the transaction is rolled back.

    -- csql> ;autocommit off
CREATE TABLE tbl2(a INT PRIMARY KEY AUTO_INCREMENT, b INT UNIQUE);
INSERT INTO tbl2 VALUES (null, 1);
COMMIT;

SELECT LAST_INSERT_ID();

    1

    INSERT INTO tbl2 VALUES (null, 2);
INSERT INTO tbl2 VALUES (null, 3);

ROLLBACK;

SELECT LAST_INSERT_ID();

    3

  • LAST_INSERT_ID() value used from the inside of a trigger cannot be identified from the outside of the trigger.

  • LAST_INSERT_IDis independently kept by a session of each application.

CREATE TABLE ss (id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, text VARCHAR(32));
INSERT INTO ss VALUES (NULL, 'cubrid');
SELECT LAST_INSERT_ID ();

     last_insert_id()
=======================
                     1

INSERT INTO ss VALUES (NULL, 'database'), (NULL, 'manager');
SELECT LAST_INSERT_ID ();

     last_insert_id()
=======================
                     2

CREATE TABLE tbl (id INT AUTO_INCREMENT);
INSERT INTO tbl values (500), (NULL), (NULL);
SELECT LAST_INSERT_ID();

     last_insert_id()
=======================
                     1

INSERT INTO tbl VALUES (500), (NULL), (NULL);
SELECT LAST_INSERT_ID();

     last_insert_id()
=======================
                     3

SELECT * FROM tbl;

                    id
=======================
                   500
                     1
                     2
                   500
                     3
                     4

LIST_DBS

LIST_DBS()

The LIST_DBS function outputs the list of all databases in the directory file($CUBRID_DATABASES/databases.txt), separated by blanks.

Return type:

STRING

SELECT LIST_DBS();

  list_dbs()
======================
  'testdb demodb'

ROW_COUNT

ROW_COUNT()

The ROW_COUNT function returns the number of rows updated (UPDATE, INSERT, DELETE, REPLACE) by the previous statement.

ROW_COUNT returns 1 for each inserted row and 2 for each updated row for INSERT ON DUPLICATE KEY UPDATE statement. It returns the sum of number of deleted and inserted rows for REPLACE statement.

Statements triggered by trigger will not affect the ROW_COUNT for the statement.

Return type:

INT

CREATE TABLE rc (i int);
INSERT INTO rc VALUES (1),(2),(3),(4),(5),(6),(7);
SELECT ROW_COUNT();

   row_count()
===============
              7

UPDATE rc SET i = 0 WHERE i >  3;
SELECT ROW_COUNT();

   row_count()
===============
              4

DELETE FROM rc WHERE i = 0;
SELECT ROW_COUNT();

   row_count()
===============
              4

SESSIONTIMEZONE

SESSIONTIMEZONE()

Prints out a timezone of session (offset or region name) as a string. (e.g. ‘-05:00’, or ‘Europe/Vienna’).

SELECT SESSIONTIMEZONE();

  sessiontimezone
======================
  'Asia/Seoul'

See also

DBTIMEZONE(), FROM_TZ(), NEW_TIME(), TZ_OFFSET()

USER, SYSTEM_USER

USER()
SYSTEM_USER()

The functions USER and SYSTEM_USER are identical and they return the user name together with the host name.

The USER and CURRENT_USER pseudo-columns return the user names who has logged on to the current database as character strings.

Return type:

STRING

--selecting the current user on the session
SELECT SYSTEM_USER ();

   user()
======================
  'PUBLIC@cubrid_host'

SELECT USER(), CURRENT_USER;

   user()                CURRENT_USER
============================================
  'PUBLIC@cubrid_host'  'PUBLIC'

--selecting all users of the current database from the system table
SELECT name, id, password FROM db_user;

  name                           id  password
=========================================================
  'DBA'                        NULL  NULL
  'PUBLIC'                     NULL  NULL
  'SELECT_ONLY_USER'           NULL  db_password
  'ALMOST_DBA_USER'            NULL  db_password
  'SELECT_ONLY_USER2'          NULL  NULL

VERSION

VERSION()

The VERSION function returns the version character string representing the CUBRID server version.

Return type:

STRING

SELECT VERSION();

   version()
=====================
  '9.1.0.0203'