如何在R中从数据库中获取列名

Question

如何使用R获取唯一特定的表列名？

样本代码：

df<-dbgetQuery(con,"select * from table 1 limit 100")
colnames(df)

对于上述查询，有其他选择吗？

Answer 1

为了完整起见，我发布了我用来检索表概述+表列概述的完整代码，类型为：

library(RPostgres)
# login
your_connection <- dbConnect(Postgres(),
host = '*your-host-address*',
port = *your-port-four-digits*,
user = '*your-username*',
password = 'your-password*',
sslmode = 'require',
dbname = '*name-of-database*')
# send request to get overview of tables
res <- dbSendQuery(your_connection, "select distinct table_schema
from information_schema.tables
where table_type ='VIEW'
or table_type ='FOREIGN TABLE'
order by table_schema")
data <- dbFetch(res, n=-1)
dbClearResult(res)
data
# send request to get overview of tables in a table schema
res <- dbSendQuery(your_connection, "select distinct table_name
from information_schema.columns
where table_schema='*your-table-name*'
order by table_name")
data <- dbFetch(res, n=-1)
dbClearResult(res)
data
# send request to get overview of columns of a table
res <- dbSendQuery(your_connection, "select distinct column_name, data_type
from information_schema.columns
where table_name ='*your-table-name*'
order by column_name")
data <- dbFetch(res, n=-1)
dbClearResult(res)
data

Answer 2

获得了解决方案，并将使用下面的查询获得colname。

dbGetQuery(con,"SELECT column_name
+ FROM information_schema.columns
+ WHERE table_schema = 'your schema'
+   AND table_name   = 'table name'") ##ORDER  BY ordinal_position; to orderby

示例查询：

dbGetQuery(con,"SELECT column_name, data_type
+ FROM   information_schema.columns
+ WHERE  table_name = 'data 1'
+ ORDER  BY ordinal_position")

两个查询都运行良好。