pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedInsert into and Sequence
2+ messages / 2 participants
[nested] [flat]
* Insert into and Sequence
@ 2007-12-17 19:30 Luigi Pirillo <luigi.pirillo@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Luigi Pirillo @ 2007-12-17 19:30 UTC (permalink / raw)
To: pgsql-sql
Hi to all,
executing an INSERT INTO like this:
INSERT INTO "_Tests_Steps"
(
"New_Test_Step_Id",
"Test_Step_Id",
"Process_Test_Id",
"Step_Id",
"Sequence_Id",
"StepGroup_Id",
"Skip"
)
SELECT
nextval('"Tests_Steps_Test_Step_Id_seq"'),
"Test_Step_Id",
B."Process_Test_Id",
"Step_Id",
"Sequence_Id",
"StepGroup_Id",
A."Skip"
FROM "_Processes_Tests" B INNER JOIN "Tests_Steps" A
ON A."Process_Test_Id" = B."Process_Test_Id"
WHERE "Version_Id" = vers_id
ORDER BY "Test_Step_Id";
...............
where "_Tests_Steps" and "_Processes_Tests" are both temporary
tables, in the resulting "_Tests_Steps table records are not sorted in
the expected mode.
In other word, I need to extract records from "Tests_Steps" and then put
them in "_Tests_Steps" ordered exactly as they were inserted first time.
This because I need to duplicate this records, assigning them a new
serial value. This records are later binding to others records of
different tables.
The resulting recordset is the follow:
"New_Test_Step_Id", "Test_Step_Id", "Process_Test_Id", "Step_Id",
"Sequence_Id", "StepGroup_Id", "Skip"
2332; 2136;
2225; 958; 404;
363; f
2331; 2137;
2225; 959; 404;
363; f
2334; 2285;
2225; 960; 404;
363; f
2333; 2286;
2225; 961; 404;
363; f
Apparently, new records are not inserted in the table according to the
order selected by "Test_Step_Id" field;
Any suggest??
Thanks in advance
Luigi Pirillo
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Insert into and Sequence
@ 2007-12-19 13:21 Ireneusz Pluta <ipluta@wp.pl>
parent: Luigi Pirillo <luigi.pirillo@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Ireneusz Pluta @ 2007-12-19 13:21 UTC (permalink / raw)
To: Luigi Pirillo <luigi.pirillo@gmail.com>; +Cc: pgsql-sql
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-15"
http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<blockquote cite="mid:4766CE38.6060904@gmail.com" type="cite"><tt><br>
Apparently, new records are not inserted in the table according to the
order selected by "Test_Step_Id" field;
<br>
</tt></blockquote>
<tt>As seen in: <a class="moz-txt-link-freetext" href="http://en.wikipedia.org/wiki/SQL:">http://en.wikipedia.org/wiki/SQL:</a><br;
"The order of rows returned by an SQL query is never guaranteed unless
an <code>ORDER BY</code> clause is specified".<br>
<br>
In other words, a database system does not guarantee you the order in
which table rows are stored. So, the order in which you insert your
rows is not the one which has to be followed when selecting them
afterwards. You must specify by "ORDER BY" how you want to sort your
recordset.<br>
<br>
It seems you need to revisit the concept of your system.<br>
<br>
Best regards<br>
<br>
Irek<br>
</tt><br>
</body>
</html>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-12-19 13:21 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-17 19:30 Insert into and Sequence Luigi Pirillo <luigi.pirillo@gmail.com>
2007-12-19 13:21 ` Ireneusz Pluta <ipluta@wp.pl>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox