Postgresql – How to create an Sql table from SELECT with a multiple row DISTINCT constraint

postgresqlprepared-statementselect

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?

Best Answer

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 parenthesis is just "noise".

When you write select (foo,bar) you are actually creating an anonymous record 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 (proprietary) operator DISTINCT ON which - in contrast to the standard DISTINCT - does operate on a sub-set of the columns.

You have to specify 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, DISTINCT ON is probably faster than the solution with the window function.

Related Solutions

Mysql – How to use MySQL variables in subqueries

The variable contains ONE string, but to work properly the subquery in IN() needs to return different items as multiple rows.

SELECT * FROM animals WHERE name IN(SELECT @animal_names);

is translated to

SELECT * FROM animals WHERE name IN(SELECT '\'dog\',\'cat\',\'penguin\',\'lax\',\'whale\',\'ostrich\'');

MySQL variable cannot hold table resultset so this is not directly possible. You would have to create a subquery to extract separate values to rows by joining some counter table and parsing the string. Or you may use dynamic sql which allows to take a string and parse it - that way you would define new variable containing entire select with the IN list filled by your current variable, and then prepare a statement from that string representation (sort of SQL eval). But probably best would be to just use the query direcly as a subquery instead of filling the variable. Newer version should materialize such independent subquery automatically. For good performance on older versions, you might want to create a temporary table instead and use JOIN and not IN.

Just a note - GROUP_CONCAT() is not very safe as there is a limit (variable) to how long string it can return and some results may be lost (with a warning) when the limit is exceeded.

Postgresql – Returning a json array slice in PostgreSQL

select  array_to_json 
        (
           (select  array_agg(my_db_field->n order by n) 
            from    generate_series(0,least(300,json_array_length(my_db_field))-1) gs(n) 
            )
        )

from    my_db_table

This solution (the original in this answer) most likely does not guarantee the order of the elements

select (array(select json_array_elements(my_db_field)))[1:300]
from    my_db_table

Best Answer

Related Solutions

Mysql – How to use MySQL variables in subqueries

Postgresql – Returning a json array slice in PostgreSQL

Related Question