Forums - - Toad Data Modeler - Toad World
Danny Torres
Follow / 1 Nov 2016 at 1:33pm / Toad Data Modeler

We are now on Twitter!!! Contact a Technical Support expert today...

Expand content
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

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

321 0 / Create an account to join the discussion.
Danny Torres
Follow / 1 Nov 2016 at 1:32pm / Toad Data Modeler

We are now on Twitter!!! Contact a Technical Support expert today...

Expand content
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

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

305 0 / Create an account to join the discussion.
dheadford
Follow / 22 Dec 2016 at 3:22pm / Toad Data Modeler Beta Program
Latest post by on 6 Jan 2017 at 2:05pm

Splitting DDL into one file per table 6.2.2

Expand content
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...

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? 

Unanswered 97 6 / Create an account to join the discussion.
pmacek
Follow / 4 Jan 2017 at 10:33pm / Toad Data Modeler
Latest post by on 5 Jan 2017 at 2:18pm

PostgreSQL unique without AK and non-null restriction(s)

Expand content
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...

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 4
select 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.

-Pete

Unanswered 90 2 / Create an account to join the discussion.
pmacek
Follow / 2 Jan 2017 at 6:42pm / Toad Data Modeler
Latest post by on 4 Jan 2017 at 3:37pm

Postgres Dictionary Types

Expand content
This question concerns Toad Data Modeler v6.0.3.13; 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...

This question concerns Toad Data Modeler v6.0.3.13; 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;

Unanswered 94 2 / Create an account to join the discussion.