0

How can I find the table name for each row result in my query?

I have tried ideas suggested on the stack network (overflow, dba)

1 How to make a table name an attribute in PostgreSQL?

2 https://stackoverflow.com/questions/24580352/get-the-name-of-a-rows-source-table-when-querying-the-parent-it-inherits-from

The template is the parent that spawns all the child tables I am searching. When I search just name and state, I get expected results, but they all come from different tables

select first_name, last_name, state
from template
where completed_interview_flag = 1

When I add the tablename as suggested, to try to find the source table name - I get an error

select tableoid::regclass AS source, first_name, last_name, state
from template
where completed_interview_flag = 1 

********** Error **********

ERROR: column "tableoid" does not exist

SQL state: 42703

Character: 8

  • 1
    Maybe add postgres version to question? – Fabrizio Mazzoni May 14 '15 at 20:50
1

There must be some kind of misunderstanding. tableoid is a system column that's available for any regular table in any version of Postgres since at least v7.3. Per documentation:

Every table has several system columns that are implicitly defined by the system.

tableoid is one of them. The solution I provided under the questions you refer to is also suggested in the manual here. And it works. I have been using it for years in various Postgres versions.

Either you are not using Postgres, or template is not a table. Is it a VIEW maybe? If you define a VIEW like:

CREATE VIEW template AS
SELECT * FROM some_table;

Then system columns are not included by default.

What do you get for

SELECT relnamespace, relname, relkind
FROM   pg_class
WHERE  relname = 'template';

Is relkind 'r', 'v' or something else?
Or do you get multiple rows? Then consider this:

0

Sorry its Postgres 8.4 and its not a view

SELECT relnamespace, relname, relkind
FROM   pg_class
WHERE  relname = 'template';

relnamespace;relname;relkind
2200;"template";"r"

I found out why it wasn't working. I was joining with 2 other tables, so it was confused which tableoid I was asking for

Although I found it odd that it wasn't giving me the usual ambiguous error that tells you to specify template.tableoid instead of just tableoid

  • Well, the question is demonstrating a query on a single table. My answer was right on target, it turned out to be a misunderstanding. – Erwin Brandstetter May 16 '15 at 3:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.