Re: [SQL] finding if a foreign key is in use
Kenneth Gonsalves <[EMAIL PROTECTED]> wrote: > in my app i have a table where the id serves as a foreign key for > one or more other tables. if i want to delete a row in the table, > i currently search the other tables where this table is referenced > to see if the row is in use - and then, if not in use, permit > deletion. You can automate this check by declaring the foreign keys like this: id some_type references other_table(id) on delete no action The "on delete no action" bit means "if you try to delete the row in the referred-to table (where it is the primary key), cause an error. The alternative is "on delete cascade", which means that rows in the referring tables are deleted if the row that they refer to is deleted. > Now if i want the delete button in my app to be disabled whenever > a row that is in use is selected, searching the database every time > would dramatically slow down the app. Basically you do have to do this search. But it won't be too slow if you create an index on the foreign key. I would probably do it like this. Here is the first table: create table T1 ( id integer primary key, xtext ); Here is the second table that refers to it: create table T2 ( id integer references T1.id on delete no action, ytext ); So that the searches can be efficient, we create an index: create index T2_by_id on T2(id); Now I would create a view that adds an extra column to T1, indicating whether any rows in T2 refer to it: create view T1_v as select *, id in (select id from T2) as cannot_delete from T1; But beware! It seems that this particular form DOESN'T use the index we've just created. On the other hand, this very similar one does: create view T1_v as select *, exists (select * from T2 where id=t.id) as cannot_delete from T1 t; Now, when you create your user interface, you can just look at the cannot_delete field to see whether the delete button should be enabled. This should run in logarithmic time. If this isn't fast enough you could instead make cannot_delete a real column and have triggers on changes to T2 that change its value. But I wouldn't resort to this unless you are really desperate. Regards, --Phil. ---(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
Re: FW: [SQL] "=" operator vs. "IS"
My question is why is the form "(anything) = NULL" allowed? Since "(anything) = NULL" is always Null, this cannot be what the coder intended. This is much different when comparing two variables, where the coder may have to handle the cases where the variables are Null. Here the comparison is to a constant. Rick ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: FW: [SQL] "=" operator vs. "IS"
Rich Hall <[EMAIL PROTECTED]> writes: > My question is why is the form > "(anything) = NULL" > allowed? If you think it shouldn't be, you can turn on the transform_null_equals flag. However, past experience has shown that that breaks more things than it fixes. In any case, few people like to depend on such a thoroughly nonstandard behavior ... regards, tom lane ---(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
Re: FW: [SQL] "=" operator vs. "IS"
NULL is handled like "unknow" When you comparing something with an unknown value, you are not able to say if they are equal or not. So the result is also unknown. The result NULL is correct. If you whant to check if somethings is NULL you have to use "(anything) IS NULL" Rich Hall schrieb: My question is why is the form "(anything) = NULL" allowed? Since "(anything) = NULL" is always Null, this cannot be what the coder intended. This is much different when comparing two variables, where the coder may have to handle the cases where the variables are Null. Here the comparison is to a constant. Rick ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Unrecognized node type
Hi: I've to import a DB schema and data from one pgsql to another. The source version is 7.3.4. The destination version is 7.4.2. I've tried to import a dump from pg_dump. Everything goes ok, but at certain point, in the following sentence: CREATE TABLE ca_persona ( ... lssi boolean DEFAULT NULL::boolean, postal boolean DEFAULT NULL::boolean, tele_marketing boolean DEFAULT NULL::boolean, ... ) I get the following error message: "ERROR: unrecognized node type: 656". If I remove those fields, then nothing fails. But I cannot simply change table definition, because then the (J2EE) application doesn't works. The sql must be with NULL::boolean default values. Both source and destination databases have the same encoding (iso-8859-15, "LATIN_9"). Both servers are running on Linux. The same dump have worked correctly in many other machines running Postgres on Windows with Cygwin, with many different versions (7.3.x and 7.4.x). I've tried all the ways of dumping with pg_dump, and using pg_restore in those ways, or loading the sql script from psql, and always fails :-(. Also I've tried with different versions of Postgres, from RPM packages and building from source code, and again always fails :-(. My question is simple: what the h... can I do? :-) Please CC me your replies as I'm not suscribed to this list. Thanks in advance. PS: sorry about my english :-). -- Álvaro Sánchez-Mariscal Arnaiz | Departamento de Comunicación [EMAIL PROTECTED] | javaHispano javaHispano. Una Comunidad al Servicio de Todos www.javaHispano.org ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Unrecognized node type
Alvaro Sanchez-Mariscal <[EMAIL PROTECTED]> writes: > I've tried to import a dump from pg_dump. Everything goes ok, but at > certain point, in the following sentence: > CREATE TABLE ca_persona ( > ... > lssi boolean DEFAULT NULL::boolean, > postal boolean DEFAULT NULL::boolean, > tele_marketing boolean DEFAULT NULL::boolean, > ... > ) > I get the following error message: > "ERROR: unrecognized node type: 656". I couldn't duplicate this: regression=# CREATE TABLE ca_persona ( regression(# lssi boolean DEFAULT NULL::boolean, regression(# postal boolean DEFAULT NULL::boolean, regression(# tele_marketing boolean DEFAULT NULL::boolean); CREATE TABLE regression=# \d ca_persona Table "public.ca_persona" Column | Type | Modifiers +-+--- lssi | boolean | default NULL::boolean postal | boolean | default NULL::boolean tele_marketing | boolean | default NULL::boolean regression=# insert into ca_persona default values; INSERT 154658 1 regression=# I suppose there's some other factor involved in the problem that you didn't show us. > My question is simple: what the h... can I do? :-) As far as finding the bug, you need to provide a complete, self-contained test case. As far as loading the schema, how about just removing the DEFAULT clauses? "default null" is the default behavior anyway. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] UPDATE ... WHERE (subselect on the same table)
I was looking for how can I give an alias for the table being updated (something like UPDATE table_name table_alias SET ...), but the current syntax doesn't allow that. What I need is to: fduch=# SELECT * from test order by typ, name; typ | name | x -+--+--- 1 | bar | 1 | baz | 1 | foo | 2 | baz | 2 | foo | (5 rows) fduch=# UPDATE test SET x = 't' fduch-# where typ = 1 and exists ( fduch(# SELECT 1 from test t2 fduch(# where t2.typ = 2 and t2.name = test.name fduch(# ); UPDATE 2 fduch=# SELECT * from test order by typ, name; typ | name | x -+--+--- 1 | bar | 1 | baz | t 1 | foo | t 2 | baz | 2 | foo | (5 rows) So I have two questions: Q1, cognitive. Why the alias for the updated table is restricted? Is there any reason for that or it's just not implemented? Q2, vital. Can I be sure that the syntax I used here will work correctly, i.e. will the "test.name" always refer the column in outer table, not inner (t2)? Thanks in advance. -- Fduch M. Pravking ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] test1
2,3,4,5 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: FW: [SQL] "=" operator vs. "IS"
Rich Hall wrote: > My question is why is the form > > "(anything) = NULL" > > allowed? > > Since > > "(anything) = NULL" is always Null, this cannot be what the coder > intended. Using that same line of argument, why is 1+1 allowed? The coder clearly knows that it is 2, so why is he writing that? Many queries are generated by automatic tools that definitely intend what they say. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] UPDATE ... WHERE (subselect on the same table)
On Tue, Jun 29, 2004 at 12:49:55PM -0400, Tom Lane wrote: > > So I have two questions: > > Q1, cognitive. Why the alias for the updated table is restricted? > > Because the SQL standard doesn't allow an alias there. We've talked > about allowing one anyway, but no one's gotten around to it. AFAICS > it would only be a marginal notational advantage, not allow you to > express queries you can't express today. > > > Q2, vital. Can I be sure that the syntax I used here will work > > correctly, i.e. will the "test.name" always refer the column in outer > > table, not inner (t2)? > > Yes. The alias *completely* hides the real name of that table > reference, so "test" will never refer to "test t2". As always, perfectly clear, thank you Tom :) I already found in docs on SELECT: When an alias is provided, it completely hides the actual name of the table or table function; /me should RTFM... (repeating hundred times) -- Fduch M. Pravking ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] UPDATE ... WHERE (subselect on the same table)
"Alexander M. Pravking" <[EMAIL PROTECTED]> writes: > fduch=# UPDATE test SET x = 't' > fduch-# where typ = 1 and exists ( > fduch(# SELECT 1 from test t2 > fduch(# where t2.typ = 2 and t2.name = test.name > fduch(# ); > So I have two questions: > Q1, cognitive. Why the alias for the updated table is restricted? Because the SQL standard doesn't allow an alias there. We've talked about allowing one anyway, but no one's gotten around to it. AFAICS it would only be a marginal notational advantage, not allow you to express queries you can't express today. > Q2, vital. Can I be sure that the syntax I used here will work > correctly, i.e. will the "test.name" always refer the column in outer > table, not inner (t2)? Yes. The alias *completely* hides the real name of that table reference, so "test" will never refer to "test t2". regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: FW: [SQL] "=" operator vs. "IS"
On Tue, 29 Jun 2004, Greg Stark wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > > IS TRUE and IS FALSE have a different effect from =true and =false when > > the left hand side is NULL. The former will return false, the latter will > > return NULL. > > No, actually they both return false. For purposes of anyone reading this in the archives I'd meant NULL IS TRUE and NULL IS FALSE return false vs NULL=true and NULL=false returning NULL. ---(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
Re: FW: [SQL] "=" operator vs. "IS"
Rich Hall <[EMAIL PROTECTED]> writes: > "(anything) = NULL" is always Null, this cannot be what the coder intended. I often have such things in my SQL. Consider what happens when you have SQL constructed dynamically. Or more frequently, consider that many drivers still don't use the new binary placeholder syntax and emulate it by putting the parameters directly into the SQL. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: FW: [SQL] "=" operator vs. "IS"
Stephan Szabo <[EMAIL PROTECTED]> writes: > IS TRUE and IS FALSE have a different effect from =true and =false when > the left hand side is NULL. The former will return false, the latter will > return NULL. No, actually they both return false. (But thanks, I didn't even realize they were special this way) -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] OUTER JOIN problem
Hi, I have a problem with LEFT OUTER JOIN, not only in PostgreSQL but in Informix, too. I thought someone can explain the "bug" I am seeing. Let me qoute my psql session, forgive me, it's a bit long: $ LANG=C psql -h localhost -U postgres postgres Welcome to psql 7.3.4-RH, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit postgres=# create table a (i serial, t varchar(40)); JELZÉS: CREATE TABLE will create implicit sequence 'a_i_seq' for SERIAL column 'a.i' CREATE TABLE postgres=# create table b (i serial, t varchar(40)); JELZÉS: CREATE TABLE will create implicit sequence 'b_i_seq' for SERIAL column 'b.i' CREATE TABLE postgres=# create table c (i serial, a integer, b integer); JELZÉS: CREATE TABLE will create implicit sequence 'c_i_seq' for SERIAL column 'c.i' CREATE TABLE ... Insert some records into all three tables ... (Actually table 'b' is not used in the SELECTs, table 'c' would store connections between 'a' and 'b' in the application's broader context.) postgres=# select * from a; i | t ---+--- 1 | 1 2 | 2 3 | 3 4 | 4 (4 rows) postgres=# select * from b; i | t ---+--- 1 | 5 2 | 6 3 | 7 4 | 8 (4 rows) postgres=# select * from c; i | a | b ---+---+--- 1 | 1 | 1 2 | 1 | 2 3 | 1 | 4 4 | 2 | 3 5 | 3 | 1 6 | 3 | 2 7 | 4 | 4 (7 rows) postgres=# select * from a left outer join c on (a.i=c.a) where c.b is null or c.b=1; i | t | i | a | b ---+---+---+---+--- 1 | 1 | 1 | 1 | 1 3 | 3 | 5 | 3 | 1 (2 rows) Let's try something: postgres=# delete from c; DELETE 7 postgres=# insert into c (a,b) values (1,1); INSERT 18490 1 postgres=# select * from a left outer join c on (a.i=c.a) where c.b is null or c.b=1; i | t | i | a | b ---+---+---+---+--- 1 | 1 | 8 | 1 | 1 2 | 2 | | | 3 | 3 | | | 4 | 4 | | | (4 rows) postgres=# insert into c (a,b) values (1,3); INSERT 18491 1 postgres=# select * from a left outer join c on (a.i=c.a) where c.b is null or c.b=1; i | t | i | a | b ---+---+---+---+--- 1 | 1 | 8 | 1 | 1 2 | 2 | | | 3 | 3 | | | 4 | 4 | | | (4 rows) postgres=# insert into c (a,b) values (3,1); INSERT 18492 1 postgres=# select * from a left outer join c on (a.i=c.a) where c.b is null or c.b=1; i | t | i | a | b ---+---++---+--- 1 | 1 | 8 | 1 | 1 2 | 2 || | 3 | 3 | 10 | 3 | 1 4 | 4 || | (4 rows) Now I get the results I want. Let's insert some more data: postgres=# insert into c (a,b) values (2,3); INSERT 18494 1 postgres=# insert into c (a,b) values (3,1); INSERT 18495 1 postgres=# insert into c (a,b) values (4,4); INSERT 18496 1 postgres=# select * from a left outer join c on (a.i=c.a) where c.b is null or c.b=1; i | t | i | a | b ---+---++---+--- 1 | 1 | 8 | 1 | 1 3 | 3 | 10 | 3 | 1 3 | 3 | 13 | 3 | 1 (3 rows) Again I don't get the data I want. I accidentally inserted duplicated data, (a,b)=(3,1) twice. (Working without unique indexes may backfire.) The original dataset at the beginning of my example did not contain duplicated data. I don't know how PostgreSQL works internally but this bug *must* be conforming to some standard if two distinct SQL server products behave (almost) the same. I said almost, I discovered the same annoyance today on an Informix 9.21 running under SCO UNIX on a slightly larger dataset with less than 70 rows. It just left out some arbitrary rows that had NULLs from the right side table (i.e not existing rows). The following (not exactly SQL conform) construct works in Informix 9.21 and always gives me all the rows I wanted and no more: select * from a, outer b where a.i=c.a and (c.b is null or c.b=1); I know I can substitute OUTER JOIN with a UNION of an INNER JOIN and a SELECT from the left table WHERE NOT EXISTS (SELECT from the right- side table WHERE condition). But that's the point of the OUTER JOIN, isn't it? Now can someone tell me whether it is a real bug in BOTH SQL servers? Or is it a conforming behaviour to some part of the SQL standard? Then please, point me where to RTFM? I am not on the list, please Cc: me. Thanks in advance, Zoltán Böszörményi ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] OUTER JOIN problem
On Wed, 23 Jun 2004, Zoltan Boszormenyi wrote: > I don't know how PostgreSQL works internally but this bug *must* be > conforming to some standard if two distinct SQL server products behave > (almost) the same. I said almost, I discovered the same annoyance today > on an Informix 9.21 running under SCO UNIX on a slightly larger dataset > with less than 70 rows. It just left out some arbitrary rows that had > NULLs from the right side table (i.e not existing rows). It's not a bug. The outerness of the join is defined over the join conditions (the ON portion), not the where conditions. In the first case you got rows from the join that matched the join condition like (a.i=2, c.a=1, c.b=2) which the where then filtered out. I think select * from a left outer join c on (a.i=c.a and c.b=1) meets the standard and may give you what you want. ---(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
Re: [SQL] Question about a CIDR based query
George, Um, no, I need an EXPLAIN ANALYZE, not just an EXPLAIN. Thanks. > June_03=# explain select * from tmp where route >>='62.1.1.0/24'; >QUERY PLAN > > Seq Scan on tmp (cost=0.00..606.60 rows=14544 width=33) >Filter: (route >>= '62.1.1.0/24'::cidr) -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Question about a CIDR based query
George, > Um, no, I need an EXPLAIN ANALYZE, not just an EXPLAIN. Thanks. > > > June_03=# explain select * from tmp where route >>='62.1.1.0/24'; > >QUERY PLAN > > > > Seq Scan on tmp (cost=0.00..606.60 rows=14544 width=33) > >Filter: (route >>= '62.1.1.0/24'::cidr) Oh, and also a SELECT VERSION(); would be nice. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
