Row ordering after CREATE TABLE AS...SELECT regexp_split_to_table(source_text, regexp) AS new_column

From: John Gage <jsmgage(at)numericable(dot)fr>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Row ordering after CREATE TABLE AS...SELECT regexp_split_to_table(source_text, regexp) AS new_column
Date: 2010-02-24 06:51:54
Message-ID: 96D8D908-9469-4437-B175-9D24A5AD9743@numericable.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

This is a two-part question:

1) I have a source_text that I want to divide into smaller subunits
that will be contained in rows in a column in a new table. Is it
absolutely certain that the initial order of the rows in the resultant
table after this operation:

CREATE TABLE new_table AS SELECT regexp_split_to_table(source_text,
E'regexp') as subunits FROM source_table;

will be the same as the order of these subunits in the original text?
Emphasis *initial order*.

2) I would like to be able to create a serial-type column during
CREATE TABLE AS in the new table that "memorizes" this order so that I
can reconstruct the original text using ORDER BY on that serial
column. However, I am stumped how to do that. I do not see how to
put the name of that column into my SELECT statement which generates
the table, and I do not see where else to put it. Please forgive my
stupidity.

The "work-around" to this problem has been to ALTER my table after its
creation with a new serial-type column. But this assumes that the
answer to Question 1) above is always "Yes".

Thanking you for your understanding,

John

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bret S. Lambert 2010-02-24 06:58:00 Re: Row ordering after CREATE TABLE AS...SELECT regexp_split_to_table(source_text, regexp) AS new_column
Previous Message Jignesh Shah 2010-02-24 06:38:32 Re: Not able to change the owner of function