Thread: Question insert data

Question insert data

From
perico@12move.nl
Date:
Hi There,

I've a problem with inserting data and I can't figure out what the problem
is:


what did I do:


1. I've a master table containing about 40000 records. A count(*) provides
me the exact number.

2. I've create a table based on from the master. I copied a fraction from
the master into the new table using a where clause (insert into ... select
* from ... where a = b . The number of records copied is about 2553.

Issueing the same select statement on the master table gives me a list of
5106 which is twice the number of copied records.

Did someone have this problem before?

I'm using 8.0 Windows XP en the pgAdmin III , r1.2.0

I even tried the same on the command line!

thanks,
Peter




_____________________________________________________________________

12move ADSL vanaf 18,95 euro! GRATIS modem, GEEN aansluitkosten en GEEN datalimiet!
Ga nu naar http://adsl.12move.nl




Re: Question insert data

From
Ragnar Hafstað
Date:
On Sat, 2005-03-19 at 18:36 +0100, perico@12move.nl wrote:

> 2. I've create a table based on from the master. I copied a fraction from
> the master into the new table using a where clause (insert into ... select
> * from ... where a = b . The number of records copied is about 2553.
>
> Issueing the same select statement on the master table gives me a list of
> 5106 which is twice the number of copied records.

sounds like you inserted into the master table instead of the new table.
can you show us the exact SQL you used?

or even better, can you make this happen in a simple test case?

gnari



Betr: Re: Question insert data

From
perico@12move.nl
Date:
insert into master_nl
select * from master where substr(loc,1,2) = 'NL'

>-- Oorspronkelijk bericht --
>Subject: Re: [GENERAL] Question insert data
>From: Ragnar Hafstað <gnari@simnet.is>
>To: perico@12move.nl
>Cc: pgsql-general@postgresql.org
>Date: Sat, 19 Mar 2005 17:56:52 +0000
>
>
>On Sat, 2005-03-19 at 18:36 +0100, perico@12move.nl wrote:
>
>> 2. I've create a table based on from the master. I copied a fraction from
>> the master into the new table using a where clause (insert into ... select
>> * from ... where a = b . The number of records copied is about 2553.
>>
>> Issueing the same select statement on the master table gives me a list
>of
>> 5106 which is twice the number of copied records.
>
>sounds like you inserted into the master table instead of the new table.
>can you show us the exact SQL you used?
>
>or even better, can you make this happen in a simple test case?
>
>gnari
>
>


_____________________________________________________________________

12move ADSL vanaf 18,95 euro! GRATIS modem, GEEN aansluitkosten en GEEN datalimiet!
Ga nu naar http://adsl.12move.nl




Betr: Re: Question insert data

From
perico@12move.nl
Date:
Some more info

select count(*) from master_bart where substr(locode_c5,1,2) =  'NL'

gives me the result: 5106


delete from masterplts_nl;

insert into masterplts_nl
select * from master_bart where substr(locode_c5,1,2) =  'NL';

Query returned successfully: 2553 rows affected, 5127 ms execution time.


>-- Oorspronkelijk bericht --
>Subject: Re: [GENERAL] Question insert data
>From: Ragnar Hafstað <gnari@simnet.is>
>To: perico@12move.nl
>Cc: pgsql-general@postgresql.org
>Date: Sat, 19 Mar 2005 17:56:52 +0000
>
>
>On Sat, 2005-03-19 at 18:36 +0100, perico@12move.nl wrote:
>
>> 2. I've create a table based on from the master. I copied a fraction from
>> the master into the new table using a where clause (insert into ... select
>> * from ... where a = b . The number of records copied is about 2553.
>>
>> Issueing the same select statement on the master table gives me a list
>of
>> 5106 which is twice the number of copied records.
>
>sounds like you inserted into the master table instead of the new table.
>can you show us the exact SQL you used?
>
>or even better, can you make this happen in a simple test case?
>
>gnari
>
>


_____________________________________________________________________

12move ADSL vanaf 18,95 euro! GRATIS modem, GEEN aansluitkosten en GEEN datalimiet!
Ga nu naar http://adsl.12move.nl




Re: Question insert data

From
Stephan Szabo
Date:
On Sat, 19 Mar 2005 perico@12move.nl wrote:

> Hi There,
>
> I've a problem with inserting data and I can't figure out what the problem
> is:
>
>
> what did I do:
>
>
> 1. I've a master table containing about 40000 records. A count(*) provides
> me the exact number.
>
> 2. I've create a table based on from the master. I copied a fraction from
> the master into the new table using a where clause (insert into ... select
> * from ... where a = b . The number of records copied is about 2553.

Define "based on". Do you mean CREATE TABLE AS or perhaps that the new
table inherits from the old one?  If the latter, you will see the rows in
the parent as well as the child if you do a select from the parent.

Betr: Re: Question insert data

From
perico@12move.nl
Date:
That's what I tought the problem was, but I created  a table afterwards without
inheritence. Could it have something to do with the max size of the schema
or oid's?


>-- Oorspronkelijk bericht --
>Date: Sat, 19 Mar 2005 14:55:50 -0800 (PST)
>From: Stephan Szabo <sszabo@megazone.bigpanda.com>
>To: perico@12move.nl
>Cc: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Question insert data
>
>
>On Sat, 19 Mar 2005 perico@12move.nl wrote:
>
>> Hi There,
>>
>> I've a problem with inserting data and I can't figure out what the problem
>> is:
>>
>>
>> what did I do:
>>
>>
>> 1. I've a master table containing about 40000 records. A count(*) provides
>> me the exact number.
>>
>> 2. I've create a table based on from the master. I copied a fraction from
>> the master into the new table using a where clause (insert into ... select
>> * from ... where a = b . The number of records copied is about 2553.
>
>Define "based on". Do you mean CREATE TABLE AS or perhaps that the new
>table inherits from the old one?  If the latter, you will see the rows in
>the parent as well as the child if you do a select from the parent.
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster


_____________________________________________________________________

12move ADSL vanaf 18,95 euro! GRATIS modem, GEEN aansluitkosten en GEEN datalimiet!
Ga nu naar http://adsl.12move.nl




Re: Betr: Re: Question insert data

From
Stephan Szabo
Date:
On Sun, 20 Mar 2005 perico@12move.nl wrote:

> That's what I tought the problem was, but I created  a table afterwards without
> inheritence. Could it have something to do with the max size of the schema
> or oid's?

I can't think of a reason it would, so can you send a self-contained
full example?

Betr: Re: Betr: Re: Question insert data

From
perico@12move.nl
Date:
Hi Stephan,

I figured out what happened:

The master table contained duplicates, but the insert statement seems to
be very smart by selecting just the unique ones.

Peter
>-- Oorspronkelijk bericht --
>Date: Sun, 20 Mar 2005 01:46:19 -0800 (PST)
>From: Stephan Szabo <sszabo@megazone.bigpanda.com>
>To: perico@12move.nl
>Cc: pgsql-general@postgresql.org
>Subject: Re: Betr: Re: [GENERAL] Question insert data
>
>
>On Sun, 20 Mar 2005 perico@12move.nl wrote:
>
>> That's what I tought the problem was, but I created  a table afterwards
>without
>> inheritence. Could it have something to do with the max size of the schema
>> or oid's?
>
>I can't think of a reason it would, so can you send a self-contained
>full example?
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster


_____________________________________________________________________

12move ADSL vanaf 18,95 euro! GRATIS modem, GEEN aansluitkosten en GEEN datalimiet!
Ga nu naar http://adsl.12move.nl




Re: Question insert data

From
Marcin Piotr Grondecki
Date:
Dnia Sat, Mar 19, 2005 at 06:36:27PM +0100, perico@12move.nl napisal(a/o):
> 1. I've a master table containing about 40000 records. A count(*) provides
> me the exact number.
> 2. I've create a table based on from the master. I copied a fraction from
> the master into the new table using a where clause (insert into ... select
> * from ... where a = b . The number of records copied is about 2553.
> Issueing the same select statement on the master table gives me a list of
> 5106 which is twice the number of copied records.
select .... only .... (read queries-table-expressions.html from PostgreSQL
documentation, then read rest of documentation).

> Did someone have this problem before?
> I'm using 8.0 Windows XP en the pgAdmin III , r1.2.0
I don't have any XP problem ;> (sorry, lame joke)

> I even tried the same on the command line!
me too :>>>>
--
ojciec