headers
Michael Adler | 6 Apr 2002 17:27

Re: [GENERAL] inherited columns as foreign keys WAS "no subject"


On Fri, 5 Apr 2002, Stephan Szabo wrote:

> > I get a "referential integrity violation", but the referenced key *does*
> > exist in the referenced table.
> >
> > I don't think that it's important, but the table "object" is inherited by
> > other tables.
>
> In fact it may certainly be... References constraints do not inherit
> to children currently.  The constraint selects from only the named table
> (do a select * from ONLY object where id=...) and I'd guess that the row
> is actually in one of the children.

I think the solution is to create a unique index on the child
table so that you can reference the inherited column.

In that "gee, it would be nice" category of suggestions, I'd like to see
more documentation on inheritance, it's limitations and suggested
workarounds. This has probably all been said before..

The interactive docs on inheritance suggest one workaround for inherited
columns as foreign keys: use a "CHECK" constraint with a custom function
instead of REFERENCES constraint. Without much investigation, it seems
like a better idea to create a unique index on the child table and
REFERENCE that. This seems simpler to setup and affords more integrity
features. I haven't really tried it, though.

The other limitation that I didn't find in the main docs is that child
tables don't inherit triggers. You have to add a trigger for each child
(Continue reading)

Permalink | Reply | 4 comments |
headers
Command Prompt, Inc. | 6 Apr 2002 19:39
Favicon

Re: Who's using PostgreSQL?


I know that the USDA, FDA, and University of California use it.

On Sat, 6 Apr 2002, Devrim GUNDUZ wrote:

>
> Hello,
>
> I will be giving a 15-day conference in Turkey on PostgreSQL. I need some
> information:
>
> Who is using PostgreSQL? I want to introduce those firms/sites in my
> presentations, and convince people to use PostgreSQL by giving those
> examples.
>
> I know some sites in Turkey. Also, got some names from
> http://www.pgsql.com/user_gallery/projects.php?sort=name
>
> I need more "famous" ones, and 2-3 lines of short information about the
> site.
>
> I'll be happy if you mail me information about your systems.
>
> Regards and best wishes.
>
>

--

-- 
--
by way of pgsql-general <at> commandprompt.com
(Continue reading)

Permalink | Reply | 0 comments |
headers
Thomas T. Thai | 6 Apr 2002 21:08

ALTER TABLE & NOT NULL

How do you alter a column so that it uses NOT NULL? I tried:

alter table auth_users alter email set default not null;

ERROR:  Column "email" is of type character varying but default expression
is of type boolean
        You will need to rewrite or cast the expression

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Permalink | Reply | 4 comments |
headers
Joe Conway | 6 Apr 2002 21:28
Gravatar

Re: ALTER TABLE & NOT NULL

Thomas T. Thai wrote:
> How do you alter a column so that it uses NOT NULL? I tried:
> 
> alter table auth_users alter email set default not null;
> 
> ERROR:  Column "email" is of type character varying but default expression
> is of type boolean
>         You will need to rewrite or cast the expression
> 

I think you have to add a table constraint to do that. Something like:

ALTER TABLE auth_users ADD CONSTRAINT auth_users_email CHECK (email is 
not null);

Joe

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org
Permalink | Reply | 3 comments |
headers
Thomas T. Thai | 6 Apr 2002 22:43

Re: ALTER TABLE & NOT NULL

On Sat, 6 Apr 2002, Joe Conway wrote:

> Thomas T. Thai wrote:
> > How do you alter a column so that it uses NOT NULL? I tried:
> >
> > alter table auth_users alter email set default not null;
> >
> > ERROR:  Column "email" is of type character varying but default expression
> > is of type boolean
> >         You will need to rewrite or cast the expression
> >
>
> I think you have to add a table constraint to do that. Something like:
>
> ALTER TABLE auth_users ADD CONSTRAINT auth_users_email CHECK (email is
> not null);

is this also what happens at the time of table creation when one specifies
that a column is to be NOT NULL?

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html
Permalink | Reply | 2 comments |
headers
Joe Conway | 6 Apr 2002 23:30
Gravatar

