Need this equivalent outcome on PostgreSQL on this MySQL query.
在这个MySQL查询上需要PostgreSQL上的这个等效结果。
select id, 'ID1' from supportContacts
Idea is to Join a Column with table name and row values equals to ID1.
想法是使用表名加入列,行值等于ID1。
Executing the same query on PostgreSQL gives the desired row values but gives ?column? uknown as column name.
在PostgreSQL上执行相同的查询会得到所需的行值,但是给出了?列? uknown为列名。
Which PostgreSQL query will give the exact output?
哪个PostgreSQL查询会给出确切的输出?
2 个解决方案
#1
3
Add a column alias to assign a name of your choosing. Else the system applies defaults.
To assign a type, use an explicit cast. I cast to text here:
添加列别名以指定您选择的名称。否则系统会应用默认值。要指定类型,请使用显式强制转换。我在这里投了文字:
SELECT id, 'ID1'::text AS "ID1" FROM supportContacts
Or use the SQL standard cast() to make it portable:
或者使用SQL标准cast()使其可移植:
SELECT id, cast('ID1' AS varchar) AS "ID1" FROM "supportContacts"
For portability, make sure that MySQL runs with SET sql_mode = 'ANSI'.
为了便于携带,请确保MySQL使用SET sql_mode ='ANSI'运行。
Also, unquoted CaMeL-case names like supportContacts are cast to lower case in PostgreSQL. Use "supportContacts" or supportcontacts depending on the actual table name.
此外,像PostContacts这样的未加引号的CaMeL案例名称会在PostgreSQL中转换为小写。根据实际的表名,使用“supportContacts”或supportcontacts。
Start by reading the excellent manual here for basics about identifiers.
首先阅读这里的优秀手册,了解有关标识符的基础知识。
#1
3
Add a column alias to assign a name of your choosing. Else the system applies defaults.
To assign a type, use an explicit cast. I cast to text here:
添加列别名以指定您选择的名称。否则系统会应用默认值。要指定类型,请使用显式强制转换。我在这里投了文字:
SELECT id, 'ID1'::text AS "ID1" FROM supportContacts
Or use the SQL standard cast() to make it portable:
或者使用SQL标准cast()使其可移植:
SELECT id, cast('ID1' AS varchar) AS "ID1" FROM "supportContacts"
For portability, make sure that MySQL runs with SET sql_mode = 'ANSI'.
为了便于携带,请确保MySQL使用SET sql_mode ='ANSI'运行。
Also, unquoted CaMeL-case names like supportContacts are cast to lower case in PostgreSQL. Use "supportContacts" or supportcontacts depending on the actual table name.
此外,像PostContacts这样的未加引号的CaMeL案例名称会在PostgreSQL中转换为小写。根据实际的表名,使用“supportContacts”或supportcontacts。
Start by reading the excellent manual here for basics about identifiers.
首先阅读这里的优秀手册,了解有关标识符的基础知识。