Log-In to post
Do you have a quick support question and short of time? Contact us Via Twitter @QuestExperts and we will take care of you. #Jointheinnovation #WeareQuest
When I generate DDL and check the "Split Output File" I get scripts for each table and one called other. The other has the key and index statements. Is there a way to have these included with the table scripts?
In PostgreSQL, and in fact any "SQL standards"-complaint DBMS, one can insist on uniqueness for non-null values and at the same time allow repetition on null values.
For instance, the following runs just fine:
create table uniqueness ( col int unique);insert into uniqueness(col) values(null);insert into uniqueness(col) values(1);insert into uniqueness(col) values(null);insert into uniqueness(col) values(2);
-- returns 4select count(*) from uniqueness
However, within TDM there doesn't appear to be a way to effect the above when designing a table. Selecting the "Unique" checkbox but unselecting the "Not Null" doesn't appear to be allowed.
From the PG docs (my emphasis in bold):
In general, a unique constraint is violated if there is more than one row in the table where the values of all of the columns included in the constraint are equal. However, two null values are never considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard, but we have heard that other SQL databases might not follow this rule. So be careful when developing applications that are intended to be portable.
This question concerns Toad Data Modeler v220.127.116.11; database is PostgreSQL.
I'd like to define a new Dictionary Type (i.e., Postgres DOMAIN) in terms of an existing Dictionary Type. However, that isn't supported in TDM, and the drop-down list of available types (whether intrinsic or user-defined) excludes Dictionary Types. Strictly speaking, this is a bug, as Postgres allows that (with the expected acyclic dependency caveats). In my particular case, it's forcing me to compromise my design.
For example, the following runs just fine:
CREATE DOMAIN my_base_type AS int;ALTER DOMAIN my_base_type OWNER TO postgres;CREATE DOMAIN my_sub_type AS my_base_type;ALTER DOMAIN my_base_type OWNER TO postgres;
One utility of this is feature is the ability to attach additional CONSTRAINTs to the subtype. Another is the ability to use a subtype as a way to overload functions of the same name and achieve embellished or different behavior. For example:
CREATE OR REPLACE FUNCTION mult(i my_base_type) RETURNS my_base_type AS $$ BEGIN RETURN i * 2; END;$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION mult(i my_sub_type) RETURNS my_sub_type AS $$ BEGIN RETURN i * 3; END;$$ LANGUAGE plpgsql;