1

I am reading the documentation and trying to get to the bottom of some queries.

Here is a query with both t and u

SELECT * FROM (VALUES 1, 2) t("left"), (VALUES 3, 4) u("right");

Here is another using only t:

SELECT * FROM (VALUES 5, 2, 4, 1, 3) t(x) ORDER BY x OFFSET 2 LIMIT 2;

There are also these two (notice the space between the t and the ():

SELECT student, score
FROM tests
CROSS JOIN UNNEST(scores) AS t (score);

FROM (
  VALUES
    (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']),
    (ARRAY[7, 8, 9], ARRAY['cow', 'pig'])
) AS x (numbers, animals)
CROSS JOIN UNNEST(numbers, animals) AS t (n, a);

Here t(sites) is described as an alias.

FROM dataset, UNNEST(items) AS t(sites)

I have googled for hours. I hope someone with more SQL experience can point me toward useful search strings.

  1. Are both u and t predefined aliases?
  2. What do the aliases mean
  3. Do I need the parentheses for the alias to work?
  4. Do the space between the character and the parentheses have any special meaning?
haugstve
  • 115
  • 7

1 Answers1

1

My DWH team helped me out. These are aliases combined with column names. When you know, it seems trivial.

The first example:

SELECT * from (VALUES 1, 2) t("x")

might be easier to understand written like this

SELECT * from (VALUES 1, 2) as t("first")

The (VALUES 1, 2) as t("first") creates a table with one column and two rows. The table has the alias t, and column name first.

SELECT * from (VALUES (1,3), (2, 4)) as t("first", "second")

Gives two rows like this

#    first  second
1    1      3
2    2      4
  1. They are not predefined aliases but normal aliases
  2. NA
  3. You need them if the column names can not be inferred from the table
  4. No
haugstve
  • 115
  • 7