2

For testing purposes I need to get some data from a set of database tables in any arbitrary (but reproducible) order. The idea being that I can later compare two runs using a textual diff tool. Is there an idiom for that?

E.g. I can obviously do a:

SELECT * FROM table_with_N_columns ORDER BY column_1, ... , column_N

I am just asking if there is an idiomatic way to achieve the same effect (for my purposes) without bothering to list every column in the ORDER BY clause. Any ordering will do so long as it is reproducible with subsequent runs of the query.

  • I assume you have a primary key (like every table should?) ... – Erwin Brandstetter Sep 6 '14 at 13:09
  • @ErwinBrandstetter I am trying to create a generic tool with as few assumptions about the tables as possible. Since the script is created automatically I can get column metadata and just ORDER BY all columns; this way I am avoiding the PK presence question. I am just asking if there is a more idiomatic way to request a repeatable ordering. – Marcus Junius Brutus Sep 6 '14 at 13:17
3

without bothering to list every column in the ORDER BY clause

For just that goal, ORDER BY tablename.* does work.

If there are several tables involved, we can always write:

SELECT * FROM (select ... complex query...) as T order by T.*;

If the resultset has many wide rows making the sort inefficient, sorting a hashed representation instead might be a better idea:

SELECT * FROM (select ... complex query...) as T order by md5(T.*::text);
  • t.* is a helpful syntax shorthand, but the approach is flawed for a couple of reasons. Hashing everything has close to no potential to fail (for this particular purpose), but is also potentially very expensive. – Erwin Brandstetter Sep 7 '14 at 0:15
6

Use PK columns

Sorting by all columns of a table is potentially (very) expensive or sometimes simply impossible. Example: table with a json column or xml, or point or one of many other data types which cannot be used in ORDER BY, lacking the necessary operators. You get an exception.

If it doesn't fail, it should work for your purpose, but it still would not guarantee a true stable sort order. Without unique or primary key, completely duplicate rows are allowed, which are still sorted arbitrarily. Two concurrent sessions could both change the "first" in a set of dupes without conflict, resulting in two different rows being changed separately.

Use the primary key column(s) of each table. Unique constraint would work, too, but you really should have a pk on every table to begin with. Besides being correct and guaranteed to work, it's also typically much faster.

Function

Query the system catalogs pg_constraint and pg_attribute to get columns for a PRIMARY KEY or UNIQUE constraint.

I wrapped the query in a handy SQL function:

CREATE OR REPLACE FUNCTION f_order_cols(_tbl regclass)
  RETURNS text AS
$func$
SELECT string_agg(_tbl::text || '.' || quote_ident(attname), ',')
FROM (
   SELECT conrelid AS attrelid, conkey
   FROM   pg_catalog.pg_constraint
   WHERE  conrelid = $1
   AND    contype = ANY ('{p,u}'::"char"[]) -- primary or unique constraint
   ORDER  BY contype                        -- pk has priority ('p' < 'u')
           , array_length(conkey, 1)        -- else, fewest columns as quick tie breaker
   LIMIT  1                                 -- ... but any 1 of them is good
   )   c
JOIN   unnest(c.conkey) attnum ON TRUE      -- unnest array, possibly mult. cols
JOIN   pg_catalog.pg_attribute a USING (attrelid, attnum)

UNION  ALL                                  -- Default if first query returns no row
SELECT _tbl::text || '::text COLLATE "C"'   -- See chapter "Default" below
LIMIT  1
$func$ LANGUAGE sql;
  • This is very fast.

  • The function takes the table name as parameter, type regclass to be precise. Resolves the name according to the current search_path and raises an exception if invalid. At the same time provides a properly quoted identifier in the result automatically.

  • Any PRIMARY KEY or UNIQUE constraint is good. PK has priority, else the unique constraint with the fewest columns is picked.

  • The constraint can consist of multiple columns, attribute numbers of which are stored in the array pg_constraint.conkey. Using an implicit JOIN LATERAL to unnest() that.

  • Result columns are table-qualified to get syntax ready for queries on multiple tables. This optional feature may have to be modified if you are using table aliases in queries.

  • Also quote column names correctly to avoid SQL injection. quote_ident() does that for you. More about that:

Default

If no constraint is found, the function returns NULL. Wrap the result into COALESCE and use the text representation of the whole row as default: tbl::text.

This is not going to sort rows like original values would, but you get a consistent sort order that works for every table. Since you are going to compare text representations anyway.
Better still, add COLLATE "C" to ignore collation rules. Faster, and just as good for your purpose.

ORDER BY tbl::text COLLATE "C"

Still a potentially very inefficient measure of last resort. Use PKs if at all possible. I put the default into the function above. Everything put together, for a query:

SELECT * FROM u JOIN p USING (a) JOIN n ON n.b = p.b ORDER BY ?

This returns the complete string for ?:

SELECT concat_ws(', ', f_order_cols('u')
                     , f_order_cols('p')
                     , f_order_cols('n')) AS order_by;

SQL Fiddle demonstrating all.

  • 1
    I can't profess to have understood all of that code in detail (far from a PostgreSQL expert), but I am breathless! You say that "Without unique or primary key, completely duplicate rows are allowed, which are still sorted arbitrarily". Am I correct in having understood that @DanielVérité 's solution would also suffer from the same issue? +1 - if I could have awarded +10, I would have. – Vérace Sep 7 '14 at 3:10
  • 1
    This is really impressive (+1); the reason I am sticking with the accepted answer is that for my use case ORDER by t.* was the idiom I was looking for; also I generate the test script using JDBC (examining the java.sql.DatabaseMetadata) instead of querying the pg_constraint table. Finally, complete duplicate rows don't bother me as I am just doing a textual diff of the dumps once the tests are finished and there's no more active sessions. So one way or the other no diff will come up. Thanks for the great insights. – Marcus Junius Brutus Sep 7 '14 at 11:40
  • 1
    @MarcusJuniusBrutus: I had another idea to improve performance (COLLATE "C") and built it into the function. If it's not for your present case, it may serve others with similar problems. – Erwin Brandstetter Sep 8 '14 at 1:41
  • 1
    @Vérace: Yes, Daniel's code suffers from the same. As long as one only compares text output of visible columns, it doesn't make a difference. In other situations (like in my example) it might. – Erwin Brandstetter Sep 9 '14 at 22:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.