PostgreSQL execute SELECT * FROM (t as a); return ERROR: syntax error at or near ")"-CodePudding

Why these SQLs can't work in PostgreSQL?

SELECT * FROM (t as a);

SELECT * FROM (t);

ERROR: syntax error at or near ")"

Those SQLs work well in MySQL.

CodePudding user response：

Well, it's invalid SQL.

If you want to give the table an alias you need to use from t as a.

The form from (....) requires a valid query inside the parentheses and t as a (or just t) is not a valid query. Additionally: a derived table (which is what (...) defines) requires an alias. So at least it has to be from (...) as bla

To get all rows and all columns from a table the SQL standard provides the shortcut table some_table which is supported by Postgres.

So the following would be valid:

select * from (table t) as a

CodePudding user response：

Typically, table alias' are applied where there is a join. For example:

SELECT alias1.login_name, alias2.name 
FROM tablename1 AS alias1
JOIN tablename2 AS alias2
ON alias1.id = alias2.role_id;

To apply an alias to a single table:

SELECT * FROM tablename AS alias;

..will do it for you, no need for parentheses.

You can confirm/test by example below if value is an integer:

SELECT * FROM tablename AS alias 
WHERE alias.colum = value;

..or if value is a string:

SELECT * FROM tablename AS alias 
WHERE alias.colum = 'value';

postgreSQL is strongly-typed, so the first example above will work for Int and Bool values, however if you have Date or other values, you may need to apply type casting. See this link for helpful info*: www.postgresqltutorial.com/postgresql-tutorial/postgresql-cast.

**Remember: For psql strings, always use single quotes for values, use double quotes for table names and column names.