Re: How to write such a query - Mailing list pgsql-general

From Jonathan Strong
Subject Re: How to write such a query
Date
Msg-id CAK8Y=HUTJA2O5Ce3k23n8LE14Fw0cO2Q9cTjGzbKAuXbEH5qow@mail.gmail.com
Whole thread Raw
In response to Re: How to write such a query  (Thomas Kellerer <shammat@gmx.net>)
List pgsql-general
Yes...absolutely. Short of using ORDER BY, the order of a multi-row result set can be arbitrary, with "row position" having no significant meaning.

This gets back to understanding set theory, the relational model, the various types of keys (primary, candidate, foreign, etc.). Truly crucial to understand the model in order to write correctly functioning and reliable code.

- Jon

    

Jonathan Strong

CIO / CTO / Consultant

P: 609-532-1715 E: jonathanrstrong@gmail.com

Quora Top Writer



On Fri, Sep 18, 2020 at 2:17 PM Thomas Kellerer <shammat@gmx.net> wrote:
Igor Korot schrieb am 18.09.2020 um 19:29:
> [code]
> CREATE TABLE X(id INTEGER PRIMARY KEY, field1 char(50), field2 int);
> CREATE TABLE Y(id INTEGER PRIMARY KEY, field1 char, field2 double(10, 2));
> SELECT X.field1, Y.field2 from X, Y WHERE X.id = Y.id;
> [/code]
>
> Assuming that the SELECT return 10 rows, I want to update X.field1
> in row 5.

There is no such thing as "row 5" in a relational database.

Rows in a table have no inherent sort order. The only way you can identify
a row, is by the value of its primary (or unique) key. Not by "position".

The only way you can identify "row 5" is, if you use an ORDER BY to
define a sort order on the result - but that position is only valid
for that _result_, it has no meaning for the actual table data.

Which brings us back to the fact, that the only way to (uniquely) identify
a row in a table is to specify its primary key value in the WHERE clause





pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: How to write such a query
Next
From: Jonathan Strong
Date:
Subject: Re: How to write such a query