CODEWITHSUNDEEP
sqlpostgresqlcommon-table-expression
yizheshexin
yizheshexin

Reputation: 93

subquery factoring cannot work in PostgreSQL

when I am using postgresql, I encounter a problem.Here is the code:

    WITH t1 AS (
    SELECT
    TABLE_NAME AS TABELA
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    COLUMN_NAME LIKE 'nu_cns' )
SELECT 
    TABLE_NAME,
    COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_NAME IN t1

If I run it ,I will get a error:

ERROR:  syntax error at or near "t1"
LINE 14:  TABLE_NAME IN t1
                    ^
SQL state: 42601
Character: 211

But what is weird is that if I replace the t1 with the whole subquery ,it can run successfully, like this: 

    WITH t1 AS (
    SELECT
    TABLE_NAME AS TABELA
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    COLUMN_NAME LIKE 'nu_cns' )
SELECT 
    TABLE_NAME,
    COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_NAME IN (
    SELECT
    TABLE_NAME AS TABELA
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    COLUMN_NAME LIKE 'nu_cns' )

You see, in fact the subquery is exactly same with the t1.So why the former cannot work? Thanks!

Upvotes: 3

Views: 186

Answers (2)

Bill Smith
Bill Smith

Reputation: 1

WITH t1 AS 
( 
  SELECT TABLE_NAME AS TABELA
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE COLUMN_NAME LIKE 'nu_cns' 
)

SELECT 
    t2.TABLE_NAME,
    t2.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS t2 join t1 on t1.TABELA = t2.table_name

Upvotes: 0

Fahmi
Fahmi

Reputation: 37483

Try below: you need to select table_name from t1 in subquery

WITH t1 AS 
( 
  SELECT TABLE_NAME AS TABELA
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE COLUMN_NAME LIKE 'nu_cns' 
)

SELECT 
    TABLE_NAME,
    COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN (select table_name from t1)

Upvotes: 1

Related Questions