[GENERAL] Re: [SQL] two tables - foreign keys referring to each other...
You have to use ALTER TABLE to add the constraint to one of the tables. Deferred refers to the checking of the constraint itself, not really to the check to see if the table is there. On Wed, 21 Feb 2001, Chris Czeyka wrote: > Hey to all, > > I got two tables, linked to each other. How can I tell the first CREATE TABLE > (institute_t) to wait to check the foreign key for the second table??? just > like "hold on a little bit... you'll receive your admin_t" :-) ? I thoght > DEFERRABLE, DEFERRED and transaction with BEGIN/COMMIT take care of this. > > ..or generally: how do you create two crosslinked foreign keyed tables? > > hopefully an easy problem for the real professionals! > > > -> here we go > BEGIN; -- begin table transaction -- Only Postgresql > CREATE TABLE institute_t ( > nameVARCHAR(48) PRIMARY KEY, > street VARCHAR(48) NOT NULL, > zip VARCHAR(16), > townVARCHAR(32) NOT NULL, > country CHAR(2) NOT NULL, /* country codes ISO-3166*/ > phone VARCHAR(32) NOT NULL, > fax VARCHAR(32), > admin VARCHAR(16) REFERENCES admin_t > ON UPDATE CASCADE > ON DELETE SET NULL > DEFERRABLE > INITIALLY DEFERRED > ); > > CREATE TABLE admin_t ( > login VARCHAR(16) PRIMARY KEY, > passwordVARCHAR(16) NOT NULL, > email VARCHAR(32) NOT NULL, > real_name VARCHAR(32) NOT NULL, > street VARCHAR(48) NOT NULL, > zip VARCHAR(16), > townVARCHAR(32) NOT NULL, > country CHAR(2) NOT NULL, /* country codes -- refer to > ISO-3166*/ > phone VARCHAR(32) NOT NULL, > fax VARCHAR(32), > access INTEGER NOT NULL, > institute VARCHAR(48) REFERENCES institute_t > ON UPDATE CASCADE > ON DELETE SET NULL > DEFERRABLE > INITIALLY DEFERRED > ); > COMMIT; > > > of course I get the ERROR, that admin_t doesn't exist. So? help the stupid! > pls! > > best greets, > Chris >
Re: [SQL] two tables - foreign keys referring to each other...
ThX Kirby, ThX Martijn, as you can see I'm new to the SQL- and database world. My policy for this project is to FORCE an admin to be in an institute. If one admin behaves bad for the database project the institute is responsible for her/him. This institute would be represented by a special admin (therefore the link back). Anyway, I see that crosslinking is really a little bit tricky... I will do this check in the application level (Java) and see, if this is easier and even necessary. For now I might use only admin(fk)->institute. This is necessary to trace back responsibility. as beginner I appreciate good hints, cheers, Chris > > IMHO, you should consider not having the admin table have a link to the > institute table. If you want to find the institute a particular admin > is connected with, find that look in the institutes table. The astute > reader will note the advice is symmetric, you can not have a link from > the institute to the admin. If you don't want to have dangling admin's > you might be able to get a trigger/stored procedure to do it for you > (Note:I done triggers in Oracle, never in PostGres so take that with a > grain of salt. I would be shocked if you couldn't do it with a Trigger > under PostGres. I believe it us commonly done with long objects as a > matter of fact). > > Do what you like, and I hope this helps. > > Kirby >
Re: [GENERAL] two tables - foreign keys referring to each other...
On Wed, Feb 21, 2001 at 12:30:51PM +0900, Chris Czeyka wrote: [big snip] > of course I get the ERROR, that admin_t doesn't exist. So? help the stupid! > pls! I think the general idea is to create the tables first without the constraints and then do ALTER TABLE ADD CONSTRAINT afterwards. HTH, Martijn
[SQL] two tables - foreign keys referring to each other...
Hey to all, I got two tables, linked to each other. How can I tell the first CREATE TABLE (institute_t) to wait to check the foreign key for the second table??? just like "hold on a little bit... you'll receive your admin_t" :-) ? I thoght DEFERRABLE, DEFERRED and transaction with BEGIN/COMMIT take care of this. ..or generally: how do you create two crosslinked foreign keyed tables? hopefully an easy problem for the real professionals! -> here we go BEGIN; -- begin table transaction -- Only Postgresql CREATE TABLE institute_t ( nameVARCHAR(48) PRIMARY KEY, street VARCHAR(48) NOT NULL, zip VARCHAR(16), townVARCHAR(32) NOT NULL, country CHAR(2) NOT NULL, /* country codes ISO-3166*/ phone VARCHAR(32) NOT NULL, fax VARCHAR(32), admin VARCHAR(16) REFERENCES admin_t ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED ); CREATE TABLE admin_t ( login VARCHAR(16) PRIMARY KEY, passwordVARCHAR(16) NOT NULL, email VARCHAR(32) NOT NULL, real_name VARCHAR(32) NOT NULL, street VARCHAR(48) NOT NULL, zip VARCHAR(16), townVARCHAR(32) NOT NULL, country CHAR(2) NOT NULL, /* country codes -- refer to ISO-3166*/ phone VARCHAR(32) NOT NULL, fax VARCHAR(32), access INTEGER NOT NULL, institute VARCHAR(48) REFERENCES institute_t ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED ); COMMIT; of course I get the ERROR, that admin_t doesn't exist. So? help the stupid! pls! best greets, Chris
RE: [SQL] handling of database size exceeding physical disk space
Thanks ! I'm using JDBC to insert into the tables. Would it throw an SQLException in such a situation ? Rini --- "Diehl, Jeffrey" <[EMAIL PROTECTED]> wrote: > I happen to know this very well... It handles > things very gracefully as far > as I can tell. I complains that it can't extend the > table and bails out of > the transaction. I just wish it didn't happen so > often... > > Mike Diehl, > Network Monitoring Tool Devl. > 284-3137 > [EMAIL PROTECTED] > > > > -Original Message- > > From: Rini Dutta [mailto:[EMAIL PROTECTED]] > > Sent: February 20, 2001 9:35 AM > > To: [EMAIL PROTECTED]; > [EMAIL PROTECTED] > > Cc: [EMAIL PROTECTED] > > Subject: [SQL] handling of database size exceeding > physical disk space > > > > > > Hi, > > > > Does anyone know how postgres/ postmaster handles > the > > situation where the physical hard disk space is > full ? > > Does it crash / corrupt the database, or does it > > cleanly exit with appopriate message so that > relevant > > tables can be pruned (by the user) to free up disk > > space and get it working again ? > > > > Thanks, > > Rini > > > > __ > > Do You Yahoo!? > > Get personalized email addresses from Yahoo! Mail > - only $35 > > a year! http://personal.mail.yahoo.com/ > > > __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
Re: [SQL] Huh? Mysterious Function Error
Josh Berkus <[EMAIL PROTECTED]> writes:
> ERROR: unexpected SELECT query in exec_stmt_execsql()
plpgsql doesn't think you should execute bare SELECTs, only SELECT INTO.
On reflection this seems an overly anal-retentive restriction, since
there are cases where one might execute a SELECT for its side effects
alone ("SELECT setval()" comes to mind).
Jan, do you think there's a good case for refusing plain SELECTs?
regards, tom lane
Re: [SQL] Huh? Mysterious Function Error
Tom, Stephan,
> Josh Berkus <[EMAIL PROTECTED]> writes:
> > ERROR: unexpected SELECT query in exec_stmt_execsql()
>
> plpgsql doesn't think you should execute bare SELECTs, only SELECT INTO.
That would explain it. I have a nested function call in the form:
SELECT fn_remove_lock(record_id, user_id);
Because I didn't care what the function returned and SELECT is the
simplest form. Easily fixed.
>
> On reflection this seems an overly anal-retentive restriction, since
> there are cases where one might execute a SELECT for its side effects
> alone ("SELECT setval()" comes to mind).
> Jan, do you think there's a good case for refusing plain SELECTs?
I can think of one, myself. In some systems (MS SQL Server), all plain
selects are returned by stored procedures as query results; thus a
stored procedure is able to return multiple rowsets. In other systems,
the single returned rowset is given by the last palin SELECT in the
procedure.
Thus, until PGSQL *does* support stored procedures, refusing plain
SELECTs may be a good way to remind procedure-writers that we do not
have the ability to return rowsets.
For stuff like my procedure, or Tom's example, one can always designate
a dummy variable with:
dummy_variable := setval('some_sq',321);
Which should have the same effect.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
Re: [SQL] Huh? Mysterious Function Error
Does the function have any SELECTs that aren't SELECT INTOs? On Tue, 20 Feb 2001, Josh Berkus wrote: > Tom, Stephan, Jan, Etc. > > Can you help me with this one? I can't figure out what is meant by > this error message: > > I have a long, complicated PL/pgSQL function called > fn_modify_candidates(INT4,INT4,INT4). > > When I call: > SELECT fn_modify_candidate(1004,5,278417036) > > I get: > ERROR: unexpected SELECT query in exec_stmt_execsql() > > Help?
[SQL] Huh? Mysterious Function Error
Tom, Stephan, Jan, Etc. Can you help me with this one? I can't figure out what is meant by this error message: I have a long, complicated PL/pgSQL function called fn_modify_candidates(INT4,INT4,INT4). When I call: SELECT fn_modify_candidate(1004,5,278417036) I get: ERROR: unexpected SELECT query in exec_stmt_execsql() Help? -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Re: [SQL] now() with microsecond granulity needed
Radoslaw Stachowiak <[EMAIL PROTECTED]> writes: > using now() to init TIMESTAMP fields I got resolution of one second. How > can I define DEFAULT in column (TIMESTAMP type) to get higher > time-resolution (TIMESTAMP supports microseconds). You could make a variant of now() that relies on gettimeofday() instead of time(). Note that you probably won't get microsecond precision in any case... regards, tom lane
[SQL] handling of database size exceeding physical disk space
Hi, Does anyone know how postgres/ postmaster handles the situation where the physical hard disk space is full ? Does it crash / corrupt the database, or does it cleanly exit with appopriate message so that relevant tables can be pruned (by the user) to free up disk space and get it working again ? Thanks, Rini __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
[SQL] now() with microsecond granulity needed
Hello! using now() to init TIMESTAMP fields I got resolution of one second. How can I define DEFAULT in column (TIMESTAMP type) to get higher time-resolution (TIMESTAMP supports microseconds). Please CC the answer to me. -- radoslaw.stachowiak.http://alter.pl/
