CODEWITHSUNDEEP
rdatabaserpostgresql
sai saran
sai saran

Reputation: 757

How to get the column names from Database in R?

How to get the only particular table column names using R?

Sample code:

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

Is there any alternative for the above query?

Upvotes: 2

Views: 4153

Answers (2)

ToWii
ToWii

Reputation: 660

For the sake of completeness, I post the full code I use to retrieve overview of tables + overview of table columns with type:

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

Upvotes: 3

sai saran
sai saran

Reputation: 757

got the solution and will get the colnames using the below query.

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

sample query : 

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

Both query's are working good.

Upvotes: 2

Related Questions