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

Community
  • 1
hackg
  • 1

2 Answers2

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:

Community
  • 1
Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
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

hackg
  • 1
  • 1
  • 1