SQL FAQ: How To's

How do you tell what other database objects exist?

 Information about databases, users, and objects in a database are stored in the Illustra system catalogs. Illustra provides DBA functions that make querying the system catalogs easier; for example, ml_dbase() lists all databases on an Illustra server: * select * from ml_dbase(); ------------------------------------------------------------ |database_dba |database_name|database_path|database_release| ------------------------------------------------------------ |jolly |jolly |jolly |2.0.9 | |miro |template1 |template1 |2.0.9 | |jolly |testsaif |testsaif |2.0.9 | |jiangwu |jiangwu |jiangwu |2.0.9 | |jta |jta |jta |2.0.9 | |pbrown |pbrown |pbrown |2.0.9 | ------------------------------------------------------------ 6 rows selected The following error means the dba functions have not been installed: * select * from ml_dbase(); XP0038:Cannot find a function named 'ml_dbase' taking no arguments As the owner of a database, you can install the functions like this: 
    % cd $MI_HOME/examples/dba_funcs
    % make MI_DATABASE=your_dbname all
The Illustra system adminstrator can install these functions in the template1 database so a database will automatically get them at the time it is created.

The DBA functions are fully documented in Chapter 6 of the Illustra System Administrator's Guide. Here is the summary list from that reference: 

     ml_aggs([owner])                    information about aggregates
     ml_cstat(object_name)               column statistics for object
     ml_dbase([dba])                     current databases
     ml_dbase_dr(dbname)                 dump times and logging status
     ml_finfo([owner [,language]])       information about functions
     ml_fsrc(func_name)                  source of sql functions
     ml_priv([grantee])                  privileges
     ml_process([user_name])             server processes
     ml_rules(owner [, table_name])      information about rules
     ml_stat_procs(pid)                  lock status for process
     ml_tables([owner [,kind]])          tables, views and indices
     ml_tcols(object_name)               column information for object
     ml_tindex(table_name)               indices for table
     ml_tstat(object_name)               statistics for objects
     ml_typecols(type_name)              column information for type
     ml_types()                          user-defined types
     ml_user()                           user information

How do you extract just the month from an abstime?

 substring extracts just a portion of a string: * return('now'::abstime) as Now; ------------------------------------- |Now | ------------------------------------- |Mon Apr 18 09:06:10.490753 1994 PDT| ------------------------------------- one row selected * return substring('now'::abstime::text from 5 for 3) as Month; --------------- |Month | --------------- |Apr | --------------- one row selected For more examples, see section 3.5.7.2 in the Illustra User's Guide.

How do you do UNIX-like wildcard searches?

 The UNIX * and ? wildcards are replaced in SQL by % and _, respectively. For example, the following query lists all databases that begin with a 't': * select * from ml_dbase() where database_name like 't%'; ------------------------------------------------------------ |database_dba |database_name|database_path|database_release| ------------------------------------------------------------ |miro |template1 |template1 |2.0.9 | |jolly |testsaif |testsaif |2.0.9 | |jiangwu |tioga |tioga |2.0.9 | ------------------------------------------------------------ 3 rows selected The Illustra RegexEQ() command allows UNIX regular expressions. The following returns the same results as the previous query: * select * from ml_dbase() where RegexEQ(database_name, '^t'); ------------------------------------------------------------ |database_dba |database_name|database_path|database_release| ------------------------------------------------------------ |miro |template1 |template1 |2.0.9 | |jolly |testsaif |testsaif |2.0.9 | |jiangwu |tioga |tioga |2.0.9 | ------------------------------------------------------------ 3 rows selected

How do you remove duplicate entries from a table?

Table foo has duplicate entries for Donald Duck: * select * from foo; ----------------------------- |first_name |last_name | ----------------------------- |Donald |Duck | |Mighty |Mouse | |Donald |Duck | ----------------------------- 3 rows selected This note describes two approaches for removing the duplicates:
  1. Using a temporary table to remove duplicates.
  2. Using oid to remove duplicates.

1. Using a temporary table to remove duplicates.

 The steps for this method are:
  1. select distinct into a temporary table.
    * create table foo2 as select distinct first_name, last_name from foo;
  2. Doublecheck the results. * select * from foo2; ----------------------------- |first_name |last_name | ----------------------------- |Donald |Duck | |Mighty |Mouse | ----------------------------- 2 rows selected
  3. Drop the first table. * drop table foo;
  4. Rename the temporary table to the original table. * alter table foo2 rename to foo;
Don't forget to recreate any indexes and permissions that were on the original table.

2. Using oid to remove duplicates.

 This method describes how to remove duplicate entries from a table by using the oid. Syntax is for Illustra Version 2.

Here is the original state of table foo that has duplicate entries for Donald Duck:

* select oid, * from foo; ------------------------------------------- |oid |first_name |last_name | ------------------------------------------- |202f.2001 |Donald |Duck | |202f.2002 |Mighty |Mouse | |202f.2003 |Donald |Duck | ------------------------------------------- 3 rows selected The goal is to leave the row with the lowest oid in place (202f.2001 in this example) and to delete all rows with an oid higher than this one (202f.2003 in this example). The steps are to:
  1. Find all rows that have an oid greater than the min oid.
    * select f1.oid, f1.first_name, f1.last_name from foo f1 where f1.oid::text > (select min(f2.oid::text) from foo f2 where f1.first_name=f2.first_name and f1.last_name=f2.last_name); ------------------------------------------- |oid |first_name |last_name | ------------------------------------------- |202f.2003 |Donald |Duck | ------------------------------------------- one row selected
  2. Delete all rows that have an oid greater than the min oid.
    Change the select query to actually delete the duplicate row. Do this inside a transaction; and don't commit the change unless the results look correct. * begin transaction; * delete from foo f1 where f1.oid::text > (select min(f2.oid::text) from foo f2 where f1.first_name=f2.first_name and f1.last_name=f2.last_name); one row deleted + select * from foo; ----------------------------- |first_name |last_name | ----------------------------- |Donald |Duck | |Mighty |Mouse | ----------------------------- 2 rows selected + end transaction;
Back to top level FAQ.

Last modified 30-June-94 (Illustra Rev 2.0.12)

Jean Anderson (jta@postgres.berkeley.edu)