Exclude foreign keys from sql query

Question

I've got some sql joining a few tables together

SELECT contact.contact_id,
        form1.*,
        form2.*
FROM contact
INNER JOIN form1
ON contact.contact_id = form1.contact_id
INNER JOIN form2
ON contact_contact_id = form2.contact_id

I want to avoid repeating the 'contact_id' column in my output without having to write every field name in the table (ie I want to keep using form1.* and form2.* as this will make the report much easier to maintain - fields are liable to change). Is this possible? I'm using Postgres.

http://stackoverflow.com/questions/3650689/sql-exclude-a-column-from-select-query — Martin Wilson, Jul 16 '12 at 10:09

score 1 · Accepted Answer · answered Jul 16 '12 at 10:07

1

Only way is "Dont use *". You need to explicitely type out the required column. This is a good practice when you use JOINs. If the columns are too many, make use of the result

select column_name from information_schema.columns
where table_name='your table' and column_name<>'contact_id'

answered Jul 16 '12 at 10:07

Madhivanan

13,470
1
24
29

When you say 'make use of the result' are you thinking programmatically or are you talking about cutting and pasting? – Aidan Ewen Jul 16 '12 at 10:48

score 1 · Answer 2 · answered Jul 16 '12 at 10:08

1

If contact_id is the only column name that the tables share, you can use 'natural join' to join the tables and you will end up with only one copy of the joining column.

answered Jul 16 '12 at 10:08

Burleigh Bear

3,274
22
32

lead me to 'USING' instead of 'NATURAL'. But both statments seem to require 'SELECT * FROM' - I can't specify some columsn then let 'NATURAL' handle the rest. – Aidan Ewen Jul 16 '12 at 10:50

Exclude foreign keys from sql query

2 Answers2