2

Need this equivalent outcome on PostgreSQL on this MySQL query.

select id, 'ID1' from supportContacts

Idea is to Join a Column with table name and row values equals to ID1.

Executing the same query on PostgreSQL gives the desired row values but gives ?column? uknown as column name.

Which PostgreSQL query will give the exact output?

jacktrades
  • 7,224
  • 13
  • 56
  • 83

2 Answers2

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:

SELECT id, cast('ID1' AS varchar) AS "ID1" FROM "supportContacts"

For portability, make sure that MySQL runs with 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.

Start by reading the excellent manual here for basics about identifiers.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

You can find answer in SQL Fiddle here

Hamlet Hakobyan
  • 32,965
  • 6
  • 52
  • 68