Re: [SQL] cannot create function that uses variable table name
On Thursday 16 January 2003 22:32, 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:
I may be wrong but aren't you trying to do something like this?
INSERT INTO files (accountid, filename, dsply_order)
VALUES ('account2', 'Testing',
(SELECT COALESCE(MAX(dsply_order), 0) + 1 FROM files
WHERE accountid = 'account2'));
Alternatively, assuming that fileid is a serial number, why not just use that
in your order by clause. I assume that you want something like this.
SELECT * FROM files WHERE accountid = 'account2' ORDER BY dsply_order;
This should give you exactly the same result:
SELECT * FROM files WHERE accountid = 'account2' ORDER BY fileid.
It all depends on what problem exactly you are trying to solve of course.
--
D'Arcy J.M. Cain| Democracy is three wolves
http://www.druid.net/darcy/| and a sheep voting on
+1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] sum(time) problem
Hi all, I've got following table structure: sport=# \d polar Table "polar" Column | Type | Modifiers +--+--- ts | timestamp with time zone | not null time | time without time zone | sport | integer | default 1 kcal | integer | Primary key: polar_pkey sport=# select * from polar limit 3; ts | time | sport | kcal +--+---+-- 2001-05-17 19:47:31+02 | 00:28:25 | 1 | 388 2001-05-18 17:08:11+02 | 00:42:36 | 1 | 595 2001-05-19 13:41:43+02 | 00:51:39 | 1 | 737 (3 rows) I'd like to have the total amount of time: sport=# select sum(time) from polar where extract(year from ts)=2001; ERROR: Unable to select an aggregate function sum(time without time zone) It seems I've chosen the wrong type. Or is there another solution? Oliver -- VECERNIK Datenerfassungssysteme A-2560 Hernstein, Hofkogelgasse 17 Tel.: +43 2633 47530, Fax: DW 50 http://members.aon.at/vecernik ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] sum(time) problem
On 17/1/03 13:03, "Oliver Vecernik" <[EMAIL PROTECTED]> wrote: > sport=# \d polar >Table "polar" > Column | Type | Modifiers > +--+--- > ts | timestamp with time zone | not null > time | time without time zone | > sport | integer | default 1 > kcal | integer | > Primary key: polar_pkey > > sport=# select * from polar limit 3; > ts | time | sport | kcal > +--+---+-- > 2001-05-17 19:47:31+02 | 00:28:25 | 1 | 388 > 2001-05-18 17:08:11+02 | 00:42:36 | 1 | 595 > 2001-05-19 13:41:43+02 | 00:51:39 | 1 | 737 > (3 rows) > > > I'd like to have the total amount of time: > > sport=# select sum(time) from polar where extract(year from ts)=2001; > ERROR: Unable to select an aggregate function sum(time without time zone) > > It seems I've chosen the wrong type. Or is there another solution? Correct diagnosis. You need the "interval" type, not the "time" type for your second field. Interval is a time difference between two timestamps, for example the time between the start and the finish of a race. If you check out the available aggregates with \da you'll find that you can sum an interval, but not a time. Julian Scarfe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] sum(time) problem
Julian Scarfe <[EMAIL PROTECTED]> writes: >> It seems I've chosen the wrong type. Or is there another solution? > Correct diagnosis. You need the "interval" type, not the "time" type for > your second field. Interval is a time difference between two timestamps, > for example the time between the start and the finish of a race. Right. "time" is intended for time-of-day values; so taking the sum would be a nonsensical operation. 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] RCF: 2nd draft: A brief guide to Nulls
Thanks all for the previous feedback. If no-one comes up with any errors
in this draft I'll call it finished.
- Richard Huxton
A Brief Guide to NULLs
==
revision: 0.9
date: 2002-01-17
author: Richard Huxton <[EMAIL PROTECTED]>
Overview
This is a short guide to the use of nulls in SQL databases. It is written
with Postgresql in mind but should be applicable to any SQL-based DBMS.
Thanks to the members of the psql-sql mailing list for their assistance in
preparing this guide.
You can get further information in:
Any good relational database book (try something written by Date or Pascal)
Bruce's book (link LHS at http://techdocs.postgresql.org)
My Postgresql Notes (link at http://techdocs.postgresql.org)
PART I - INTRODUCTION
What is a null?
===
A null is *not* an empty string.
A null is *not* a value like others.
A null is the absence of a value[1].
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).
Actually, since you are trying to store a company in the ACME example that
might indicate that you need to rethink your design.
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. A null is
unknown and thus not equal to, less than or greater than any value it is
compared to.
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;
Note that the following will not work, you need to use "IS NULL"
SELECT * FROM customer WHERE sex=NULL;
There are actually three possible results for a test in SQL - True (the
test passed), False (the test failed) and Null (unknown or can't say). The
table below indicates the result of using AND/OR operations on a,b for
values of True,False and Null.
a | b | a AND b | a OR b
--+---+-+
TRUE | TRUE | TRUE| TRUE
TRUE | FALSE | FALSE | TRUE
TRUE | NULL | NULL| TRUE
FALSE | FALSE | FALSE | FALSE
FALSE | NULL | FALSE | NULL
NULL | NULL | NULL| NULL
In the example of a=True,b=Null, (a AND b) is Null (which gets treated as
false for the purposes of WHERE clauses). However (a OR b) is True since
if a is True, we don't care what b is.
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. This can catch
you out if you are joining first_name to last_name and one of them
contains nulls.
How are nulls implemented?
==
You can think of each null-able field/column having a separate "is_null"
flag attached to it. So, if you have a column "a" of type integer, in
addition to space required to store the number, there is another bit which
says whether the item is null and the value should be ignored. Of course,
there are optimisations that get made, but that is the general idea.
PART II - IMPLICATIONS
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"
[SQL] "Best practice" advice
I'm developing the second stage of a database that will eventually be used to model networks of references between documents. I already have a table of core documents, and the next step is to track what documents each of these core documents refers to. (Is this confusing enough already?) The relationship is one-to-many, and I can handle that fine. The issue is: some of the references in the core documents will be to other core documents. Other references will be to documents that are not in the core documents table. I need to track whether the document referred to is in the core table or not. The question is how best to capture this. Ideas I have are: 1.) A single referrals table that can track both kinds: referring_id --> serial number of the referring core document referred_title referred_author referred_date referred_page referred_id --> serial number of the referred document if it's in the core table; otherwise NULL 2.) Two referrals tables: referring_id referring_id referred_titlereferred_id referred_author referred_date referred_page 3.) A "peripheral documents" table and a referrals table: periph_id referring_id title referred_table authorreferred_id date page Comments? Thanks. -- Andrew J Perrin - http://www.unc.edu/~aperrin Assistant Professor of Sociology, U of North Carolina, Chapel Hill [EMAIL PROTECTED] * andrew_perrin (at) unc.edu ---(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] "Best practice" advice
Andrew, > The relationship is one-to-many, and I can handle that fine. The issue > is: some of the references in the core documents will be to other core > documents. Other references will be to documents that are not in the core > documents table. I need to track whether the document referred to is > in the core table or not. The question is how best to capture this. Ideas > I have are: I'd suggest, instead, having just one "documents" table with a boolean column called "is_core". This will simplify relating core docs to each other, and additionally allow for the promotion of "peripheral" docs to the core easily. -- -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
Re: [SQL] [GENERAL] double precision to numeric overflow error
Thomas O'Connell wrote: > > Well, it would've immediately (rather than the several minutes it took) > given away the problem if it read something like: > > ERROR: overflow caused by cast of double precision value to numeric > without sufficient precision, scale (15, 6) > > or even, depending on how much detail is available or how much worth > assigned to error reporting: The message will be exactly the same in all cases, since there is no more detail. I think words about casting and such could be fairly confusing in the case someone just inserts a literal value - no? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Can I do this?
I want to select some data out of database A, and insert them into database B. Is it possible to do in one SQL query? Thanks Wei
Re: [SQL] Can I do this?
Wei, > I want to select some data out of database A, and insert them into database B. Is it possible to do in one SQL query? > No. Use a Perl script. -- -Josh Berkus Aglio Database Solutions San Francisco ---(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] Can I do this?
See /contrib/dblink, or use an application that attaches to both databases, or use COPY somehow. --- Wei Weng wrote: > I want to select some data out of database A, and insert them into database B. Is it >possible to do in one SQL query? > > Thanks > > Wei -- 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 4: Don't 'kill -9' the postmaster
[SQL] Problem with COPY FROM
Hi all, I am trying to copy some date from a text file to pgsql. I am using pgsql 7.3 version. The problem i am face is that i try to copy a TEXT field to a table. I have serveral line in this field. Let say, I have a table T1, and three fields with it, F1, F2, F3. F1 is varchar(5), F2 is text and F3 is int When I pg_dump T1, it show something like COPY T1(F1, F2) FROM stdin; 1 Line1\r\nLine2\r\nLine3\r\n 1 2 LineA\r\nLineB\r\nLineB\r\n 3 \. . I cut it out and run it. It get me the following error message: ERROR: copy: line 1, value too long for type character(5) Then I thought, the tab(\t) is messed up, so I delete all the space in between the fields and put a tab by myself. The funny thing happen. Sometime, it give me the error message ERROR: copy: line 2, Missing data for column "F2" However, somtime it have no error message and no "COPY" to identicate the sussecc of the command. ( In fact, it didn't copy the data for me!) Please, could anyone help me a bit with the COPY FROM with text field. Thank You very Much! Harry Yau ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
