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

On Wed, Feb 24, 2010 at 07:51:54AM +0100, John Gage wrote:
> 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*.

I'd put money on not; this is not what databases are designed for.

>
> 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.

Pre- or append an increasing serial number to the data, and use that
as a column named "initial_order" or something else that will make
it clear to you and other users what it is, and then import.

But if you have the original data, in order, why do you need to be
able to reconstruct it from a database dump? It just looks like
adding a step to add a step, to me.

>
> 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general