I'm using 7.1.1 right now, and have the following table:
id | s
+---
1 | alpha
2 | beta
3 | gamma
4 | delta
(4 rows)
I'd like to switch the id of "beta" to 3 and the id of "gamma" to 2 ("flip"
them). Since id is the PK, it must remain unique and so I can't just set
the
I'd like to retrieve a row of a table that has the maximum ID. For example,
with:
id | s
+---
1 | alpha
2 | beta
3 | gamma
4 | delta
I'd like to get the row with ID=4. I've tried:
SELECT * FROM mytable WHERE id=(SELECT MAX(id) FROM mytable);
The subquery can take a /r
>=> explain select id from mytable order by seed desc limit 1;
Oops, a cut & paste mistake. That should be:
explain select id from mytable order by id desc limit 1;
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.
At 07:31 PM 6/7/2001 +0200, Peter Eisentraut wrote:
> > SELECT * FROM mytable WHERE id=(SELECT MAX(id) FROM mytable);
> > SELECT * FROM mytable ORDER BY id DESC LIMIT 1;
>The second is generally thought to be faster, at least if you use the
>latest version of PostgreSQL.
This is quite amusing ac
At 10:39 AM 6/7/2001 -0700, Stephan Szabo wrote:
>Not exactly. It's a bug in the implementation of the unique constraint.
>The unique constraint is being checked per-row rather than per-statement.
Is this bug on a todo list, or should I submit a bug report?
---(end of
I'm using 7.1.1 right now, and have the following table:
id | s
+---
1 | alpha
2 | beta
3 | gamma
4 | delta
(4 rows)
I'd like to switch the id of "beta" to 3 and the id of "gamma" to 2 ("flip"
them). Since id is the PK, it must remain unique and so I can't just set
the
I'd like to retrieve a row of a table that has the maximum ID. For example,
with:
id | s
+---
1 | alpha
2 | beta
3 | gamma
4 | delta
I'd like to get the row with ID=4. I've tried:
SELECT * FROM mytable WHERE id=(SELECT MAX(id) FROM mytable);
The subquery can take a /r
I think you're just witnessing the optimizer at work. If it thinks that
doing sequential scans is faster, it will ignore the indices.
At 11:03 AM 5/31/2001 +0200, Koen Antonissen wrote:
>Thing I descovered after i posted to the group was that after creating
>the scheme again, the indexes are us
That list looks like an ordering that is custom to your application. The
latter sort created by the SQL seems more typical (correct?) to me.
I would return the table unsorted, and write the sorting routine in the
calling code instead.
-Original Message-
From: [EMAIL PROTECTED]
[mailto
Hi all. We have a table called DeckEJB with the follow structure and
associates indices as indicated below. Our application makes use of this
table by doing INSERTs into it, and then after every 741 inserts, one
DELETE. We are not SELECTing from the table in our test, though in
production use SEL
Hi all.
I have a table with about 5 million rows in it. I need to be able to get the
exact number of rows in the table at runtime. So I tried the following:
xxx=> explain select count(seed) from mytable;
NOTICE: QUERY PLAN:
Aggregate (cost=103152.27..103152.27 rows=1 width=4)
-> Seq Scan o
It seems to just feel like conflicting requirements, so it's a tug-of-war.
I've always done it by doing all the processing I can and then, from inside
a transaction, do
update seed from seed_table set seed=seed+1 where id='abc';
insert into some_table values ((select seed from seed_table where
I've been looking into indices and which ones to create and I'm getting
myself a little confused. The "PostgreSQL Introduction and Concepts" book
didn't help very much. I wonder if a kind soul can give me some tips.
SELECT * FROM T1 WHERE a=1 and b='hello';
Is the appropriate index for this que
Perhaps you can run your file through a filter to replace '' to null.
Any of sed, perl, python and other scripting languages would do nicely.
e.g.
sed -e "s/''/null/g" < myfile > myfile2
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Maurizio Ortola
Hi all.
I'm trying to track down a deadlock problem caused by some automatically
generated SQL code. It seems I'm deadlocking on a table that is actually
quite rarely modified. I'm unsure, but it appears that maybe something is
blocking on a SELECT call.
Under what situations can a SELECT block
What does "=P0] Ick ,O$0;r7N+d, ,O$0;r&r*:AYmailto:[EMAIL PROTECTED]]On Behalf Of S.F. Lee
Sent: Monday, March 26, 2001 5:40 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: [SQL] AY wrote:
> > I'm using 7.1 Beta 3, which has been pretty
> stable up until now. This
> > morning, I went
>There is an undocumented little factoid here: CREATE INDEX will update
>(some of) the planner stats, but only if it finds some data in the
>table. CREATE INDEX on an empty table leaves the initial default
>numbers alone. This may be contributing to your confusion, but it was
>deemed necessary
e | Type | Owner
--+---+---
t1_a_ndx | index | gutz
t2_a_ndx | index | gutz
(1 row)
test1=> \d t2_a_ndx
Index "t2_a_ndx"
Attribute | Type
---+---
a | varchar()
btree
At 11:42 AM 3/15/2001 -0800, Gerald Gutierrez wrote:
>I'm confused over two
I'm confused over two question involving PostgreSQL index scans. I'm using
Linux Mandrake 7.2 on Intel, and PostgreSQL 7.0.3. If someone can explain
what's going on, I'd greatly appreciate it.
-
1) When I create a empty table, and then immediate cre
I've found the discussion to which you have referred:
http://www.postgresql.org/mhonarc/pgsql-sql/2001-02/msg00157.html
but was unable to find the documentation for the EXECUTE command. From the
discussion it seemed to me that doing an "INSERT" into a table of variable
name cannot be done now
I don't seem to be able to create tables (persistent or temporary) from
within a PL/PGSQL function. With the following script, I can create the
function fine:
CREATE FUNCTION tst()
RETURNS INTEGER
AS '
BEGIN
CREATE TABLE ttt(a int);
RETURN 0;
END;
'
LANGUAGE 'plpgsql';
... but when
I've written my Dijkstra's algorithm in PL/PGSQL. It didn't turn out to be
a big deal at all actually, programming-wise. I understand execution speed
will be poor but it shouldn't be any slower than having something else,
like PHP or Java, execute logic and query the database.
I'd like to gen
At 12:48 PM 3/2/2001 -0800, David Olbersen wrote:
>On Fri, 2 Mar 2001, Gerald Gutierrez wrote:
>
>->Recently I wanted to implement Dijkstra's algorithm as a stored procedure,
>->and finding that PL/PGSQL cannot return record sets, I thought about using
>->a tempor
I use PostgreSQL via a connection pooling mechanism, whether it be J2EE or
PHP. I've been able to achieve good performance this way, and it has been
good to me.
Recently I wanted to implement Dijkstra's algorithm as a stored procedure,
and finding that PL/PGSQL cannot return record sets, I th
24 matches
Mail list logo
