EnterpriseDB: DESCRIBE Command
Oracle Tips by
describe command works as you would expect for the most part. The
describe against a table is actually better than in Oracle because it
provides a list of indexes and triggers.
edb=# desc hr.employees
Column | Type |
employee_id | numeric(6,0) | not null
first_name | character varying(20) |
last_name | character varying(25) | not null
email | character varying(25) | not null
phone_number | character varying(20) |
hire_date | timestamp without time zone | not null
job_id | character varying(10) | not null
salary | numeric(8,2) |
commission_pct | numeric(2,2) |
manager_id | numeric(6,0) |
department_id | numeric(4,0) |
"emp_emp_id_pk" PRIMARY KEY, btree (employee_id)
"emp_email_uk" UNIQUE, btree (email)
"emp_department_ix" btree (department_id)
"emp_job_ix" btree (job_id)
"emp_manager_ix" btree (manager_id)
"emp_name_ix" btree (last_name, first_name)
"emp_salary_min" CHECK (salary > 0::numeric)
Describes on views are also nice (I modified the text for
edb=# desc user_tables;
Column | Type | Modifiers
owner | text |
schemaname | text |
table_name | text |
table_space | text |
status | character varying(5) |
WHERE all_tables."owner" = upper("current_user"()::text);
behind when you try to describe a code object:
edb=# select funname, funrettype, funlang
where funname = 'xmlattributes';
funname | funrettype | funlang
xmlattributes | 25 | 17424
edb=# desc xmlattributes
find any relation named "xmlattributes".
only works for objects that the database server considers a
"relation". You can think of a relation as something that can be
joined to something else, like a table or a view.
not a big deal because as I show, you can get much the same
information from alternate sources. It is kind of annoying when you
just want a quick describe of a package though.
Data Type Conversion
particular problem I find in much of the SQL code that I have to
maintain at times is implicit conversions. I stress to all new
programmers, and even to old hands, to always use explicit conversion
and do not rely on assumptions.
not a lot of work to ensure that when you mean to compare a number to
a number or a date to a date that you are in fact doing so. You can
ensure that you are using an explicit conversion by using the
appropriate conversion function, such as to_char, to_date, to_number.
When using such a conversion function it is also important to use the
appropriate format mask.
appropriate data type conversion, and by appropriate I mean explicit,
is an important standard even if you are not planning to port your
application. One reason for this is that it makes the code much
easier to maintain. The second is that it is less error prone and the
third is that you make the important decisions (like the data type)
and let the database optimizer make the data access choices (like
which index to use).
converting an application from Oracle to EnterpriseDB (or vice versa),
it behooves you to use appropriate data type conversion. The problem
becomes more dramatic when you consider the additional data types that
Oracle, a number is the equivalent of an integer. In EnterpriseDB, a
number and an integer are two distinct data types. For example, if we
have function A:
CREATE OR REPLACE PROCEDURE a( p_in_val IN INTEGER )
call that with an anonymous block:
EDB-00302: procedure 'A' not found
we change the anonymous block variable v from NUMBER to INTEGER:
an entirely different result:
Procedure successfully complete
nice when we can change the code so that the error goes away, but that
is not always an option. Sometimes you really mean NUMBER when the
procedure wants an INTEGER. EnterpriseDB provides the CAST function
and related cast syntax (::) for such instances.
anonymous block above could be re-written as:
a(CAST (v AS INTEGER));
other hand, it could be re-written as:.
these blocks produce the same result. Coming from an Oracle
background, I find the CAST syntax much more readable than the ::
place I have really run into difficulties with type conversion has
been with NUMBER and INTEGER. The reason that this is the only place
I have run into issues is because I never rely on implicit type
conversion in my code. I always use TO_CHAR, TO_DATE, etc so I am
always aware of the type of data I should be dealing with.
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.