When How to Use an Identifier Number Instead of Its Name in Postgresql

When can we use an identifier number instead of its name in PostgreSQL?

Positional references to output columns are valid in the GROUP BY and ORDER BY clauses of a SELECT statement.

GROUP BY Clause
expression can be an input column name, or the name or ordinal number
of an output column (SELECT list item)

ORDER BY Clause
Each expression can be the name or ordinal number of an output column
(SELECT list item)

And also DISTINCT ON:

The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY.

Example:

Select first row in each GROUP BY group?

Trouble converting IDs to name values in Postgres

SELECT match.id, 
       character_a.first_name AS player1_char,
       character_b.first_name AS player2_char
FROM match
JOIN character as character_a
  ON match.player1_char_id = character_a.id
JOIN character as character_b
  ON match.player2_char_id = character_b.id;

Postgres table column name restrictions?

Here's a nice table of reserved words in PostgreSQL:

http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html

It is probably best to simply avoid using those words as table- or column-names.

An alternative, however, is to enclose the identifier in double-quotes, e.g.:

CREATE TABLE IF NOT EXISTS apiss (
    skey TEXT, 
    time INTEGER, 
    "user" TEXT, 
    ip TEXT);

Additionally, Postgres reserves system column names for internal use in every table:
"Every table has several system columns that are implicitly defined by the system. Therefore, these names cannot be used as names of user-defined columns."

https://www.postgresql.org/docs/current/ddl-system-columns.html

Escaping keyword-like column names in Postgres

Simply enclose year in double quotes to stop it being interpreted as a keyword:

INSERT INTO table (id, name, "year") VALUES ( ... );

From the documentation:

There is a second kind of identifier: the delimited identifier or
quoted identifier. It is formed by enclosing an arbitrary sequence of
characters in double-quotes ("). A delimited identifier is always an
identifier, never a key word. So "select" could be used to refer to a
column or table named "select", whereas an unquoted select would be
taken as a key word and would therefore provoke a parse error when
used where a table or column name is expected.

Table or column name cannot start with numeric?

It comes from the original sql standards, which through several layers of indirection eventually get to an identifier start block, which is one of several things, but primarily it is "a simple latin letter". There are other things too that can be used, but if you want to see all the details, go to http://en.wikipedia.org/wiki/SQL-92 and follow the links to the actual standard ( page 85 )

Having non numeric identifier introducers makes writing a parser to decode sql for execution easier and quicker, but a quoted form is fine too.

Edit: Why is it easier for the parser?

The problem for a parser is more in the SELECT-list clause than the FROM clause. The select-list is the list of expressions that are selected from the tables, and this is very flexible, allowing simple column names and numeric expressions. Consider the following:

SELECT 2e2 + 3.4 FROM ...

If table names, and column names could start with numerics, is 2e2 a column name or a valid number (e format is typically permitted in numeric literals) and is 3.4 the table "3" and column "4" or is it the numeric value 3.4 ?

Having the rule that identifiers start with simple latin letters (and some other specific things) means that a parser that sees 2e2 can quickly discern this will be a numeric expression, same deal with 3.4

While it would be possible to devise a scheme to allow numeric leading characters, this might lead to even more obscure rules (opinion), so this rule is a nice solution. If you allowed digits first, then it would always need quoting, which is arguably not as 'clean'.

Disclaimer, I've simplified the above slightly, ignoring corelation names to keep it short. I'm not totally familiar with postgres, but have double checked the above answer against Oracle RDB documentation and sql spec

Why PostgreSQL does not like UPPERCASE table names?

put table name into double quotes if you want postgres to preserve case for relation names.

Quoting an identifier also makes it case-sensitive, whereas unquoted
names are always folded to lower case. For example, the identifiers
FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo"
and "FOO" are different from these three and each other. (The folding
of unquoted names to lower case in PostgreSQL is incompatible with the
SQL standard, which says that unquoted names should be folded to upper
case. Thus, foo should be equivalent to "FOO" not "foo" according to
the standard. If you want to write portable applications you are
advised to always quote a particular name or never quote it.)

from docs (emphasis mine)

example with quoting:

t=# create table "UC_TNAME" (i int);
CREATE TABLE
t=# \dt+ UC

t=# \dt+ "UC_TNAME"
                      List of relations
 Schema |   Name   | Type  |  Owner   |  Size   | Description
--------+----------+-------+----------+---------+-------------
 public | UC_TNAME | table | postgres | 0 bytes |
(1 row)

example without quoting:

t=# create table UC_TNAME (i int);
CREATE TABLE
t=# \dt+ UC_TNAME
                      List of relations
 Schema |   Name   | Type  |  Owner   |  Size   | Description
--------+----------+-------+----------+---------+-------------
 public | uc_tname | table | postgres | 0 bytes |
(1 row)

So if you created table with quotes, you should not skip quotes querying it. But if you skipped quotes creating object, the name was folded to lowercase and so will be with uppercase name in query - this way you "won't notice" it.

Are PostgreSQL column names case-sensitive?

Identifiers (including column names) that are not double-quoted are folded to lowercase in PostgreSQL. Column names that were created with double-quotes and thereby retained uppercase letters (and/or other syntax violations) have to be double-quoted for the rest of their life:

"first_Name"

Values (string literals / constants) are enclosed in single quotes:

'xyz'

So, yes, PostgreSQL column names are case-sensitive (when double-quoted):

SELECT * FROM persons WHERE "first_Name" = 'xyz';

Read the manual on identifiers here.

My standing advice is to use legal, lower-case names exclusively so double-quoting is never required.

Syntax Error when using a case when conditional

An alias like 2020Q4_revenue is an “identifier” in SQL, so it must conform with the rules:

SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($). Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable.

You can still use that alias name if you quote it with double quotes:

There is a second kind of identifier: the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). A delimited identifier is always an identifier, never a key word. So "select" could be used to refer to a column or table named “select”, whereas an unquoted select would be taken as a key word and would therefore provoke a parse error when used where a table or column name is expected.
Quoted identifiers can contain any character, except the character with code zero. (To include a double quote, write two double quotes.)

displaying column names instead of data from psql terminal

'Name' is a string constant, identifiers (column names) must be enclosed in double quotes (the same you did with the table name)

SELECT "Name" FROM "Restaurants";