Re: [GENERAL] UPDATE/DELETE with ORDER BY and LIMIT

[Bartlomiej Korupczynski]

On Sat, 25 Sep 2010 12:32:55 +0200
Alban Hertroys  wrote:

> On 24 Sep 2010, at 21:20, Bartłomiej Korupczyński wrote:
> 
> > Hi guys,
> > 
> > I've found some posts from 2007 about UPDATE/DELETE ... LIMIT N syntax
> > and I'd like to raise it again. Current PostgreSQL of UPDATE/DELETE
> > implement RETURNING statement, so extending by ORDER and LIMIT would be
> > really useful.
> 
> > All that with just one query. In this specific example, the ORDER BY
> > statement could be even omitted if we don't care how slots are
> > distributed between users.
> 
> This probably came up in the discussion from back then as well, but what 
> stops you from using a sub-select?
> 
> UPDATE slots
>   FROM (SELECT id FROM slots WHERE user IS NULL
>   ORDER BY id LIMIT 1) AS available
>SET user='joe'
>  WHERE id = available.id
>  RETURNING *;
> 
> Admittedly that's longer and would be slightly less efficient, but it is 
> available now (and has been for a while) and it's still in one query.

Well, it's not that anything can stop me ;)

It's just a suggestion. I think that:
1. UPDATE ... LIMIT approach is more obvious (= more clear for people to read)
2. as you said -- it's shorter and more efficient, even if it's just a little 
bit (one index scan less, if id was indexed).


> Also:
> > CREATE TABLE slots (
> > id INTEGER UNIQUE NOT NULL,
> > user VARCHAR(32),
> > expires TIMESTAMP WITH TIMEZONE,
> > -- some other columns
> > );
> 
> 
> I'd declare a primary key as what it is, not as some generic UNIQUE NOT NULL 
> column ;) It won't make much difference in practice, but for example, that 
> way it's intended use is immediately clear from the table definition if 
> people look it up.

It was just a quick and dirty example, but of course you're right :)


Regards,
BK

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to handle results with column names clash

[Bartlomiej Korupczynski]

Hi,

I'm curious how do you handle results from multiple tables with
repeated column names. For example:

# CREATE TABLE c1 (id integer PRIMARY KEY, address inet);
# CREATE TABLE c2 (id integer PRIMARY KEY, address text);
# SELECT * FROM c1 JOIN c2 USING (id);
 id | address | address 
+-+-
(0 rows)
or:
# SELECT * FROM c1, c2 WHERE c1.id=c2.id;
 id | address | id | address 
+-++-
(0 rows)

Now lets say we want access results from PHP/perl/etc using column
names. We have "address" from c1, and the same from c2. We can't even
distinguish which one is from which table.

I see two available possibilities:
1. rename one or each column (eg. prefix with table name), but it's not
always acceptable and makes JOIN ... USING syntax useless (and it's
messy to change to JOIN .. ON for many columns), it would also not work
if we join on the same table twice or more,
2. select each column explicitly:
  SELECT c1.id, c1.address AS c1_address, c2.address AS c2.address
but this is nightmare for tables with many columns, especially if the
schema changes frequently.

Someone could say, that if we JOIN on some column, then it's the same
value, but it does not need to be always true -- we can join on
different columns in different queries.

Any other ideas?


3. Suggestion, but it would be probably hard to implement: to make SQL
engine prefix each returned column with table alias. Of course it would
not be a default behavior, but it would be enabled by some session wide
setting.

# SELECT * FROM c1, c2 WHERE c1.id=c2.id;
 c1.id | c1.address | c2.id | c2.address
[...]
# SELECT * FROM c1 JOIN c2 USING (id);
 ??id | c1.address | c2.address

As JOIN returns only one copy of id, it would be hard to decide about
results (could return one copy for each alias like above).

4. Probably also hard to implement, something like:
# SELECT c1.* AS c1_*, c2.* AS c2_* FROM ...


Or maybe 3 or 4 are already there?


Regards,
BK

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general