PostgreSQL: How to add Column from other table in select statement?

Question

I'm trying to select a column from another table like I used to do in MSSQL:

 select * , Date = (select top 1 Date from [dbo].TableB where status = 1 order by Date desc)
 from [dbo].TableA

How can I do that in PostgreSQL?

Additional sample data:

TableA

Names

Richards
Marcos
Luke
Matthew
John

TableB

Date        Status
2016-01-01  1
2016-01-02  0
2016-01-03  1
2016-01-04  1
2016-01-05  1

Expected Output:

Name        Date
Richards    2016-01-02
Marcos      2016-01-02
Luke        2016-01-02
Matthew     2016-01-02
John        2016-01-02

Thanks!

Would select * , (select NewColumn from [dbo].TableB) as NewColumn not work? — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 22, 2016 at 1:34
Can you please post sample data and expected results? Do you only have a single record in tableb? If not, it will return multiple results and cause an error most likely... — sgeddes
– sgeddes, Commented Mar 22, 2016 at 1:36

score 2 · Accepted Answer · 2016-03-22 08:05:51Z

Date = (...) is invalid (standard) SQL and won't work in Postgres (or any other DBMS except for SQL Server)

A column alias is defined using AS ... in SQL (and Postgres). Postgres also doesn't have top. It uses limit.

Using square brackets in an identifier is also not allowed in SQL. So [dbo] needs to become dbo or "dbo" depending on how you created the schema.

select a.*, 
       (select date
        from dbo.tableb as b
        where b.status = 1 
        order by b.date desc
        limit 1) as date
from dbo.tablea a

date is a reserved word and should not be used as an identifier (column name)

If you want to use standard ANSI SQL, you can also use fetch first 1 row only instead of limit 1.

Another option would be to use max() instead of the limit in the sub-select which doesn't need the limit at all:

select a.*, 
       (select max(date)
        from dbo.tableb as b
        where b.status = 1) as date
from dbo.tablea a

Juan Pablo · Accepted Answer · 2016-03-22 01:34:46Z

1

I'm not sure if this is the correct syntax, but did you try this:

select * , (select NewColumn from [dbo].TableB) as NewColumn 
 from [dbo].TableA

I hope it helps.

answered Mar 22, 2016 at 1:34

Juan Pablo

1,22310 silver badges15 bronze badges

2 Comments

fLen Over a year ago

Not possible in postgreSQL. :(

user330315 Over a year ago

If tableb has more then one row, this won't work. Neither in SQL Server nor in Postgres (where [dbo]. is an invalid identifier anyway)

Licantropo · Accepted Answer · 2016-03-22 03:19:36Z

I'm not too familiar with PostgreSQL but SQL is still SQL. First thing to say is you have to have only one result on the second table query and you can do it in

 SELECT 
        A.*
        (select NewColumn from [dbo].TableB.NewColumn) as NewColumn 
FROM TableA

However I think you'll need to declare a join condition.

 SELECT 
        A.*
        (select NewColumn from [dbo].TableB.NewColumn where A.Col1 = TableB.col1)
FROM TableA A

without a real example I cant be more specific.

Rodrigo Silver · Accepted Answer · 2016-03-22 07:56:19Z

0

Try this:

select a.*, b.column
from tableA as a, tableB as b;

edited Mar 22, 2016 at 7:56

user330315

answered Mar 22, 2016 at 1:45

Rodrigo Silver

52 bronze badges

Comments

Tim Biegeleisen · Accepted Answer · 2016-03-22 08:04:36Z

0

You could try doing a CROSS JOIN:

SELECT * FROM
    (SELECT * FROM dbo.TableA),
    (SELECT Date FROM dbo.TableB WHERE status = 1 ORDER BY Date DESC LIMIT 1)

edited Mar 22, 2016 at 8:04

answered Mar 22, 2016 at 7:51

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: How to add Column from other table in select statement?

5 Answers 5

Comments

2 Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

Comments

Comments

Comments

Related