SEO

#1 (**permalink**) 04-09-2008, 05:58 AM

I just wanted to confirm that the COPY command always stores data in the
table in the order in which it appears in the import file.

I.e., if the import file is sorted ahead of time, am I correct in
assuming that the COPY command can have the same effect as CLUSTER'ing
(or as creating a new table as an ordered select on the loaded table)?
(Indexes would of course be applied after the data load.)

Thanks,
Kevin Murphy

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

#2 (**permalink**) 04-09-2008, 05:58 AM

Kevin Murphy wrote:
> I just wanted to confirm that the COPY command always stores data in the
> table in the order in which it appears in the import file.

This is not the case -- depending on the content of the FSM, the newly
added rows might be distributed throughout the table.

-Neil

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

#3 (**permalink**) 04-09-2008, 05:58 AM

Neil Conway wrote:

> Kevin Murphy wrote:
>
>> I just wanted to confirm that the COPY command always stores data in
>> the table in the order in which it appears in the import file.
>
>
> This is not the case -- depending on the content of the FSM, the newly
> added rows might be distributed throughout the table.

How about for a freshly created, empty table -- I should have qualified
my original statement.

Thanks,
Kevin

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

#4 (**permalink**) 04-09-2008, 05:58 AM

On Thu, Sep 15, 2005 at 05:09:51PM -0400, Kevin Murphy wrote:
> Neil Conway wrote:
>
> >Kevin Murphy wrote:
> >
> >>I just wanted to confirm that the COPY command always stores data in
> >>the table in the order in which it appears in the import file.
> >
> >This is not the case -- depending on the content of the FSM, the newly
> >added rows might be distributed throughout the table.
>
> How about for a freshly created, empty table -- I should have qualified
> my original statement.

In that particular case, the answer is yes. As is if you TRUNCATE the
table just prior to the COPY.

--
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
"El destino baraja y nosotros jugamos" (A. Schopenhauer)

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

CLUSTER equivalent