Re: ALTER TABLE & NOT NULL

Thomas T. Thai wrote:
>>>
>>
>>I think you have to add a table constraint to do that. Something like:
>>
>>ALTER TABLE auth_users ADD CONSTRAINT auth_users_email CHECK (email is
>>not null);
> 
> 
> is this also what happens at the time of table creation when one specifies
> that a column is to be NOT NULL?

Not quite the same, but the net effect is. To illustrate, I created 2 
tables foobar1 and foobar2. foobar2 had the constraint added after 
creation, foobar1 was created with the f1 column set to not null. Here's 
what it looks like from psql:

test=# \d foobar1
        Table "foobar1"
  Column |  Type   | Modifiers
--------+---------+-----------
  f1     | integer | not null

test=# \d foobar2
        Table "foobar2"
  Column |  Type   | Modifiers
--------+---------+-----------
  f1     | integer |
Check constraints: "foobar2_f1" (f1 IS NOT NULL)
(Continue reading)

Permalink | Reply | 1 comment |
headers
Tom Lane | 7 Apr 2002 03:52
Picon

Re: ALTER TABLE & NOT NULL

Joe Conway <mail <at> joeconway.com> writes:
>> is this also what happens at the time of table creation when one specifies
>> that a column is to be NOT NULL?

> Not quite the same, but the net effect is.

The built-in NOT NULL constraint is more efficient than doing it with a
general-purpose CHECK expression (or should be, anyway, but I've not
tried to measure the performance difference).

> I think I remember some discussion around making a way to add a not null 
> modifier, but I don't believe it can be done today.

Chris Kings-Lynne recently contributed code to support ALTER COLUMN SET
NOT NULL and DROP NOT NULL, which will enable turning the built-in
constraint on and off.  It will be in 7.3.

> (Except, maybe by hacking the system tables directly - it seems to work 
> for me, but that's always a risky proposition.

That's all that the ALTER code does ;-)

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo <at> postgresql.org
Permalink | Reply | 0 comments |
headers
Alvaro Herrera | 7 Apr 2002 06:24

Re: Porting from MySql - meta data issues

En Fri, 05 Apr 2002 17:13:40 +0100
Geoff Caplan <geoff <at> advantae.com> escribió:

> Thanks folks - useful responses.
> 
> In the longer run, though, it would be good if there were a more accessible 
> set of functions or queries (such as the MySql SHOW queries), which made 
> this kind of meta-data more accessible.

You can also use the pg_tables view, for example, and the system
catalogs for the rest (pg_database, pg_attribute).

Anyway, there's little use for SHOW queries, as you can always poke into
the system catalogs. That's something you can't do in MySQL, and is the
reason for the SHOW hack.

--

-- 
Alvaro Herrera (<alvherre[a]atentus.com>)
"Everybody understands Mickey Mouse. Few understand Hermann Hesse.
Hardly anybody understands Einstein. And nobody understands Emperor Norton."

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Permalink | Reply | 2 comments |
headers
Thomas T. Thai | 7 Apr 2002 06:48

DATE datatype = NULL?

can a column of type DATE be null?

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo <at> postgresql.org so that your
message can get through to the mailing list cleanly
Permalink | Reply | 0 comments |
headers
Tom Lane | 7 Apr 2002 07:49
Picon

Re: Porting from MySql - meta data issues

Alvaro Herrera <alvherre <at> atentus.com> writes:
> You can also use the pg_tables view, for example, and the system
> catalogs for the rest (pg_database, pg_attribute).

> Anyway, there's little use for SHOW queries, as you can always poke into
> the system catalogs. That's something you can't do in MySQL, and is the
> reason for the SHOW hack.

If we actually implement anything new in this line, it will presumably
be the SQL-standard-compliant INFORMATION_SCHEMA views.  Although as
far as I can see, those have nothing to recommend them except standards
compliance :-(.  "SELECT * FROM INFORMATION_SCHEMA.TABLES" doesn't seem
to have any great advantage over either "\d" or whatever the equivalent
MySQL SHOW command is...

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org
Permalink | Reply | 1 comment |

Gmane