RE: [SQL] Delete coloumn
Aug 6, 23:35 -0500, Robby Slaughter wrote: > SELECT INTO it. Example: > > CREATE TABLE sample ( > id INTEGER, > data TEXT, > badcolumn DATE ); > > Now to delete the bad column table: > > CREATE TABLE sample_copy ( > id INTEGER, > data TEXT); > > and then copy it all over: > > SELECT id,data INTO sample_copy FROM sample; It is not correct. This statement used to _create_ _new_ table. Correct is: insert into sample_copy select id, data from sample; > > and then you can DROP TABLE sample; > > If you need the original table name, repeat the process of > creating a new table now and copying the data over. > > Hope that helps! > > -Robby Slaughter my best regards, Grigoriy G. Vovk ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Are circular REFERENCES possible ?
Hello ! I have a case where I wanted to do circular REFERENCES, is this impossible ? Just an example where it would be useful : We deliver to the *shops* of our *customers*. We have therefore two tables : - customers (enterprise, financial information, and so on...) - shop (with a name, street, phone number, name of manager) Now, each shop REFERENCES a customer so that we know to which customer belongs a shop. AND, each customer has a DEFAULT shop for deliveries, i.e. most customers only have one shop, or a main shop and many small ones. Therefore a customer should REFERENCES the 'main' or 'default' shop. Which leads to : CREATE TABLE shops ( id_shop SERIAL PRIMARY KEY, id_cust integer REFERENCES customers, ...) CREATE TABLE customers ( id_cust SERIAL PRIMARY KEY, id_defaultshop integer REFERENCES shops, ...) But this doesn't work ! Postgres complains like "ERROR: Relation 'customers' does not exist" when creating 'shops'. Someone told me I should create a third table, ok, but in this case I loose the total control about my logic... Do you have a suggestion ? Thanks a lot in advance ! Denis ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Are circular REFERENCES possible ?
Aug 7, 11:54 +0200, Denis Bucher wrote: > We deliver to the *shops* of our *customers*. > We have therefore two tables : > - customers (enterprise, financial information, and so on...) > - shop (with a name, street, phone number, name of manager) > > Now, each shop REFERENCES a customer so that we know > to which customer belongs a shop. > > AND, each customer has a DEFAULT shop for deliveries, i.e. most > customers only have one shop, or a main shop and many small ones. > Therefore a customer should REFERENCES the 'main' or 'default' shop. > > Which leads to : > > CREATE TABLE shops ( id_shop SERIAL PRIMARY KEY, id_cust integer REFERENCES > customers, ...) > CREATE TABLE customers ( id_cust SERIAL PRIMARY KEY, id_defaultshop integer > REFERENCES shops, ...) > > But this doesn't work ! Postgres complains like "ERROR: Relation > 'customers' does not exist" > when creating 'shops'. > > Someone told me I should create a third table, ok, but in this case I loose > the total > control about my logic... Do you have a suggestion ? We have next conditions: Each customer has many shops; Each shop belong to one and only one customer; So, you have a classic one -> many relation. Next. Shop may have additional attribute - 'default'. You can add column 'default bool' to the table 'shops' but then you should implement something like trigger to check that only one shop of each customer will have 'default' = 't'; Of cause, you can create third table like create table default_shop(id_shop int not null primary key references shops); but it doesn't make a sense, you again must implement something to keep your business logic like trigger which will check that only one shop from belong to a customer there is in the table. So, I think, best solution is add column 'default bool' in the table 'shop' and create a trigger before insert on shop which will check if inserted row has 'default' ='t' are there a row which already has 'default' = 't'. May be you will want a trigger for update. And may be for delete - if will deleted 'default' shop - what we should to do? Better will be create a function for delete shop which will take id_shop which will be deleted and id_shop which will be default as arguments and this function will delete one shop and assign an other as default. my best regards, Grigoriy G. Vovk ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Are circular REFERENCES possible ?
> > >Which leads to : > >CREATE TABLE shops ( id_shop SERIAL PRIMARY KEY, id_cust integer >REFERENCES customers, ...) You can't reference to a table who doesn't exists still. >CREATE TABLE customers ( id_cust SERIAL PRIMARY KEY, id_defaultshop >integer REFERENCES shops, ...) Perhaps you can do it something like that if: 1.- Create the two tables. 2.- Use alter table to add the constraint "references". ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] prob with PERL/Postgres
On Mon, 6 Aug 2001, Tom Lane wrote:
> Thomas Good <[EMAIL PROTECTED]> writes:
> > Kate, he uses a diff module by the same author (Edmund Mergl) but with
> > a very diff syntax. The advantage of the DBI - Kris, if you're
> > interested - is that the syntax is much like ESQL/C and the code is
> > much more portable. For example I use DBI to access both pg and
> > oracle.
>
> DBI is a good alternative, but is unlikely to act much differently as
> far as connection problems go.
You know Thomas, after I build Pg (including 7.1.2) and fire up initdb
there comes a message about starting the db with -D and the location of
the datafiles. Nothing about using -i...it might be good to include,
no?
Cheers,
Tom
SVCMC - Center for Behavioral Health
Thomas Good tomg@ { admin | q8 } .nrnet.org
Programmer/Analyst Phone: 718-354-5528
Residential ServicesMobile: 917-282-7359
/* Die Wahrheit Ist Irgendwo Da Draussen... */
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Simple Insert Problem
[EMAIL PROTECTED] wrote: > Josh, > > Fuck you and the high horse you rode in on. Stop that language immediately! It is not accepted on any of our PostgreSQL mailing lists. > Yes as a matter of fact I did forget the quote marks. Do you think reading > that book will help with my silly syntactical errors? Do you think I did not > pull my hair out prior to posting? Sometimes the obvious just eludes the > smartest of us. All Josh did was to put the IMNSHO appropriate "Read The Fine Manual" into polite words. And I don't see a problem with that. Maybe reading that book might help you. Maybe reading any book about SQL might help you. I guess somehow Josh got the impression you haven't tried that yet. > Oh and P.S. Thanks for the clue. I'm not spinning my wheels on that one > anymore, but I'm sure I'll run up aginst something else soon enough. Feel free to do so and be sure that you'll get the answer (maybe with some tips where to gather that information yourself :-). But stay calm and show the same amount of tolerance you want to get. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Are circular REFERENCES possible ?
Denis, > I have a case where I wanted to do circular REFERENCES, is this > impossible ? It can be done. It's just a bad idea. > We deliver to the *shops* of our *customers*. > We have therefore two tables : > - customers (enterprise, financial information, and so on...) > - shop (with a name, street, phone number, name of manager) > > Now, each shop REFERENCES a customer so that we know > to which customer belongs a shop. > > AND, each customer has a DEFAULT shop for deliveries, i.e. most > customers only have one shop, or a main shop and many small ones. > Therefore a customer should REFERENCES the 'main' or 'default' shop. You can do this by applying the constraints *after* table creation. However, you will forever fight the following problems: 1. You will not be able to add any records to Customers without dropping and re-creating the REFERENCES each time. 2. You will never be able to delete a record from either table due to the circular reference check. 3. Some UPDATES will also fail for the same reason. All of this makes circular references a bad idea; references are meant to be heirarchical. Heck, I got into a real mess when I accidentally set up a circular reference among 5 tables ... took me forever to figure out why INSERTS kept failing. So, an alternate solution to your database structure: 1. Each Customer has one to many Shops (Shops.CustomerID REFERENCES Customers(ID)). 2. Each Shop has a Boolean characteristic Default. 3. Of a Customer's shops, only one can have Default=TRUE at any one time. You use triggers or functions to enforce rule 3. This system works quite well for this purpose ... I was able to put it to use for a much more complex CRM system with main and secondary HR and billing addresses. Your third alternative is to create a JOIN table called Default Shops. However, this does not really provide you any additional referential integrity -- it jsut may suit you if you find triggers intimidating. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Re: Adding an INTERVAL to a variable
> "GC" == Graham Coates <[EMAIL PROTECTED]> writes: GC> SELECT Invoices.InvoiceDate + INTERVAL '41 Days' GC> works fine GC> but when trying to substitute the number of days with a value form a field GC> e.g. GC> SELECT Invoices.InvoiceDate + INTERVAL Acct.AverageDaysToPay 'Days' try SELECT Invoices.InvoiceDate + Acct.AverageDaysToPay::interval -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Why can't I .........
-- do a join between two databases within the same installation. ... where db1.table1.userid = db2.table2.userid -- use labels to name columns in this way ... select col1 userid, col2 name from ... (instead of using AS) -- do an update with full referenced colomns ... update table1 set table1.col1=value ??? Thank you in advance! mo ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Are circular REFERENCES possible ?
Hi Denis, I've just had a similar experience with a 3-way circle. I have members, who belong in regions. Each region had a Regional Liasson Officer who was a member. I got round it by creating the three tables, but missing out one of the references - i.e. the one that links table 1 to table 3 which doesn't exist yet. I then used pg_dump to see how that would re-create the tables. It didn't create any references/foreign keys etc. when it created the tables, but right at the end, aftter the 'copy's and index creations it did a load of CREATE CONSTRACT TRIGGER entries. I edited these to generate the ones that were missing. This was a bit messy, but it meant that I could keep the logic of my data. As stated in some of the other posts, you will have problems updating your data, with inserts. One thing to remember here is that references aren't checked if the reference value is NULL. So, you could add a customer with the default shop as NULL, then add a shop, and then update the customer. I haven't checked this, but I seam to remember reading that if you do it all inside a transaction, the references aren't checked until the transaction is comitted, so you could do something like: begin insert customer insert shop comit Gary On Tuesday 07 August 2001 10:54 am, Denis Bucher wrote: > Hello ! > > I have a case where I wanted to do circular REFERENCES, is this > impossible ? > > Just an example where it would be useful : > > We deliver to the *shops* of our *customers*. > We have therefore two tables : > - customers (enterprise, financial information, and so on...) > - shop (with a name, street, phone number, name of manager) > > Now, each shop REFERENCES a customer so that we know > to which customer belongs a shop. > > AND, each customer has a DEFAULT shop for deliveries, i.e. most > customers only have one shop, or a main shop and many small ones. > Therefore a customer should REFERENCES the 'main' or 'default' shop. > > Which leads to : > > CREATE TABLE shops ( id_shop SERIAL PRIMARY KEY, id_cust integer REFERENCES > customers, ...) > CREATE TABLE customers ( id_cust SERIAL PRIMARY KEY, id_defaultshop integer > REFERENCES shops, ...) > > But this doesn't work ! Postgres complains like "ERROR: Relation > 'customers' does not exist" > when creating 'shops'. > > Someone told me I should create a third table, ok, but in this case I loose > the total > control about my logic... Do you have a suggestion ? > > Thanks a lot in advance ! > > Denis > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Re: Adding an INTERVAL to a variable
Graham, > GC> SELECT Invoices.InvoiceDate + INTERVAL Acct.AverageDaysToPay > 'Days' Actually, all you're missing is some punctuation. Don't skimp on the :: and () ! Plus, you should use explicit CASTs wherever you remember them: SELECT Invoices.InvoiceDate + INTERVAL(CAST(Acct.AverageDaysToPay AS VARCHAR) || ' days'); Will work fine. Here I've explicitly cast the Average Days integer (if it's NUMERIC or FLOAT, you will have to use TO_CHAR()) to varchar, then concatinated it with the word "days". *Then* I convert it to INTERVAL, which will recognize '# days' as a valid expression. IMHO, you've been lucky being able to skip the parens and CASTs so far; get used to using them. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Are circular REFERENCES possible ?
On Tue, 7 Aug 2001, Josh Berkus wrote: > Denis, > > > I have a case where I wanted to do circular REFERENCES, is this > > impossible ? > > It can be done. It's just a bad idea. > > > We deliver to the *shops* of our *customers*. > > We have therefore two tables : > > - customers (enterprise, financial information, and so on...) > > - shop (with a name, street, phone number, name of manager) > > > > Now, each shop REFERENCES a customer so that we know > > to which customer belongs a shop. > > > > AND, each customer has a DEFAULT shop for deliveries, i.e. most > > customers only have one shop, or a main shop and many small ones. > > Therefore a customer should REFERENCES the 'main' or 'default' shop. > > You can do this by applying the constraints *after* table creation. > However, you will forever fight the following problems: > > 1. You will not be able to add any records to Customers without dropping > and re-creating the REFERENCES each time. > 2. You will never be able to delete a record from either table due to > the circular reference check. > 3. Some UPDATES will also fail for the same reason. This is actually not quite true. You need to make the references in a circular relationship deferrable andprobably initially deferred and then add pairs if necessary within one transaction (note: there are some bugs in deferred constraints if you do somewhat wierd things) The other tricks are things like for deletes, you may want to use on delete set null for the the default shop on deliveries (ie, if the shop they use is deleted, they don't have a default shop until someone gives them one). However, I agree that generally circular constraints are painful and its often better to think of another way to hold the relationship. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Why can't I .........
Mounir, > -- do a join between two databases within the same installation. > > ... where db1.table1.userid = db2.table2.userid Because this is not supported on PostgreSQL. There are a number of reasons, and it may never be supported because there are drawbacks to allowing databases to reference each other (mainly security). > -- use labels to name columns in this way > > ... select col1 userid, col2 name from ... (instead of using AS) Because AS is the ANSI SQL 92 standard. And we like standards. > -- do an update with full referenced colomns > > ... update table1 set table1.col1=value Because UPDATES, per the SQL 92 standard, are on one table only. Thus any refenced columns *must* belong to that table, and if so, why name it? -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Are circular REFERENCES possible ?
Josh Berkus wrote: > Denis, > > > I have a case where I wanted to do circular REFERENCES, is this > > impossible ? > > It can be done. It's just a bad idea. I don't see why it is a bad idea to apply the full business model to the database schema. > > Now, each shop REFERENCES a customer so that we know > > to which customer belongs a shop. > > > > AND, each customer has a DEFAULT shop for deliveries, i.e. most > > customers only have one shop, or a main shop and many small ones. > > Therefore a customer should REFERENCES the 'main' or 'default' shop. > > You can do this by applying the constraints *after* table creation. > However, you will forever fight the following problems: > > 1. You will not be able to add any records to Customers without dropping > and re-creating the REFERENCES each time. > 2. You will never be able to delete a record from either table due to > the circular reference check. > 3. Some UPDATES will also fail for the same reason. All of this is wrong. If the constraints are defined to be INITIALLY DEFERRED, all you have to do is to wrap all the changes that put the database into a temporary inconsistent state into a transaction. What is a good idea and strongly advised anyway. DEFERRED means, that the consistency WRT the foreign key constratins will be checked at COMMIT time instead of the actual statement. So if you BEGIN TRANSACTION; INSERT INTO customer ... INSERT INTO shop ... COMMIT TRANSACTION; It'll get you out of the circular problem without dropping and re-creating the constraints. The same applies to updates and deletes generally. Well, if you want to you can specify ON UPDATE CASCADE and ON DELETE CASCADE, so if you delete a shop, the customers referencing it will get deleted automatically too, which might cause other shops referencing them ... > All of this makes circular references a bad idea; references are meant > to be heirarchical. Heck, I got into a real mess when I accidentally > set up a circular reference among 5 tables ... took me forever to figure > out why INSERTS kept failing. Josh, maybe you should buy a newer SQL-bo... :-) Got ya (LOL)! The point is that we based our implementation of foreign keys on the SQL3 specs. DEFERRED is not in SQL-92 AFAIK. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Are circular REFERENCES possible ?
Jan, > All of this is wrong. If the constraints are defined to be > INITIALLY DEFERRED, all you have to do is to wrap all the > changes that put the database into a temporary inconsistent > state into a transaction. What is a good idea and strongly > advised anyway. > > DEFERRED means, that the consistency WRT the foreign key > constratins will be checked at COMMIT time instead of the > actual statement. So if you Hmmm... sounds interesting. Can this be done through functions? I.E., if I put the INSERT/INSERT/UPDATE operation inside a function, does it automatically wait until the function completes before checking constraints? > Josh, maybe you should buy a newer SQL-bo... :-) > > Got ya (LOL)! Zap! Ouch. ;-) > > The point is that we based our implementation of foreign keys > on the SQL3 specs. DEFERRED is not in SQL-92 AFAIK. > Know a good SQL3 book? I bought O'Reilly's SQL In A Nutshell for that, but the book has numerous omissions and a few mistakes. -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Name Alike Challenge
Folks, The "Name Alike" PL/pgSQL function has been posted on Roberto Mello's cookbook: http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=967 This function requires Joe Conway's port of the Metaphone and Levenshtein functions to PostgreSQL, available from /contrib on CVS as well as another site where /contrib stuff is available (anyone know where this is?). Thanks again, Joe! The purpose of the function is to take two First/Last name sets, and depending on the desired degree of "fuzzyness" return whether they are similar or not. I constructed this function with two purposes in mind: preventing duplicates by checking for similar names before accepting INSERTS, and de-duplicating large, messy lists from external souces (such as box office lists). Now, the challenge: 1. Aside from stripping the annoying copyright comments, can anyone name a way in which this function could be made more efficient without changing its results? 2. Can anyone come up with roman-alphabet names which will "defeat" the function? I.e. can you think of similar-typoed names that can't be detected as similar, or patently different names that come up as identical? 3. Can anyone turn this function on its head, and rather than having it accept 2 first/last names and a looseness factor and return TRUE/FALSE, have it accept the two name pairs and return a looseness factor? Go for it! -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Are circular REFERENCES possible ?
Tom Lane wrote: > Jan Wieck <[EMAIL PROTECTED]> writes: > > The point is that we based our implementation of foreign keys > > on the SQL3 specs. DEFERRED is not in SQL-92 AFAIK. > > I still have a concern about this --- sure, you can set up the circular > references using ALTER TABLE, but will pg_dump dump them correctly? AFAIK yes. I'm not sure if it still uses the CONSTRAINT TRIGGER syntax or does it now with ALTER TABLE. But for sure it creates all tables first, then loads the data, then activates the constraints (wouldn't work very well otherwise). Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Are circular REFERENCES possible ?
Jan Wieck <[EMAIL PROTECTED]> writes: > The point is that we based our implementation of foreign keys > on the SQL3 specs. DEFERRED is not in SQL-92 AFAIK. I still have a concern about this --- sure, you can set up the circular references using ALTER TABLE, but will pg_dump dump them correctly? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Why can't I .........
"Josh Berkus" <[EMAIL PROTECTED]> writes: >> -- use labels to name columns in this way >> >> ... select col1 userid, col2 name from ... (instead of using AS) > Because AS is the ANSI SQL 92 standard. And we like standards. Actually, SQL92 says that AS is optional. However, Postgres contains a lot of extensions to SQL92, and some of them produce parse ambiguities if AS is optional. So we require it. This isn't going to change, as it would require ripping out a lot of useful stuff. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Are circular REFERENCES possible ?
Josh Berkus wrote: > Jan, > > > All of this is wrong. If the constraints are defined to be > > INITIALLY DEFERRED, all you have to do is to wrap all the > > changes that put the database into a temporary inconsistent > > state into a transaction. What is a good idea and strongly > > advised anyway. > > > > DEFERRED means, that the consistency WRT the foreign key > > constratins will be checked at COMMIT time instead of the > > actual statement. So if you > > Hmmm... sounds interesting. Can this be done through functions? I.E., > if I put the INSERT/INSERT/UPDATE operation inside a function, does it > automatically wait until the function completes before checking > constraints? Acutally you have fine control over it if you name the constraints explicitly. You can define a constraint just beeing DEFERRABLE but INITIALLY IMMEDIATE. Such a constraint will by default be checked immediately at the time a PK/FK is touched. Inside of your function (as well as inside a transaction from the app-level) you can SET CONSTRAINTS namelist DEFERRED; do all your inserts/updates; SET CONSTRAINTS namelist IMMEDIATE; Setting them to DEFERRED means, that the checks for primary key existence on make of references or the check for non- existence of references on destruction of primary key are delayed, at max until COMMIT. Setting them back to IMMEDIATE runs the checks "for these constraint" immediately, without waiting for the COMMIT, and arranges for all further actions to get checked immediately. Whatever you do and in whatever state you leave the constraints, everything not yet checked will be at COMMIT. Well, the SET CONSTRAINTS has to be put into an EXECUTE in PL/pgSQL, but I think that's not too big of a problem. > > Josh, maybe you should buy a newer SQL-bo... :-) > > > > Got ya (LOL)! > > Zap! Ouch. ;-) Couldn't resist ;-P > > The point is that we based our implementation of foreign keys > > on the SQL3 specs. DEFERRED is not in SQL-92 AFAIK. > > > > Know a good SQL3 book? I bought O'Reilly's SQL In A Nutshell for that, > but the book has numerous omissions and a few mistakes. Unfortunately no - others? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] views and null bothering
On Lun 06 Ago 2001 21:02, Josh Berkus wrote: > Martin, > > > I have a bunch of tables which I give access through a view. The > > problem is > > that in the main table there are columns, that are referenced to > > another > > tables column, that have NULLs. > > In the SELECT inside the view's definition I put the join equality, > > but have > > lots of trouble makeing it put correctly the columns that have NULL > > values. > > You need to use LEFT OUTER JOIN, supported in Postgres 7.1.x. > > See the current postgresql docs, or your favorite ANSI-SQL handbook, for > guidance. Sorry for the late answer, but... THANKS ALOT! It's the first time I use OUTER JOINS, and I think they are great! Thanks Tom for this great feature. Saludos... :-) -- Cualquiera administra un NT. Ese es el problema, que cualquiera administre. - Martin Marques |[EMAIL PROTECTED] Programador, Administrador | Centro de Telematica Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] loop on a rule
As I said before, I am playing around with views and rules, and found out
that I have something wrong in one of the rules. My delete rule says
something like this:
CREATE RULE admin_delete AS ON
DELETE TO admin_view
DO INSTEAD (
DELETE FROM carrera WHERE id_curso=old.id_curso;
DELETE FROM inscripcion WHERE carrera=old.id_curso;
DELETE FROM resol WHERE carr=old.id_curso;
DELETE FROM modalidad WHERE carrera=old.id_curso;
INSERT INTO log_carrera (accion,tabla) VALUES ('D','carrera');
);
Where id_curso is an identifier to which inscripcion.carrera, resol.carr and
modalidad.carrera are REFERENCED to.
When I try to execute a query like this:
DELETE FROM admin_view WHERE id_curso=2;
I get a lost connection and this on the log:
2001-08-07 19:08:40 DEBUG: ProcessQuery
2001-08-07 19:08:40 DEBUG: CommitTransactionCommand
2001-08-07 19:08:43 DEBUG: StartTransactionCommand
2001-08-07 19:08:43 DEBUG: query: DELETE FROM admin_view WHERE id_curso=2;
XLogFlush: rqst 0/0; wrt 0/62940168; flsh 0/62940168
XLogFlush: rqst 0/57764568; wrt 0/62940168; flsh 0/62940168
2001-08-07 19:08:44 DEBUG: ProcessQuery
INSERT @ 0/62940168: prev 0/62940128; xprev 0/0; xid 21593; bkpb 1: Heap -
delete: node 290095/290371; tid 0/2
/usr/local/pgsql/bin/postmaster: reaping dead processes...
/usr/local/pgsql/bin/postmaster: CleanupProc: pid 12353 exited with status 138
Server process (pid 12353) exited with status 138 at Tue Aug 7 19:08:45 2001
Terminating any active server processes...
Server processes were terminated at Tue Aug 7 19:08:45 2001
Reinitializing shared memory and semaphores
invoking IpcMemoryCreate(size=1245184)
2001-08-07 19:08:45 DEBUG: database system was interrupted at 2001-08-07
19:08:13 GMT
2001-08-07 19:08:45 DEBUG: CheckPoint record at (0, 62658256)
2001-08-07 19:08:45 DEBUG: Redo record at (0, 62610224); Undo record at (0,
0); Shutdown FALSE
2001-08-07 19:08:45 DEBUG: NextTransactionId: 21527; NextOid: 313116
2001-08-07 19:08:45 DEBUG: database system was not properly shut down;
automatic recovery in progress...
/usr/local/pgsql/bin/postmaster: ServerLoop:handling reading 6
/usr/local/pgsql/bin/postmaster: ServerLoop:handling reading 6
/usr/local/pgsql/bin/postmaster: ServerLoop:handling reading 6
/usr/local/pgsql/bin/postmaster: ServerLoop:handling reading 6
The Data Base System is starting up
/usr/local/pgsql/bin/postmaster: ServerLoop:handling writing 62
And after that tns of REDOs (which I guess are OK). Does that ServerLoop
belong to REDO or is it part of my problem?
Any ideas?
--
Cualquiera administra un NT.
Ese es el problema, que cualquiera administre.
-
Martin Marques |[EMAIL PROTECTED]
Programador, Administrador | Centro de Telematica
Universidad Nacional
del Litoral
-
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] loop on a rule
=?iso-8859-1?q?Mart=EDn=20Marqu=E9s?= <[EMAIL PROTECTED]> writes:
> As I said before, I am playing around with views and rules, and found out
> that I have something wrong in one of the rules. My delete rule says
> something like this:
> CREATE RULE admin_delete AS ON
> DELETE TO admin_view
> DO INSTEAD (
>DELETE FROM carrera WHERE id_curso=old.id_curso;
>DELETE FROM inscripcion WHERE carrera=old.id_curso;
>DELETE FROM resol WHERE carr=old.id_curso;
>DELETE FROM modalidad WHERE carrera=old.id_curso;
>INSERT INTO log_carrera (accion,tabla) VALUES ('D','carrera');
> );
I think you need the patch for multi-action rules --- see
http://www.ca.postgresql.org/cgi/cvsweb.cgi/pgsql/src/backend/rewrite/rewriteHandler.c
You need version 1.93.2.1, assuming that you're on PG 7.1.2.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] loop on a rule
On Mar 07 Ago 2001 20:53, you wrote:
> =?iso-8859-1?q?Mart=EDn=20Marqu=E9s?= <[EMAIL PROTECTED]> writes:
> > As I said before, I am playing around with views and rules, and found out
> > that I have something wrong in one of the rules. My delete rule says
> > something like this:
> >
> > CREATE RULE admin_delete AS ON
> > DELETE TO admin_view
> > DO INSTEAD (
> >DELETE FROM carrera WHERE id_curso=old.id_curso;
> >DELETE FROM inscripcion WHERE carrera=old.id_curso;
> >DELETE FROM resol WHERE carr=old.id_curso;
> >DELETE FROM modalidad WHERE carrera=old.id_curso;
> >INSERT INTO log_carrera (accion,tabla) VALUES ('D','carrera');
> > );
>
> I think you need the patch for multi-action rules --- see
> http://www.ca.postgresql.org/cgi/cvsweb.cgi/pgsql/src/backend/rewrite/rewri
>teHandler.c You need version 1.93.2.1, assuming that you're on PG 7.1.2.
Yes, I'm on PostgreSQL 7.1.2. I have a resently updated cvs of pgsql (about 3
hours old), but I don't want to put the cvs version on my stable server (I
use it on my WorkStation only).
How can I patch the source of my pgsql 7.1.2 src tree? Any docs are welcomed!
Saludos. and tons of thanks :-)
--
Cualquiera administra un NT.
Ese es el problema, que cualquiera administre.
-
Martin Marques |[EMAIL PROTECTED]
Programador, Administrador | Centro de Telematica
Universidad Nacional
del Litoral
-
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [SQL] Name Alike Challenge
> The "Name Alike" PL/pgSQL function has been posted on Roberto Mello's > cookbook: > > http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=96 7 > > This function requires Joe Conway's port of the Metaphone and > Levenshtein functions to PostgreSQL, available from /contrib on CVS as > well as another site where /contrib stuff is available (anyone know > where this is?). Thanks again, Joe! I sent it as a tgz to Justin for techdocs.postgresql.org. Here's the link: http://techdocs.postgresql.org/source.php#ffuzzy Note that the lastest source in cvs has soundex included, which this tar file does not, but soundex is not needed for Josh's function and it was in previous contribs anyway. -- Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
