CODEWITHSUNDEEP
mysqlsqlpostgresql
jacktrades
jacktrades

Reputation: 7392

Join a Column with SELECT query in PostgreSQL

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?

Upvotes: 2

Views: 156

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657892

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.

Upvotes: 3

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

You can find answer in SQL Fiddle here

Upvotes: 0

Related Questions