Re: [SQL] RFC: A brief guide to nulls
> > --- [EMAIL PROTECTED] wrote: >> There have been a few posts recently where people >> have had problems with >> nulls. Anyone got comments on the below before I >> submit it to techdocs? >> >> TIA >> >> - Richard Huxton >> >> A Brief Guide to NULLs >> == >> >> What is a null? >> === >> A null is *not* an empty string. >> A null is *not* a value. >> A null is *not* a "special" value. >> A null is the absence of a value. >> >> >> What do nulls mean? >> === >> Well, they *should* mean one of two things: >> 1. There is no applicable value >> 2. There is a value but it is unknown > > Good job!, it sure helps people who don't much > background on formal database theory. > > What about adding this to the section "What does > nulls mean" > --> 3) No value has yet been assigned to that > particular attribute (field). > > > I think it would also be nice if you can add a > section on functions that deals with NULL such as > nullif() and coalesce(). These functions help users > interpret NULL values. > > > best regards, > > ludwig > > > > __ > Do you Yahoo!? > New DSL Internet Access from SBC & Yahoo! > http://sbc.yahoo.com > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] RFC: A brief guide to nulls
Apologies for previous post with no content - hit send by mistake. Thanks to everyone for the feedback, 2nd draft out later today/tomorrow. - Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] lost on self joins
Ross J. Reedstrom wrote: Finaly, a table to allow a many to many join called files_folders | files_folders x| ffid | folderid (fk to folders.folderid) | fileid (fk to files.fileid) Strange. Do you need this table? Can one file exist in several directories? If not, you can just add "folderid" field into table files. Good point. No, it can't exist in multiple folders, so I guess it's overkill to do a many to many here. Thanks for the moment of clarity. Unless you're attempting to accurately map Unix filesystem sematics, where the exact same file _can_ be in more than one place in the filesystem (hard links). It's all about the inode. One of the wierder bits of unix that you don't often see used in common occurances. Ross If we are talking about Unix filesystems - this solution doesn't let you change filename when using hard (symbolic) links. Anyway I wish I could use symbolic link on windows machine the same like on linux one... I think more important for Matthew would be protection against circular join which causes query to hang up. Tomasz ---(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] RFC: A brief guide to nulls (noarchive)
You might add this as an example:
mytable
id value
1 1
2 2
3 3
4
5 4
6 5
-- Count ALL records
SELECT count(*) FROM mytable;
Result: 6
-- Count id records
SELECT count(id) FROM mytable;
Result: 6
-- Count value records
SELECT count(value) FROM mytable;
Result: 5
-- Sum of all values
SELECT sum(value) FROM mytable;
Result: 15
-- Average of the values
SELECT sum(value)/count(value) FROM mytable;
Result: 3
-- !!!WRONG!!! Method of the average
SELECT sum(value)/count(*) FROM mytable;
Result: 2.5
What I try to do and what I advice to the novice database designer is try to
avoid NULLS. Why??? because it's differcult to think in tree based logic.
As for a example in the case of the customer sex.
You can use 'M' for male, 'F' for female, 'U' for unknown and 'N' for 'not
applicapable'.
In this way you can use the NOT NULL contsraint so the novice programmer can
work with actual values instead of 'forgetting' about the NULLS. This idea
can be helpfull for the novice database designer.
Just a thought...
Ries van Twisk
> -Oorspronkelijk bericht-
> Van: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]Namens [EMAIL PROTECTED]
> Verzonden: woensdag 15 januari 2003 18:23
> Aan: [EMAIL PROTECTED]
> Onderwerp: [SQL] RFC: A brief guide to nulls
>
>
> There have been a few posts recently where people have had
> problems with
> nulls. Anyone got comments on the below before I submit it to
> techdocs?
>
> TIA
>
> - Richard Huxton
>
> A Brief Guide to NULLs
> ==
>
> What is a null?
> ===
> A null is *not* an empty string.
> A null is *not* a value.
> A null is *not* a "special" value.
> A null is the absence of a value.
>
>
> What do nulls mean?
> ===
> Well, they *should* mean one of two things:
> 1. There is no applicable value
> 2. There is a value but it is unknown
>
> Example 1: Imagine you have a customer table with name and sex fields.
> If you get a new customer "ACME Widgets Ltd", the sex field
> is meaningless
> since your customer is a company (case 1).
> If you get a new customer "Jackie Smith" they might be male
> or female, but
> you might not know (case 2).
>
> Example 2: You have an address table with
> (street,city,county,postalcode)
> fields.
> You might insert an address ("10 Downing
> Street","London",Null,"WC1 1AA")
> since you don't have a valid county.
> You might also insert an address ("1 Any
> Street","Maidstone","Kent",Null)
> where there *must be* a valid postalcode, but you don't know
> what it is.
>
> It might be useful to be able to distinguish between these
> two cases - not
> applicable and unknown, but there is only one option "Null"
> available to
> us, so we can't.
>
>
> How do nulls work?
> ==
> There is one very important rule when dealing with nulls. The
> result of
> any operation or comparison, when applied to a null is null. The only
> exception is testing if a value is null.
>
> Example: with the customer table above you could run the
> following queries:
> SELECT * FROM customer WHERE sex='M';
> SELECT * FROM customer WHERE sex<>'M';
> Now you might think this returns all customers, but it will miss those
> where sex is null. You've asked for all rows where the value
> of sex is 'M'
> and all those with values not equal to 'M' but not rows with
> *no value at
> all*
>
> It might help to think of a database as a set of statements
> you *know* to
> be true. A null indicates that you *cannot say anything at
> all* about that
> field. You can't say what it is, you can't say what it isn't,
> you can only
> say there is some information missing.
>
> So, to see all the customers with unknown or inapplicable sex
> you would need:
> SELECT * FROM customer WHERE sex IS NULL;
>
> There are actually three possible results for a test in SQL -
> True (the
> test passed), False (the test failed) and Null (you tested against a
> null). A result of null usually gets treated as False, so
> testing against
> nulls always fails.
>
> If you try to perform an operation on nulls, again the result
> is always
> null. So the results of all of the following are null:
> SELECT 'abc' || null;
> SELECT 1 + null;
> SELECT sqrt(null::numeric);
> The first case can be especially confusing. Concatenating a
> null string to
> a string value will return null, not the original value.
>
>
> Uniqueness and nulls
>
> If you define a unique index on a column it prevents you inserting two
> values that are the same. It does not prevent you inserting
> as many nulls
> as you like. How could it, you don't have a value so it can't
> be the same
> as any other.
>
> Example: We create a table "ta" with a unique constraint on column "b"
> CREATE TABLE ta (
> a int4,
> b varchar(3),
> PRIMARY KEY (a)
> );
> CREATE UNIQUE INDEX ta_b_idx ON ta (b);
> INSERT INTO ta VALUES (1,'aaa'); -- succeeds
> INSERT INTO ta VALUES (2,'bbb'); -- succ
Re: [SQL] Oracle outer join porting question
On Wed, 15 Jan 2003, Tambet Matiisen wrote: > ... > Try this: > > SELECT doc.id,doc.title,sub.user_id,sub.operation > FROM document doc LEFT OUTER JOIN document_subscription sub > ON sub.document_id = doc.id AND sub.user_id = 6; yes, this query seems to give the same results as the original one. thanks! best regards, -- aspahttp://www.kronodoc.fi/ ---(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] pg_dump problem
Rudi Starcevic wrote: Hi, After doing a pg_dump on a database I'm unable to access the file. My command is simply 'mv' :: mv camper.dump20020116 camper_bak/ The error I get is :: mv: camper.dump20020116: Value too large for defined data type Strange. It seems to be saying the file I created is too large to handle. Do you know where I've gone wrong ? I just want to dump one of my databases, move it to a back up dir. then make changes to the running database. It looks like you have not a postgres, but filesystem problem. How big is this file? Anyway pg_dump can save filedump in gzip format, which is better for further backuping. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Implementing automatic updating of primary keys...
Hi we are working on re-structuring our database schemas and intend to implement the functionality below at database level. consider a master table with following data. Table: profile_master id | username | password ---|--|-- 1 | u1 | p1 2 | u2 | p2 id--> primary key not null. table t1 --- id | service_id ---| 1 | 1 2 | 10 table t2 -- id | rfi_id ---|--- 1 | 1001 2 | 23 there can be many such tables that have foreign key id which is referencing the master table test_master column "id". what we want is when some ids become redundant we have to merge two ids , we want that thru out the system the occurance of the old primary key shud be removed and replaced with the new id. so if id 2 were to be mergered to id 1 then the tables shud look like: Table: profile_master id | username | password ---|--|-- 1 | u1 | p1 id--> primary key not null. table t1 --- id | service_id ---| 1 | 1 1 | 10 table t2 -- id | rfi_id ---|--- 1 | 1001 1 | 23 can some thing be done in the database level it self so that we do not have to keep modifying the mantainence programs as the number of tables referencing master table grows? regds mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Implementing automatic updating of primary keys...
Rajesh Kumar Mallah. wrote: Hi we are working on re-structuring our database schemas and intend to implement the functionality below at database level. consider a master table with following data. Table: profile_master id | username | password ---|--|-- 1 | u1 | p1 2 | u2 | p2 id--> primary key not null. can some thing be done in the database level it self so that we do not have to keep modifying the mantainence programs as the number of tables referencing master table grows? regds mallah. If I understood well you want to change id in all tables from some value into another one and no matter, how many these tables exist? First - if your tables are created with "on update cascade", you can just change value on master table. If you didn't create tables with this option and referencing key has the same name in all tables, it isn't still too difficult. Everything you need is a function which finds all tables with field "id" and for each table performs: update set id=newvalue where id=oldvalue. In plpgsql it will look something like: create or replace function... declare oldvalue alias for $1; newvalue alias for $2; tablename varchar; begin for tablename in SELECT relname from pg_attribute join pg_class on (attrelid=oid) where attname=''id'' and relkind='r'; loop perform ''update '' || tablename '' set id='' || newvalue || '' where id='' || oldvalue; end loop; end; Many interesting find about database special tables you will find in Chapter 3. System Catalogs inside Postgresql documentation. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Implementing automatic updating of primary keys...
yes you got my problem rightly. If i use "on update cascade" approach still there is problem. If i attempt to update the key in master table it wont be allowed becoz of temporary violation of PRIMARY KEY CONSTRAINT. becoz 1 is also existing in the master table. update profile_master set id=1 where id=2 will not be accepted. regds mallah. On Wednesday 01 January 2003 06:11 pm, Tomasz Myrta wrote: > Rajesh Kumar Mallah. wrote: > >Hi we are working on re-structuring our database schemas and > >intend to implement the functionality below at database level. > > > >consider a master table with following data. > > > > > >Table: profile_master > > > > > >id | username | password > >---|--|-- > >1 | u1 | p1 > >2 | u2 | p2 > > > >id--> primary key not null. > > > >can some thing be done in the database level it self so that we do not > > have to keep modifying the mantainence programs as the number of tables > > referencing master table grows? > > > >regds > >mallah. > > If I understood well you want to change id in all tables from some value > into another one and no matter, how many these tables exist? > > First - if your tables are created with "on update cascade", you can just > change value on master table. > > If you didn't create tables with this option and referencing key has the > same name in all tables, it isn't still too difficult. > > Everything you need is a function which finds all tables with field "id" > and for each table performs: update set id=newvalue where > id=oldvalue. > > In plpgsql it will look something like: > create or replace function... > declare > oldvalue alias for $1; > newvalue alias for $2; > tablename varchar; > begin > for tablename in SELECT relname from pg_attribute join pg_class on > (attrelid=oid) where attname=''id'' and relkind='r'; > loop > perform ''update '' || tablename '' set id='' || newvalue || '' where > id='' || oldvalue; end loop; > end; > > Many interesting find about database special tables you will find in > Chapter 3. System Catalogs inside Postgresql documentation. > > Regards, > Tomasz Myrta > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Function unkown
How is this function ? plpgsql_call_handler() RETURNS language_handler AS 'plpgsql_call_handler' LANGUAGE 'c' VOLATILE; ---Outgoing mail is certified Virus Free.Checked by AVG anti-virus system (http://www.grisoft.com).Version: 6.0.443 / Virus Database: 248 - Release Date: 1/10/2003
Re: [SQL] RFC: A brief guide to nulls
I think that having this topic defined and available
will be of great benefit... !!! Thanks Richard.
Some additional thoughts based upon what other people
have explicitly or implicitly implied. Peter quoted the sql
definition as:
>Every data type includes a special value, called the null value,
and my earlier post implied that, but...
I believe that one needs to be VERY explicit in the
distinction between sementic meaning of some field
and the values accessable. Using the example proposed
in earlier posts, the storing of sex field for a customer.
The example query was:
> SELECT * FROM customer WHERE sex='M';
> SELECT * FROM customer WHERE sex<>'M';
One person suggested:
>For example sex could be classified as
> 'n' - not applicable
> 'f' - female
> 'm' - male
> null - yet unknown
>...
>if customers sex is unknown - null, we can't decide
>whether they're men or not.
Which is straying way into the semantic information of
the field. In reality we have:
+Fieldname(sex)--+
|..|value|{somevalue}|
|..|nullP|{1or0} |
++
Every field has a value portion and a nullP portion.
Here I use nullP, which is either 1 or 0, indicating
that the field is NULL or NOTNULL.
NOTNULL means that the value field has a value.
NULL means that the value field does not have a value.
Period. Anything else begins to stray into the semantic
range. Using the above...
> 'n' - not applicable
> 'f' - female
> 'm' - male
> null - yet unknown
which we could code as..
.meaning
+Fieldname(sex)--+
|..|value|{n}| not applicable
|..|nullP|{0}|
++
+Fieldname(sex)--+
|..|value|{f}| female
|..|nullP|{0}|
++
+Fieldname(sex)--+
|..|value|{m}| male
|..|nullP|{0}|
++
+Fieldname(sex)--+
|..|value|{} |
|..|nullP|{1}|
++
If I was a clerk, looking at a person and could not
tell their sex due to appearance, etc, that should
probably be coded: 'u'-unknown
The "concept" of null meaning unknown is SEMANTIC...
in the case of sex.
The nullP, ie testing via NULL/NOTNULL, means that
one can either test or (should/can) access the value field.
No other meaning should be implied. The application
on up the ladder implies the meaning behind not having
a value.
The point I'm trying to make here is that one should not
intermix the application meaning of having/not-having
a value with the value itself.
Especially since you can have a NOTNULL field that
has a text value having no bytes in it, i.e. a string
field that has no characters...
Humm... don't know if I made my point clear as mud...
Best regards,
.. Otto
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] pg_restore cannot restore an index
Last July, I pointed out this problem when I use v7.2.1, I got the answer that will be
resolved in v7.3, however, I am using v7.3.1, pg_restore.c seems have no change in
this section. So it still doesn't work.
Jie Liang
Jie Liang wrote:
> I read the pg_restore.c source code, I found:
> #ifdef HAVE_GETOPT_LONG
> struct option cmdopts[] = {
> {"clean", 0, NULL, 'c'},
> {"create", 0, NULL, 'C'},
> {"data-only", 0, NULL, 'a'},
> {"dbname", 1, NULL, 'd'},
> {"file", 1, NULL, 'f'},
> {"format", 1, NULL, 'F'},
> {"function", 1, NULL, 'P'},
> {"host", 1, NULL, 'h'},
> {"ignore-version", 0, NULL, 'i'},
> {"index", 1, NULL, 'I'},
> So, -i may be mapped wrong, however, -I is illegal option.
>
> Thanks!
>
>
---(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] query speed joining tables
Vernon, > What I stated is my observation on my project with over twenty > multivalued detail tables. I have a selection query > contained 200 characters, involving 10 tables, and using subquery. > The performance is not bad after properly indexing, > least than 3 second (what the planner says). I will have longer > queries later and hope they won't have any performance > problem. Keep in mind that the complexity is all on your end, not the users'. You can construct VIEWs, FUNCTIONs, TRIGGERs and RULEs which will make the actual sophistication (i.e., 20 "detail tables") appear to the user exactly as if there was only one flatfile table. Frequently in database design, the design which is good for efficiency and data integrity ... the "nromalized" design ... is baffling to users. Fortunately, both SQL92-99 and PostgreSQL give us a whole toolkit to let us "abstract" the normalized design into something the users can handle. In fact, this is job #2 for the DBA in an applications-development team (#1 is making sure all data is stored and protected from corruption). > Thank you for recommending another DB book after the "Database Design > For Mere Mortals". I will read the book. That's a great book, too. Don't start on Pascal until *after* you have finished "database design". -Josh Berkus ---(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] pg_restore cannot restore an index
Yes, I remember this. The code in 7.3 looks OK to me. Can you show me a
command line that fails for you?
I just tried:
$ pg_restore -I x asdf
pg_restore: [archiver] could not open input file: No such file or directory
so it looks like -I is working.
---
Jie Liang wrote:
> Last July, I pointed out this problem when I use v7.2.1, I got the answer that will
>be resolved in v7.3, however, I am using v7.3.1, pg_restore.c seems have no change in
>this section. So it still doesn't work.
>
> Jie Liang
>
>
>
> Jie Liang wrote:
> > I read the pg_restore.c source code, I found:
> > #ifdef HAVE_GETOPT_LONG
> > struct option cmdopts[] = {
> > {"clean", 0, NULL, 'c'},
> > {"create", 0, NULL, 'C'},
> > {"data-only", 0, NULL, 'a'},
> > {"dbname", 1, NULL, 'd'},
> > {"file", 1, NULL, 'f'},
> > {"format", 1, NULL, 'F'},
> > {"function", 1, NULL, 'P'},
> > {"host", 1, NULL, 'h'},
> > {"ignore-version", 0, NULL, 'i'},
> > {"index", 1, NULL, 'I'},
> > So, -i may be mapped wrong, however, -I is illegal option.
> >
> > Thanks!
> >
> >
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] pg_restore cannot restore an index
Sorry, it because I have another index with same indexname because pg_restore index
fail.
Thanks.
Jie Liang
-Original Message-
From: Bruce Momjian [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 16, 2003 10:07 AM
To: Jie Liang
Cc: Tom Lane; [EMAIL PROTECTED]
Subject: Re: [SQL] pg_restore cannot restore an index
Yes, I remember this. The code in 7.3 looks OK to me. Can you show me a
command line that fails for you?
I just tried:
$ pg_restore -I x asdf
pg_restore: [archiver] could not open input file: No such file or directory
so it looks like -I is working.
---
Jie Liang wrote:
> Last July, I pointed out this problem when I use v7.2.1, I got the answer that will
>be resolved in v7.3, however, I am using v7.3.1, pg_restore.c seems have no change in
>this section. So it still doesn't work.
>
> Jie Liang
>
>
>
> Jie Liang wrote:
> > I read the pg_restore.c source code, I found:
> > #ifdef HAVE_GETOPT_LONG
> > struct option cmdopts[] = {
> > {"clean", 0, NULL, 'c'},
> > {"create", 0, NULL, 'C'},
> > {"data-only", 0, NULL, 'a'},
> > {"dbname", 1, NULL, 'd'},
> > {"file", 1, NULL, 'f'},
> > {"format", 1, NULL, 'F'},
> > {"function", 1, NULL, 'P'},
> > {"host", 1, NULL, 'h'},
> > {"ignore-version", 0, NULL, 'i'},
> > {"index", 1, NULL, 'I'},
> > So, -i may be mapped wrong, however, -I is illegal option.
> >
> > Thanks!
> >
> >
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] query speed joining tables
16/01/2003 9:46:30 AM, "Josh Berkus" <[EMAIL PROTECTED]> wrote: >Vernon, > >> What I stated is my observation on my project with over twenty >> multivalued detail tables. I have a selection query >> contained 200 characters, involving 10 tables, and using subquery. >> The performance is not bad after properly indexing, >> least than 3 second (what the planner says). I will have longer >> queries later and hope they won't have any performance >> problem. > >Keep in mind that the complexity is all on your end, not the users'. > You can construct VIEWs, FUNCTIONs, TRIGGERs and RULEs which will make >the actual sophistication (i.e., 20 "detail tables") appear to the user >exactly as if there was only one flatfile table. > Well, my current position is a DB design as well as a DB user. I'm doing J2EE development without EJB. I currently have two ways of building a query. One is to set up a query string as a static string. This method is similar with the View in DB, but in application layer (Date Access Object). I apply this type of query strings on insertion, selection, updating, and deletion operations of a DB table. The other way to build a query string is used on selection operation for multiple table joined and/or involved. A query string is built dynmically due to whether or not any fields are examined. The characteristic of the application is that among of many fields a user may only want search on a few selected fields. I think this approach is better than to have all fields listed and fill in "%" for fields the user doesn't want to search on. (Please correct me if I'm wrong). But the building query function is as long as more than one hundred lines. >Frequently in database design, the design which is good for efficiency >and data integrity ... the "nromalized" design ... is baffling to >users. Fortunately, both SQL92-99 and PostgreSQL give us a whole >toolkit to let us "abstract" the normalized design into something the >users can handle. In fact, this is job #2 for the DBA in an >applications-development team (#1 is making sure all data is stored and >protected from corruption). > Please elaborate the above statement. I don't know any 'toolkit to let us "abstract" the normalized design into something the users can handle', other than something like View. >> Thank you for recommending another DB book after the "Database Design >> For Mere Mortals". I will read the book. > >That's a great book, too. Don't start on Pascal until *after* you >have finished "database design". I waited for the book from the local library for more than a month, but only took me less than a hour to scan over the whole book and grip the multivalued table design idea. > >-Josh Berkus > Vernon ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] query speed joining tables
Vernon, > The other way to build a query string is used on selection operation for multiple table joined and/or involved. A query > string is built dynmically due to whether or not any fields are examined. The characteristic of the application is that > among of many fields a user may only want search on a few selected fields. I think this approach is better than to have > all fields listed and fill in "%" for fields the user doesn't want to search on. (Please correct me if I'm wrong). You're correct. >But the > building query function is as long as more than one hundred lines. Sure. It's a question of whether you want to spend your DBA time during the design phase, or when you're using and administering it in production. My general experience is that every extra hour well spent on good DB design saves you 20-40 hours of admin, data rescue, and by-hand correction when the database is in production. > Please elaborate the above statement. I don't know any 'toolkit to let us "abstract" the normalized design into > something the users can handle', other than something like View. VIEWs, TRIGGERs, RULEs and FUNCTIONs. WIth 7.3.1, SCHEMA as well. Using only these structures, I have been able to build entire applications where my PHP programmer never needs to know the intracacies of the database. Instead, he is given an API for views and data manipulation functions. > I waited for the book from the local library for more than a month, but only took me less than a hour to scan over the > whole book and grip the multivalued table design idea. Hmmm. I'll need to look at it again. If he's suggesting that it's a good idea to put a delimited list in a field, I'll need to stop recommending that book. -- -Josh Berkus Aglio Database Solutions San Francisco ---(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] cannot create function that uses variable table name
I have a number of tables in my database that use the concept of display order, which is a field that can be used in an order by clause to dictate what order the results should come out in. I thought I would be crafty and devise a function that would always return the highest numbered item in the table. But it doesnt work. It always gives me a parse error at $1. Heres the function: CREATE OR REPLACE FUNCTION get_last_dsply_order( varchar,-- tablename varchar,-- id_col_name varchar)-- where_item RETURNS integer AS ' DECLARE total_items integer; tablename ALIAS FOR $1; id_col_name ALIAS FOR $2; where_item ALIAS FOR $3; BEGIN SELECT INTO total_items count(*) FROM tablename WHERE id_col_name = where_item; RETURN total_items; END; ' LANGUAGE 'plpgsql'; Heres some sample data so that you can better see what Im doing: Fileid| accountid | filename | dsply_order == 1| account1 | My File | 1 2| account1 | Another file | 2 3| account1 | YA File | 3 4| account2 | Hello world | 1 5| account2 | Hi again | 2 6| account3 | Good bye | 3 7| account4 | Mom | 2 8| account4 | Dad | 1 = Therefore you would want to see the last item number used by account2 so that you can add a new item to the end of the list. You might do something like this: INSERT INTO files (accountid, filename, dsply_order) VALUES (account2,Testing,get_last_dsply_order(files,accountid,account2 )); BTW, it will have a complementary trigger assigned to each table that upon delete will shift all the items up 1 to fill in the gap left by the deleted item. Therefore the count() of the items in the table should also match the highest numbered item. -- Matthew Nuzum www.bearfruit.org [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] cannot create function that uses variable table name
--- Matthew Nuzum <[EMAIL PROTECTED]> wrote: > I thought I would be crafty and devise a function that would always > return the highest numbered item in the table. But it doesnt work. > It always gives me a parse error at $1. Heres the function: build the query as a string and execute it. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] cannot create function that uses variable table name
On Thu, 16 Jan 2003, Matthew Nuzum wrote: > I have a number of tables in my database that use the concept of > display order, which is a field that can be used in an order by clause > to dictate what order the results should come out in. > > I thought I would be crafty and devise a function that would always > return the highest numbered item in the table. But it doesnt work. It > always gives me a parse error at $1. Heres the function: > > CREATE OR REPLACE FUNCTION get_last_dsply_order( >varchar,-- tablename >varchar,-- id_col_name >varchar)-- where_item >RETURNS integer AS ' >DECLARE total_items integer; > tablename ALIAS FOR $1; > id_col_name ALIAS FOR $2; > where_item ALIAS FOR $3; >BEGIN > SELECT INTO total_items count(*) FROM tablename WHERE id_col_name > = where_item; >RETURN total_items; > END; > ' LANGUAGE 'plpgsql'; You'll need to look into EXECUTE. You also are going to have to watch out for concurrency issues since two transactions calling this function at the same time for the same args are likely to give incorrect results. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
