Re: Inserting data of two other tables

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: papapep <papapep(at)gmx(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Inserting data of two other tables
Date: 2003-05-23 19:31:40
Message-ID: 17966.1053718300@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

papapep <papapep(at)gmx(dot)net> writes:
> INSERT INTO totaltrajectes (turno, condu, linea, fecha, nserie,
> nseriechip,tipotrbus,paraor,parade,hora1hora,hora1min,pasaindi,numvez,clase,
> tarifa,tarneto,tarbruto,formapago,texto,journey,salreca) SELECT
> turno,condu,linea,fecha,nserie,nseriechip,tipotrbus,paraor,parade,
> hora1hora,hora1min,pasaindi,numvez,clase,tarifa,tarneto,tarbruto,
> formapago,texto,journey,salreca from captrajectes,detalltrajectes

er ... no WHERE clause?

> Also I have to mention that this sentence works with about 650.000 rows
> ~ in the table "detalltrajectes" and
> 35.000 in the "captrajectes" one.

With no WHERE clause, your SELECT is forming the unconstrained cross
product, with upwards of 22 billion rows. No wonder it takes awhile
(it'd likely run you out of disk space, too).

> Captrajectes is a header file and every row from this table refers to
> many rows of detalltrajectes, and they get linked with two fields that
> are identical. In captrajectes pkey is the primary key and in
> detalltrajectes journey is the field that refers to pkey letting us know
> to wich header every row belongs.

If that's the behavior you want, you need to say so with a WHERE clause.
Or JOIN ... ON ....

regards, tom lane

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Ben Schneider 2003-05-23 20:24:36 Case Insensitive Searching?
Previous Message Manfred Koizar 2003-05-23 19:14:59 Re: Inserting data of two other tables