What are differences between SQL queries?

Question

We've this query:

SELECT t FROM articles t WHERE t.article_id = 59446

Also known as:

SELECT articles FROM articles WHERE articles.article_id = 59446

I can not understand

SELECT articles FROM articles

What does this mean? Why it works?

Update: table 'articles' does not have column 'articles'

Answer 1

This is a result of Postgres' object-relational architecture. For every table you create, there is also a matching composite type with the same name.

When you run

SELECT articles 
FROM articles

you are selecting a single column with the type articles from the table named articles. If you pay close attention to the output of that query you will notice that your result only contains a single column where the value is enclosed in parentheses, e.g. (1,Foobar) (if the table articles has two columns). If you run select * from articles the output is two columns (and no parentheses)

The same thing happens when you put the list of columns between parentheses:

select (article_id, article_name) 
from articles

also returns a single column with an anonymous composite type containing two fields (this is also a good example that "column" and "field" is not the same thing).