Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I want to create a table from a SELECT statement based on a multiple row DISTINCT, but PostgreSQL does not allow me to do it.

I need to create a table where the elements were previously selected from another table and they are all unique.

Here's my try:

CREATE TABLE new_name AS (

   SELECT DISTINCT(table.field1, table.field2), table.field1, table.field2, 
   FROM ...
   WHERE ...

);

If I run this statement, PostgreSQL says:

ERROR: column "row" has pseudo-type record

How could I solve this problem?

share|improve this question

1 Answer

up vote 3 down vote accepted

distinct is NOT a function. It always operates on all columns of the result.

The difference between select distinct (foo), bar and select distinct foo, bar is the same as the difference between select (foo), bar and select foo, bar. The paranthesis is just "noise".

When you write select (foo,bar) you are actually creating an anonymous object type in Postgres which results in a single column that has two attributes - which is not what you actually want.

As you are using Postgres, you can use the Postgres specific extension DISTINCT ON which - in contrast to the standard DISTINCT does operate on a sub-set of the columns.

You have to specfiy an ORDER BY in that case to "define" which of the rows to take if there is more than one with the same combination of (field1, field2).

CREATE TABLE new_name 
AS 
SELECT DISTINCT ON (table.field1, table.field2), 
       table.field1, 
       table.field2, 
       .....
FROM ...
WHERE ...
ORDER BY ..

If you want to stick to ANSI SQL you will need a window function for this:

create table new_name
as
select column1, column2, column3, column4, column5, column6
from (
   select column1, column2, column3, column4, column5, column6, 
          row_number() over (partition by column1, column2 order by ...) as rn 
   from the_table
   where ...
) t
where rn = 1;

For a large table DISTINC ON is probably faster than the solution with the window function.

share|improve this answer
It worked, thanks! I did not need the IN ORDER clause. – DavideChicco.it Aug 5 at 10:33
1  
@DavideChicco.it You should use an ORDER BY if you want consistent results. From the manual: "Note that the "first row" of each set is unpredictable unless ORDER BY is used": postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT – a_horse_with_no_name Aug 5 at 10:34
But I'm not interested in the order, later I'll do just a COUNT(*)... should I use the ORDER BY clause anyway? – DavideChicco.it Aug 5 at 11:14
The order by is necessary to get a consistent result if you run this more than once. See this example: sqlfiddle.com/#!12/df4ef/4 if you don't care about which values are in the non-distinct columns, then you can leave out the order by – a_horse_with_no_name Aug 5 at 11:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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