library(RPostgreSQL)

# Enter the values for you database connection
dsn_database = "northwind"
dsn_hostname = "localhost"
dsn_port = "5432"
dsn_uid = "kilia"
dsn_pwd = ""

# Create the database connection
tryCatch({
  drv <- dbDriver("PostgreSQL")
  print("Connecting to database")
  conn <- dbConnect(drv,
                    dbname = dsn_database,
                    host = dsn_hostname,
                    port = dsn_port,
                    user = dsn_uid,
                    password = dsn_pwd)
  print("Connected!")
},
error=function(cond) {
  print("Unable to connect to database.")
})

# List tables existing in the database 'northwind'
cursor <- dbGetQuery(conn, "SELECT table_name
  FROM information_schema.tables
                     WHERE table_schema='public'
                     AND table_type='BASE TABLE'")
print(cursor)

# Query data
df <- dbGetQuery(conn, "SELECT * FROM categories LIMIT 10")
print(df)

# Create a table
dbSendQuery(conn, "DROP TABLE IF EXISTS Cars")
dbSendQuery(conn, "CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name VARCHAR(20), Price INT)")

# Insert data into a table
dbSendQuery(conn, "INSERT INTO Cars VALUES(1,'Audi',52642)")
dbSendQuery(conn, "INSERT INTO Cars VALUES(2,'Mercedes',57127)")

# Close the database connection
dbDisconnect(conn)