Обсуждение: Alter Table Command Rearranges Rows

I noticed that my database was in order based on my primary key column
called 'id' which when from 1 (first) to 6 (last). Today I had to edit
table data which wasn't anything crazy:

team=#ALTER users SET name = 'David' WHERE id = '1';
UPDATE 1

Now when I do a 'SELECT * FROM users' command in PostgreSQL, my row
that I altered column data in has been dropped all the way to the
bottom. This is extremely messy and annoying for me and I was
wondering if this is normal behavior for PostgreSQL? I could
understand that if remove the row and then re-added it, I would expect
it to add a new row to the bottom of the table.

Thanks for any assistance or clarification.

-Carlos

On Fri, Sep 17, 2010 at 4:12 PM, Carlos Mennens
<carlos.mennens@gmail.com> wrote:
> Thanks for any assistance or clarification.
>

Rows in SQL are unordered.  If you want an ordering, specify one on your SELECT.

On Sep 17, 2010, at 16:12 , Carlos Mennens wrote:

> I noticed that my database was in order based on my primary key column
> called 'id' which when from 1 (first) to 6 (last). Today I had to edit
> table data which wasn't anything crazy:
>
> team=#ALTER users SET name = 'David' WHERE id = '1';
> UPDATE 1

This isn't valid syntax: I believe you issued UPDATE users....

Postgres (nor any other SQL RDBMS) does not guarantee row order unless you specify it with an ORDER BY clause.

Michael Glaesemann
grzm seespotcode net

On Fri, Sep 17, 2010 at 1:32 PM, Michael Glaesemann
<grzm@seespotcode.net> wrote:

> Postgres (nor any other SQL RDBMS) does not guarantee row order unless you specify it with an ORDER BY clause.

This is true, but some database will maintain a tables clustering.
MS-Access comes to mind.  I don't know if MySQL does this also.

In PostgreSQL you can issue a periodic cluster command on the primary
key.  But as mentioned it is a bad practice to rely on the physical
ordering of the table.

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On Fri, Sep 17, 2010 at 4:32 PM, Michael Glaesemann
<grzm@seespotcode.net> wrote:
> This isn't valid syntax: I believe you issued UPDATE users....

Woops. I did use the UPDATE and not ALTER command.

On Fri, Sep 17, 2010 at 4:39 PM, Richard Broersma
<richard.broersma@gmail.com> wrote:
> On Fri, Sep 17, 2010 at 1:32 PM, Michael Glaesemann
> <grzm@seespotcode.net> wrote:
>
>> Postgres (nor any other SQL RDBMS) does not guarantee row order unless you specify it with an ORDER BY clause.
>
> This is true, but some database will maintain a tables clustering.
> MS-Access comes to mind.  I don't know if MySQL does this also.

MySQL does but I am fine with just running the ORDER BY command when I
use SELECT.

-Carlos