headers
Gary Stainburn | 1 May 2002 19:29
Picon

problem with update from subselect

Hi all, using the schema described below, I want to be able to update each 
arrival time from departure times and trip lengths.

However the update fails because the subselect returns all three answers.

How would I correct the update to make it work

update trip set trip_arrive = (select t.trip_depart + r.route_time
  from route r, trip t where r.routeid = t.trip_route);

Database definition:
drop table route;
drop table trip;
drop sequence route_id_seq;
drop sequence trip_id_seq;

create sequence "route_id_seq" start 1 increment 1;
create sequence "trip_id_seq" start 1 increment 1;
create table route (
  routeid int4 unique default nextval('route_id_seq'::text) not null,
  route_depart character (4),  -- std ICAO code e.g. EGNM
  route_dest   character (4),  -- ditto
  route_time   interval,
  primary key (routeid)
);

create table trip (
  tripid int4 unique default nextval('trip_id_seq'::text) not null,
  trip_route int4 references route(routeid),
  trip_depart timestamp,  -- departure time
(Continue reading)

Permalink | Reply | 2 comments |
headers
Nick Fankhauser | 1 May 2002 18:37

Re: convert from sybase to postgresql

Frank-

Try this link:

http://pgadmin.postgresql.org/pgadmin2.php?ContentID=15

You might find some other clues in the techdocs:

http://techdocs.postgresql.org/

-Nick

--------------------------------------------------------------------------
Nick Fankhauser  nickf <at> ontko.com  Phone 1.765.935.4283  Fax 1.765.962.9788
Ray Ontko & Co.     Software Consulting Services     http://www.ontko.com/

> -----Original Message-----
> From: pgsql-sql-owner <at> postgresql.org
> [mailto:pgsql-sql-owner <at> postgresql.org]On Behalf Of Frank Morton
> Sent: Monday, April 29, 2002 6:09 PM
> To: pgsql-sql <at> postgresql.org
> Subject: [SQL] convert from sybase to postgresql
> 
> 
> Anyone know of a utility to convert a sybase .db
> file to something postgres can use? If not, to convert
> to anything that I know the file format of to itself
> to convert into postgres. All preferably open-source.
> 
> Thanks.
(Continue reading)

Permalink | Reply | 0 comments |
headers
Travis Hoyt | 1 May 2002 18:51

Problems returning multiple columns

CREATE FUNCTION wklyavg(numeric, timestamp, timestamp) RETURNS TEXT AS '
         DECLARE
                 system          ALIAS FOR $1;
                 startdate       ALIAS FOR $2;
                 enddate         ALIAS FOR $3;
                 result          numeric;

         BEGIN
                 result := (select (avg(usr) + avg(sys)) from sardata
                        where systemid = system and time between startdate
and enddate);
                 return enddate || result;
         END;
' LANGUAGE 'plpgsql';

Above is the function I'm trying to run and I keep getting errors telling
me I need to cast my values.  I've played with it a bit but not knowing
much about casting I was hoping for some help.  I'm basically just summing
a couple of averages for values between two given dates.  I wanted to
return the summed average and the "enddate" as one row.  Any suggestions?

Thanks,

Travis
Attachment (smime.p7s): application/x-pkcs7-signature, 3080 bytes
Permalink | Reply | 0 comments |
headers
Stephan Szabo | 1 May 2002 18:55

Re: problem with update from subselect


On Wed, 1 May 2002, Gary Stainburn wrote:

> Hi all, using the schema described below, I want to be able to update each
> arrival time from departure times and trip lengths.
>
> However the update fails because the subselect returns all three answers.
>
> How would I correct the update to make it work
>
> update trip set trip_arrive = (select t.trip_depart + r.route_time
>   from route r, trip t where r.routeid = t.trip_route);

Do you really want to join with another "trip" in the subselect?

I think you probably want something like:
update trip set trip_arrive = (select trip.trip_depart + r.route_time
 from route r where r.routeid=trip.trip_route);

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo <at> postgresql.org so that your
message can get through to the mailing list cleanly
Permalink | Reply | 0 comments |
headers
Joe Conway | 1 May 2002 19:09
Gravatar

Re: problem with update from subselect

Gary Stainburn wrote:
> Hi all, using the schema described below, I want to be able to update each 
> arrival time from departure times and trip lengths.
> 
> However the update fails because the subselect returns all three answers.
> 
> How would I correct the update to make it work
> 
> update trip set trip_arrive = (select t.trip_depart + r.route_time
>   from route r, trip t where r.routeid = t.trip_route);
> 

If I understand what you're trying to do correctly, this works:

test=# update trip set trip_arrive = trip_depart + r.route_time from 
route r where r.routeid = trip.trip_route;              UPDATE 3
test=# select * from trip;
  tripid | trip_route |     trip_depart     |     trip_arrive
--------+------------+---------------------+---------------------
       1 |          1 | 2002-01-01 10:00:00 | 2002-01-01 11:40:00
       2 |          1 | 2002-02-01 11:30:00 | 2002-02-01 13:10:00
       3 |          2 | 2002-01-01 11:00:00 | 2002-01-01 11:30:00
(3 rows)

HTH,
Joe

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
(Continue reading)

Permalink | Reply | 0 comments |
headers
j.konzack | 1 May 2002 21:53
Picon
Favicon

CREATE VIEW question...

Hello there...

I have two databases, called db1 an db2. Database db1 contains a table
called memberlist.
Now I will create a view, called members, in database db2 (containig data
from table memberlist) with the following query:

CREATE VIEW members AS SELECT * FROM db1.memberlist;

->>> this error is displayed: ERROR; parser: error at or near "."

How can I create a view in one database with data from a second/different
database?

Jan Konzack

-----------------------------------------------------------------------
Riester-Rente - Kassieren Sie das Geld vom Staat:
Zum Förderungsrechner! http://www.arcor.de/home/redir.php/riesterrente
-----------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html
Permalink | Reply | 2 comments |
headers
Oliver Elphick | 1 May 2002 22:51
Picon

Re: CREATE VIEW question...

On Wed, 2002-05-01 at 20:53, j.konzack <at> arcor.de wrote:

> CREATE VIEW members AS SELECT * FROM db1.memberlist;
>   
> ->>> this error is displayed: ERROR; parser: error at or near "."
>    
> How can I create a view in one database with data from a second/different
> database?

You cannot.

When 7.3 comes out, you could implement this as separate schemas within
one database.

--

-- 
Oliver Elphick                                Oliver.Elphick <at> lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "For if ye forgive men their trespasses, your heavenly 
      Father will also forgive you; But if ye forgive not 
      men their trespasses, neither will your Father forgive
      your trespasses."         Matthew 6:14,15
Permalink | Reply | 0 comments |
headers
Joel Burton | 2 May 2002 05:17

Re: CREATE VIEW question...

At present, you cannot access data from another database, either with a
VIEW, or any other SQL statement.

You either want to: merge the two databases or dump/restore the needed table
to db2.

In PG 7.3 (still in development), namespaces have been added. While this
technically won't fix cross-database access, it will provide an excellent
solution to many tables in a database, and will provide an effective
solution to problems like this.

In the contrib/ directory (or using the contrib RPM, if that's your style),
you'll find "dblink", which provides a working but hackish way to access
other databases, but I'm not sure that it is ready for prime time.

HTH. Sorry for the bad news.

Joel BURTON | joel <at> joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

> -----Original Message-----
> From: pgsql-sql-owner <at> postgresql.org
> [mailto:pgsql-sql-owner <at> postgresql.org]On Behalf Of j.konzack <at> arcor.de
> Sent: Wednesday, May 01, 2002 3:53 PM
> To: pgsql-sql <at> postgresql.org
> Subject: [SQL] CREATE VIEW question...
>
>
> Hello there...
>
(Continue reading)

Permalink | Reply | 0 comments |
headers
Wolfgang.Fuertbauer | 2 May 2002 10:00

Re: join-performance problem


On 30.04.2002 18:44:19 pgsql-sql-owner wrote:
>Wolfgang.Fuertbauer <at> ebewe.com writes:
>>> explain SELECT b.Kdnr, date_part('year', b.Datum), d.Name, e.Name,
a.Menge,
>>> a.Rabproz, a.Netto - (a.netto * a.rabproz / 100),
>>> a.Netto * b.kurs - ((a.netto * b.kurs) * a.rabproz / 100), 'RG'
>>> FROM Faktzeilen a, Fakt b, Artikel c, Kollektion d, ArGruppen e
>>> where a.Fanr = b.nr
>>> and c.nr = a.Arnr
>>> and c.Kollektion = d.Nr
>>> and (c.Gruppe = e.nr or c.gruppe = 0)
>>> and b.kdnr = 49736;
>
>Anyplace that c.gruppe is 0, this is an unconstrained join to e --- ie,
>you'll get a row out for *every* row of e.  Somehow I doubt that's the
>behavior you really want.

you're absolutly right! I fixed that (by having an record in c with nr 0 -
what
is ok for the application)

>I concur with Stephan's observation that you haven't analyzed.  But
>even when you have, this query doesn't give much traction for the use
>of indexes on a --- the only constraint that might possibly be used to
>avoid a complete scan of a is the "b.kdnr = 49736", and that's not even
>on a.  The only hope I can see is if you create an index on b.kdnr;
>then (if there aren't very many rows matching b.kdnr = 49736), it might
>be able to pick those up with an indexscan on b and then do an inner
>indexscan join to a using a.Fanr = b.nr.
(Continue reading)

Permalink | Reply | 1 comment |
headers
Rajesh Kumar Mallah. | 2 May 2002 11:16

Weird select output...

Hi Folks,

when does the below happen?

tradein_clients=# SELECT  company_id from branding_master where 
company_id=58498;

 company_id
------------
      58498
      42136
(2 rows)

regards
Mallah.

--

-- 
Rajesh Kumar Mallah,
Developer www.trade-india.com
Infocom Network Limited, New Delhi.
Internet: mallah <at> trade-india.com
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo <at> postgresql.org so that your
message can get through to the mailing list cleanly
(Continue reading)

Permalink | Reply | 0 comments |

Gmane