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

Thank you very much for this explanation/reply.  It precisely answers
my question.

Unfortunately, it prompts a new question.  I am using 8.4.2 which I
assume is new enough to trigger a "yes" response to "If you have a
version new enough to have synchronize_seqscans...".  I have
absolutely no idea how to turn that off.  Perhaps the best thing would
be to direct me to the documentation where turning it off is described
so that I can become more autonomous.  However, accompanying that with
explicit directions would be welcome too.

I am in Greenwich +1 timezone, but I fear you are in the 2AM time
zone.  Thank you again,

John


On Feb 24, 2010, at 8:06 AM, Tom Lane wrote:

> John Gage <jsmgage@numericable.fr> writes:
>> 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?
>
> If you have a version new enough to have synchronize_seqscans, you'd
> need to turn that off.  Otherwise should be OK.
>
>> 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 think the trick is to get the SRF to be expanded before the serial
> values are assigned.  There's more than one way to do it, but I think
> (too tired to experiment) this would work:
>
>  CREATE TABLE new_table (id serial, subunits text);
>
>  INSERT INTO new_table(subunits) SELECT
> regexp_split_to_table(source_text,
>    E'regexp') FROM source_table;
>
>             regards, tom lane