Re: [SQL] Documentation for CASE
On Sat, Dec 16, 2000 at 06:39:14PM +0100, Peter Eisentraut wrote: > Albert REINER writes: > > > I think that the documentation for CASE is not very clear: > > Maybe you will like this better (from upcoming 7.1): > > http://www.postgresql.org/devel-corner/docs/postgres/functions-conditional.htm much better. Sorry not to have checked that first. Albert. -- -- Albert Reiner <[EMAIL PROTECTED]> Deutsch * English * Esperanto * Latine --
[SQL] Subqueries in Non-SELECT Queries
I am trying to do something that looks to me exactly like what's described in chapter 8.4 in the Postgresql book: test=> UPDATE salesorder test-> SET ship_date = '11/16/96' test-> WHERE customer_id = ( test(> SELECT customer_id test(> FROM customer test(> WHERE name = 'Fleer Gearworks, Inc.' test(> ); UPDATE 1 Here only one row is updated. This should work with more than one, right? I get an error which suggests that it doesn't: mpi=# update index set level = 2 where parentid = ( select id from index where level = 1 ); ERROR: More than one tuple returned by a subselect used as an expression. mpi=# This is a recursive query, on a single table. Is that not possible? I am using 7.1. - Frank
Re: [SQL] Subqueries in Non-SELECT Queries
Frank Joerdens wrote: > mpi=# update index set level = 2 where parentid = ( select id from > index where level = 1 ); > ERROR: More than one tuple returned by a subselect used as an > expression. mpi=# > > This is a recursive query, on a single table. Is that not possible? Hi Frank, as the error message says, the subselect must return only one value, since the where clause compares just one field for equality. You might try IN instead of =, ie update index set level = 2 where parentid in ( select id from index where level = 1 ); Hope that helps. Christof -- gl.aser . software engineering . internet service http://gl.aser.de/ . Planckstraße 7 . D-39104 Magdeburg Tel. +49.391.7 44 77 10 . Fax +49.391.7 44 77 13 . Mobil 0177.77 92 84 3
Re: [SQL] Subqueries in Non-SELECT Queries
Frank Joerdens <[EMAIL PROTECTED]> writes: > mpi=# update index set level = 2 where parentid = ( select id from > index where level = 1 ); > ERROR: More than one tuple returned by a subselect used as an expression. Apparently the subquery "select id from index where level = 1" is returning more than one row. Perhaps you want WHERE parentid IN subselect rather than WHERE parentid = subselect. "=" is a scalar operator, not a set operator. regards, tom lane
[SQL] PostgreSQL crashes on me :(
Hi all, I've been running pgsql database since 6.3 with sizes ranging from a few megabytes to a few hundred megabytes. And ever since 6.5 came out I've had almost no crashes. Until now. We recently installed a small server for an external party to develop websites on. This machine, a K6-233 with 256 MB, is running FreeBSD 3.3 and PostgreSQL 7.0.2 (maybe I'll upgrade to 7.0.3 tonight). The database it's running is about 2 MB in size and gets to process an estimated 1 to 25000 queries per day. Nothing special, I'd say. However, pgsql keeps crashing. It can take days, but pgsql will crash. It spits out the following error: ServerLoop: select failed: No child processes I've done some searching with Google and I've searched the mailinglist archives on the PostgreSQL site, but I couldn't turn up anything useful. I did find a post from someone with the same problem, but appearently nobody ever replyed to that post. Does anyone have any idea what might be going on here? Should I be asking this question on another list? If so, which one? Any information would be very welcome, Mathijs -- "A book is a fragile creature. It suffers the wear of time, it fears rodents, the elements, clumsy hands." Umberto Eco
[SQL] Don't understand creation statement's answer
Hi,
In the long term I'm willing to construct a Little Java application that
runs a library. I was looking to create a few table & connect them
according to a non existing model . To create the Author table here's
what I thought doing for a table that should only contain The Name & the
Surname of the Author (once per Author).
tsmets=> create sequence author_seq;
CREATE
tsmets=> create table author (
tsmets=> author_id numeric primary key default
nextval('author_seq'),
tsmets=> name char(25) not null,
tsmets=> surname char(25) not null
tsmets=> );
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index
'author_pkey' for table 'author'
CREATE
tsmets=> \d author
Table "author"
Attribute | Type | Modifier
---+---+--
author_id | numeric(30,6) | not null default
nextval('author_seq'::text)
name | char(25) | not null
surname | char(25) | not null
Index: author_pkey
tsmets=>
Now the question is :
What is the meaning of the following statement in the table description
?
Attribute | Type | Modifier
---+---+--
author_id | numeric(30,6) | not null default
nextval('author_seq'::text)
Why was my author_id changesdin a "nextval('author_seq'::text)".
There's no text here ?
That for highlighting the meaning of this ?
Thomas,
--
Sun Dec 17 19:42:31 CET 2000
Thomas SMETSe-mail : [EMAIL PROTECTED]
Av. de la Brabançonne 133 / 3 Tel. : +32 (0)2 742. 05. 94.
1030 Bruxelles
=== Quote of the Day =
If at first you don't succeed, you must be a programmer.
= End of Quote ===
Re: [SQL] Don't understand creation statement's answer
On Mon, Dec 18, 2000 at 12:32:54AM +0100, Thomas SMETS allegedly wrote:
> tsmets=> create sequence author_seq;
> CREATE
>
> tsmets=> create table author (
> tsmets=> author_id numeric primary key default
> nextval('author_seq'),
> tsmets=> name char(25) not null,
> tsmets=> surname char(25) not null
> tsmets=> );
> NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index
> 'author_pkey' for table 'author'
> Now the question is :
> What is the meaning of the following statement in the table description
> ?
>
> Attribute | Type | Modifier
> ---+---+--
> author_id | numeric(30,6) | not null default
> nextval('author_seq'::text)
>
> Why was my author_id changesdin a "nextval('author_seq'::text)".
> There's no text here ?
nextval takes one parameter of the type text. It's just an explicit typecast.
The output of nextval is a number, as you requested.
I hope this helps,
Mathijs
--
"Books constitute capital."
Thomas Jefferson
[SQL] sql/database admin courses
hi, I looking for SQL Server Management course in the Northern California 1-2 mo length. Please advise.. Thanks, Dan Vanags Isis New Media (415) 885 5512 [EMAIL PROTECTED]
