[SQL] Constraints...
Does anyone know how I can make a constraint on a key to enforce a 1 to n relationship where n>0? I've invented an example to show the sort of constraint I need: CREATE TABLE permissions ( id int4, userid int4, perm int4, primary key (id,userid) ); CREATE TABLE objects ( id int4, perm int4 NOT NULL, data text ); INSERT INTO permissions (id,userid,perm) VALUES (1,1001,7); INSERT INTO permissions (id,userid,perm) VALUES (1,1002,6); INSERT INTO permissions (id,userid,perm) VALUES (1,1003,4); INSERT INTO permissions (id,userid,perm) VALUES (2,1001,0); So I want to allow something like: INSERT INTO objects (id,perm,data) VALUES (1,1,'everyone can read'); INSERT INTO objects (id,perm,data) VALUES (2,1,'everyone can read'); INSERT INTO objects (id,perm,data) VALUES (3,2,'nobody can read'); But disallow an insert like: INSERT INTO objects (id,perm,data) VALUES (,1,'bad perm example'); Is this possible? -Michael _ http://fastmail.ca/ - Fast Free Web Email for Canadians ---(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] multi-table join, final table is outer join count ...
Okay, not sure best way to try and describe this ... have multiple tables,
of a form like:
table a
gid int
data text
table b
gid int
data text
table c
gid int
data text
table d
gid int
data text
I want to return:
a.gid,a.data,b.data,c.data,count(d.data)
where
a.gid = b.gid = c.gid = d.gid
*but* I want count(d.data) to return zero *if* there are no records in
table d ...
essentially, gid has to exist in tables a,b,c but not d ...
So, ignoring table d, i'd have:
SELECT a.gid,a.data,b.data,c.data
FROM tablea a, tableb b, tablec c
WHERE a.gid = b.gid
AND b.gid = c.gid;
How do I add 'tabled d' to the mix?
Thanks ...
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: multi-table join, final table is outer join count ...
Got it after a bit of fiddling ... actually, not bad code ...
SELECT distinct s.gid, s.created, count(i.title) AS images
FROM status s LEFT JOIN images i ON (s.gid = i.gid AND i.active),
personal_data pd, relationship_wanted rw
WHERE s.active AND s.status != 0
AND (s.gid = pd.gid AND pd.gender = 0)
AND (s.gid = rw.gid AND rw.gender = 0 )
GROUP BY s.gid,s.created
ORDER BY images desc;
The part that had confused me was the whole 'ON' part ... once I clued in
that that is essentially a WHERE, it actually made sense ...
On Sat, 12 May 2001, The Hermit Hacker wrote:
>
> Okay, not sure best way to try and describe this ... have multiple tables,
> of a form like:
>
> table a
> gid int
> data text
>
> table b
> gid int
> data text
>
> table c
> gid int
> data text
>
> table d
> gid int
> data text
>
> I want to return:
>
> a.gid,a.data,b.data,c.data,count(d.data)
>
> where
>
> a.gid = b.gid = c.gid = d.gid
>
> *but* I want count(d.data) to return zero *if* there are no records in
> table d ...
>
> essentially, gid has to exist in tables a,b,c but not d ...
>
> So, ignoring table d, i'd have:
>
> SELECT a.gid,a.data,b.data,c.data
> FROM tablea a, tableb b, tablec c
> WHERE a.gid = b.gid
>AND b.gid = c.gid;
>
> How do I add 'tabled d' to the mix?
>
> Thanks ...
>
> Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
> Systems Administrator @ hub.org
> primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
>
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
---(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] Constraints...
Yes. It depends on what exactly you want for the update/delete cases on permissions. I believe that in any cases you can use the check function that's used by the fk implementation to do the insert/update check on objects. If you don't mind update/deletes on permission failing if the row being modified has any references (Even if there are others) you can use the fk functions for that too - you'll have to make the triggers manually). If you want update/deletes on permission to only fail (cascade, whatever) when the *last* row matching a referencing row is deleted (rather than any update/delete causing it) you'll probably need to make a function like those in ri_triggers.c that makes sure there isn't another row, which will involve locking the other matching rows in permissions I think if you went that route.) On Sat, 12 May 2001, Michael Richards wrote: > Does anyone know how I can make a constraint on a key to enforce a 1 > to n relationship where n>0? > > I've invented an example to show the sort of constraint I need: > CREATE TABLE permissions ( > id int4, > userid int4, > perm int4, > primary key (id,userid) > ); > CREATE TABLE objects ( > id int4, > perm int4 NOT NULL, > data text > ); > > INSERT INTO permissions (id,userid,perm) VALUES (1,1001,7); > INSERT INTO permissions (id,userid,perm) VALUES (1,1002,6); > INSERT INTO permissions (id,userid,perm) VALUES (1,1003,4); > INSERT INTO permissions (id,userid,perm) VALUES (2,1001,0); > > So I want to allow something like: > INSERT INTO objects (id,perm,data) VALUES (1,1,'everyone can read'); > INSERT INTO objects (id,perm,data) VALUES (2,1,'everyone can read'); > INSERT INTO objects (id,perm,data) VALUES (3,2,'nobody can read'); > > But disallow an insert like: > INSERT INTO objects (id,perm,data) VALUES (,1,'bad perm example'); ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Re: multi-table join, final table is outer join count ...
The Hermit Hacker <[EMAIL PROTECTED]> writes: > SELECT distinct s.gid, s.created, count(i.title) AS images > FROM status s LEFT JOIN images i ON (s.gid = i.gid AND i.active), > personal_data pd, relationship_wanted rw >WHERE s.active AND s.status != 0 > AND (s.gid = pd.gid AND pd.gender = 0) > AND (s.gid = rw.gid AND rw.gender = 0 ) > GROUP BY s.gid,s.created > ORDER BY images desc; > The part that had confused me was the whole 'ON' part ... once I clued in > that that is essentially a WHERE, it actually made sense ... Right, but there's some fine points here. When you're dealing with INNER JOINs, ON (or its variant USING) is exactly equivalent to WHERE. Write whichever you like. When you're dealing with OUTER JOINs, ON is *not* quite the same as WHERE, because it determines which rows are considered to "match" and thus which rows will be extended with NULLs. Let's take a simplified version of your above example. If you wrote FROM status s LEFT JOIN images i ON (s.gid = i.gid) WHERE i.active AND ...other conditions... then this would produce the regular inner join of status and images where gid matches, *plus* a row for each unmatched status row (extended with NULLs for the images columns). This collection of rows would then pass through your WHERE clauses, and whichever ones pass all the WHERE conditions get into the result. But, when you write FROM status s LEFT JOIN images i ON (s.gid = i.gid AND i.active) WHERE ...other conditions... then you get the inner join of status and images on gid, minus the rows where i.active is false, plus a null-extended row for each status row that does not have a matching *active* image row. So the set of rows that comes out of the join is different: there could be more null-extended rows in this case than in the other one. In particular, you could see rows having i.active=NULL in the final result, which'd never happen if you had put i.active into the WHERE clause instead of the ON clause. Bottom line: what you put in the ON part should just be the clauses that determine whether you think there's a match between the two tables. The WHERE part is additional restrictions that limit what you want to see, but don't affect the semantics of whether there's a match. In your above example, I'm not sure whether it's right to put i.active in the ON part or in WHERE. It depends on what you want to happen for status rows that match only inactive images, and whether you consider them different from status rows that match no images at all. 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
[SQL] can't get rid of unnesesary SORT step in explain plan for hash join
Hello. I need to make some sql-statement to be executed as fast as possible. :) My database consists of: 1) table of categories having 1'000 rows, 2) table of manufacturers having 1'000 rows, 3) table of resellers having 1'000 rows, 4) table of products having 1'000'000 rows. In the products table there are aproximately 1'000 products per category; as well as per manufacturer and per reseller. Here are these tables: postgres=# create table categories ( c_id int4, c_name varchar(32) ); CREATE postgres=# create table manufacturers ( m_id int4, m_name varchar(32) ); CREATE postgres=# create table resellers ( r_id int4, r_name varchar(32) ); CREATE postgres=# create table products ( p_id int4, c_id int4, m_id int4, r_id int4, p_name varchar(32) ); CREATE I want to get category identifiers (c_id) for those categories which have products manufactured by manufacturer with identifier (m_id) equal to 123 and reselled by companies which names (r_name) start with 'a'. In order to make this query faster I created two indicies: postgres=# create index products_mcr on products ( m_id, c_id, r_id ); CREATE postgres=# create index resellers_n on resellers ( r_name ); CREATE I tried several sql-statements which can be used for my purpose, because they have different execution plans. They are listed below according with their plans. Each #a query is different from the corresponding # one in that it has fictive "order by m_id, c_id" clause. This clause does not change the result of a query, but in some way says the optimiser not to use "Sort" step. It works for the 2a (SubPlan), 3a (SubPlan) and 4a (Nested Loop) queries, but not for the 1a (Hash Join). And I want to use this query, because practice results show, that it is the fastest one. At my computer these queries took: 1a) 1sec, 2a) 16sec, 3a) 3sec, 4a) 3sec. So, my question is: how can I get rid of this unnesesary "Sort" step in the execution plan for hash join? Thanks in advance. P.S.: Excuse me for bad english. +++ +++ 1) select distinct c_id from products, ( select r_id from resellers where r_name like 'a%' ) as temp where m_id = 123 and products.r_id = temp.r_id; Unique (cost=16.83..16.83 rows=1 width=12) -> Sort (cost=16.83..16.83 rows=1 width=12) -> Hash Join (cost=8.16..16.82 rows=1 width=12) -> Index Scan using products_mcr on products (cost=0.00..8.14 rows=10 width=8) -> Hash (cost=8.14..8.14 rows=10 width=4) -> Index Scan using resellers_n on resellers (cost=0.00..8.14 rows=10 width=4) 1a) select distinct c_id, m_id from products, ( select r_id from resellers where r_name like 'a%' ) as temp where m_id = 123 and products.r_id = temp.r_id order by m_id, c_id; Unique (cost=16.83..16.83 rows=1 width=12) -> Sort (cost=16.83..16.83 rows=1 width=12) -> Hash Join (cost=8.16..16.82 rows=1 width=12) -> Index Scan using products_mcr on products (cost=0.00..8.14 rows=10 width=8) -> Hash (cost=8.14..8.14 rows=10 width=4) -> Index Scan using resellers_n on resellers (cost=0.00..8.14 rows=10 width=4) 2) select distinct c_id from products where m_id = 123 and r_id in ( select r_id from resellers where r_name like 'a%' ); Unique (cost=89.68..89.71 rows=1 width=4) -> Sort (cost=89.68..89.68 rows=10 width=4) -> Index Scan using products_mcr on products (cost=0.00..89.52 rows=10 width=4) SubPlan -> Materialize (cost=8.14..8.14 rows=10 width=4) -> Index Scan using resellers_n on resellers (cost=0.00..8.14 rows=10 width=4) 2a) select distinct c_id, m_id from products where m_id = 123 and r_id in ( select r_id from resellers where r_name like 'a%' ) order by m_id, c_id; Unique (cost=0.00..89.57 rows=1 width=8) -> Index Scan using products_mcr on products (cost=0.00..89.52 rows=10 width=8) SubPlan -> Materialize (cost=8.14..8.14 rows=10 width=4) -> Index Scan using resellers_n on resellers (cost=0.00..8.14 rows=10 width=4) 3) select distinct c_id from products where m_id = 123 and exists ( select r_id from resellers where r_name like 'a%' and r_id = products.r_id ); Unique (cost=89.91..89.93 rows=1 width=4) -> Sort (cost=89.91..89.91 rows=10 width=4) -> Index Scan using products_mcr on products (cost=0.00..89.74 rows=10 width=4) SubPlan -> Index Scan using resellers_n on resellers (cost=0.00..8.16 rows=1 width=4) 3a) select distinct c_id, m_id from products where m_id = 123 and exists ( select r_id from resellers where r_name like 'a%' and r_id = products.r_id ) order by m_id, c_id; Unique (cost=0.00..89.79 rows=1 width=8) -> Index Scan using products_mcr on products (cost=0.00..89.74 rows=10 width=8) SubPlan -> Index Scan using resellers_n on resellers (cost=0.00..8.16 rows=1 width=4) 4) set enable_hashjoin =
[SQL] Re: Informix->PostgreSQL database convertion
How is the SQL command UNLOAD (informix) used to extract both data and database architecture? Is it able to create a file of SQL statements like when using postgresql's command "pg_dump -f outputfile mydb" ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] SELECT timestamp('2001-06-12'::date - '2000-06-12'::date)
The "+" operator is not defined for (date, date) which seems very
logical to me since adding dates does in my opinion not make sense.
performance=# SELECT timestamp('2001-06-12'::date + '2000-06-12'::date);
ERROR: Unable to identify an operator '+' for types 'date' and 'date'
You will have to retype this query using an explicit cast
Surprisingly "-" is defined for (date, date).
performance=# SELECT timestamp('2001-06-12'::date - '2000-06-12'::date);
timestamp
1970-01-01 01:06:05+01
(1 row)
Does anybody understand the logic behind that operation and why is the
result not something like 1970-01-01 00:00:00?
Hans
---(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] can't get rid of unnesesary SORT step in explain plan for hash join
Alexey Nalbat <[EMAIL PROTECTED]> writes: > So, my question is: how can I get rid of this unnesesary "Sort" step > in the execution plan for hash join? You can't, because it's not unnecessary. Hash join doesn't promise to produce its outputs in any particular order. But the Unique filter needs to see its inputs in order by the fields being made unique. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] SELECT timestamp('2001-06-12'::date - '2000-06-12'::date)
=?iso-8859-1?Q?Hans=2DJ=FCrgen=20Sch=F6nig?= <[EMAIL PROTECTED]> writes:
> Surprisingly "-" is defined for (date, date).
What's surprising about that? It yields an integer number of days
between the dates:
regression=# select '2001-06-12'::date - '2000-06-12'::date;
?column?
--
365
(1 row)
> performance=# SELECT timestamp('2001-06-12'::date - '2000-06-12'::date);
>timestamp
>
> 1970-01-01 01:06:05+01
> (1 row)
timestamp(integer) converts a Unix timestamp value (seconds since 1970)
into a timestamp.
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
[SQL] Problem using IP functions
Hi,
I'm having trouble using the host() and netmask() functions within a select
query. For some reason, the following query returns 1 row:
ipdb=> select id, subnet_number from subnet where subnet_number =
'216.46.13.0';
id | subnet_number
+---
96 | 216.46.13.0
(1 row)
Yet, if I replace the ip string literal with host('216.46.13.0/24'), I get
no results. ie:
ipdb=> select id, subnet_number from subnet where subnet_number =
host('216.46.13.0/24');
id | subnet_number
+---
(0 rows)
Even though host('216.46.13.0/24') evaluates to 216.46.13.0
ipdb=> select host('216.46.13.0/24');
host
-
216.46.13.0
(1 row)
The subnet_number column is a varchar(16) which I assume you can compare
with a text data type, which is what host() returns. Just to be sure, I
tried casting everything to type text, but that didn't do the trick.
ipdb=> select texteq(host('216.46.13.0/24')::text, '216.46.13.0'::text);
texteq
f
(1 row)
Any insight would be greatly appreciated.
Marc
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [SQL] Problem using IP functions
"Marc Lamothe" <[EMAIL PROTECTED]> writes: > The subnet_number column is a varchar(16) which I assume you can compare > with a text data type, which is what host() returns. Are you on a pre-7.1 Postgres release? host() is buggy before 7.1 --- it includes a trailing null in its output, which it shouldn't oughta have done. You can't see the null from outside the system, but it manages to mess up text comparisons anyway. BTW, you should consider using inet or cidr datatype for that column rather than varchar... 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])
