| Lists: | pgsql-sql |
|---|
| From: | Luigi Pirillo <luigi(dot)pirillo(at)gmail(dot)com> |
|---|---|
| To: | pgsql-sql(at)postgresql(dot)org |
| Subject: | Insert into and Sequence |
| Date: | 2007-12-17 19:30:00 |
| Message-ID: | 4766CE38.6060904@gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | 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
| From: | Ireneusz Pluta <ipluta(at)wp(dot)pl> |
|---|---|
| To: | Luigi Pirillo <luigi(dot)pirillo(at)gmail(dot)com> |
| Cc: | pgsql-sql(at)postgresql(dot)org |
| Subject: | Re: Insert into and Sequence |
| Date: | 2007-12-19 13:21:29 |
| Message-ID: | 47691AD9.5070704@wp.pl |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | 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(dot)6060904(at)gmail(dot)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>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 1.1 KB |