Tag Info

Hot answers tagged

6

A very simple query would be to use UNION DISTINCT. I think it would be most efficient if there is a separate index on each of the four columns: SELECT a AS abcd FROM tablename UNION -- means UNION DISTINCT SELECT b FROM tablename UNION SELECT c FROM tablename UNION SELECT d FROM tablename ; Another would be to first UNION ...


6

What makes you think you are comparing dates? Actually, you are comparing string literals which - in the absence of a cast context and any explicit cast - default to text SELECT '20150526' > '2015-05-26' AS text2text , '20150526'::date > '2015-05-26'::date AS date2date; text2text | date2date ----------+---------- t | f Try: SELECT ...


5

To be clear, I'd use union as ypercube suggests, but it is also possible with arrays: select distinct unnest( array_agg(distinct a)|| array_agg(distinct b)|| array_agg(distinct c)|| array_agg(distinct d) ) from t order by 1; SQLFiddle here


4

You could use LATERAL, like in this query: SELECT DISTINCT x.n FROM atable CROSS JOIN LATERAL ( VALUES (a), (b), (c), (d) ) AS x (n) ; The LATERAL keyword allows the right side of the join to reference objects from the left side. In this case, the right side is a VALUES constructor that builds a single-column subset out of the column values ...


3

Turns out this wasn't as complicated as I thought! (With just a little knowledge of pg_catalog...) Part 1: Query whether a materialized view exists: SELECT count(*) FROM pg_catalog.pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'm' AND n.nspname = 'some_schema' AND c.relname = 'some_mat_view'; Part 2: Query the definition of a ...


2

You can, but as I wrote and tested the function I felt wrong. It is a resources waste. Just please use a union and more select. Only advantage (if it is), one single scan from main table. CREATE OR REPLACE FUNCTION fn_readuniqu() RETURNS SETOF text AS $BODY$ DECLARE a_array text[]; b_array text[]; c_array text[]; d_array ...



Only top voted, non community-wiki answers of a minimum length are eligible