CODEWITHSUNDEEP
sqlpostgresql
b degnan
b degnan

Reputation: 692

SQL column names and comparing them to row records in another table in PostgreSQL

I have one of those cases where each SQL query works great, but not when I paste them together.

I have two tables. One is dynamic that allows NULL values. The other helps fill the null values by providing a user with a questions. I take the column names from the dynamic table and see if I can get them to match with fields in the other questions table. Currently, we have this working PHP, but I have been trying put it all into our PostgreSQL database.

This gets all of the column names from the dynamic table and return a comma separated list of strings.

select array_to_string(array_agg(quote_literal(column_name::text)),',') from
information_schema.columns where table_name='dynamic_table';

It works great, and returns something like: 'notificationemail','birthdate','lastnotificationcheck','createmethod'

If I paste this list directly into my next query, it works and returns rows:

select * FROM questions_table WHERE questioncolumn IN   
('notificationemail','birthdate','lastnotificationcheck','createmethod');

Now once I paste them together, I get something that runs but returns no rows:

select * FROM questions_table WHERE questioncolumn IN   
(select array_to_string(array_agg(quote_literal(column_name::text)),',') from
information_schema.columns where table_name='dynamic_table');

I am trying to determine how the static text field is different from the embedded SELECT inside the IN statement. It seems to make sense that it would work, but obviously, something is different from the dynamic list and the static text.

EDIT: Thank you! I guess I was over thinking things. By not making it a string list and keeping it an object, it worked right away.

Upvotes: 1

Views: 11279

Answers (3)

fredt
fredt

Reputation: 24352

The IN clause expects a transient table, not a string of comma-separated values.

select * FROM questions_table WHERE questioncolumn IN   
select column_name from
information_schema.columns where table_name='dynamic_table';

Upvotes: 1

sgeddes
sgeddes

Reputation: 62831

I think this might be what you're looking for instead:

select * 
FROM questions_table 
WHERE questioncolumn IN
(
  select column_name 
  from information_schema.columns where table_name='dynamic_table'
);

SQL Fiddle Demo

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

select *
FROM questions_table
WHERE questioncolumn IN (
    select quote_literal(column_name::text)
    from information_schema.columns
    where table_name='dynamic_table'
);

Upvotes: 1

Related Questions