Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Unable to create table with logical column #430

Open
castagninojose opened this issue Jan 13, 2023 · 4 comments
Open

Unable to create table with logical column #430

castagninojose opened this issue Jan 13, 2023 · 4 comments

Comments

@castagninojose
Copy link

castagninojose commented Jan 13, 2023

I was not able to create a table with a logical (boolean) column using dbCreateTable. If I define

table_a = c(col_1="character", col_2="numeric", col_3="integer")
table_b = c(col_1="character", col_2="numeric", col_3="logical")

And attempt to create them with dbCreateTable, it works as expected for table_a. However, it fails for table_b giving the following Error: Failed to fetch row: ERROR: type "logical" does not exist at character 75.

I was not able to find a mention of this in the docs or this repo's issues. My apologies in advance if this was an already considered/documented case or simply a misuse on my end.

Full MWE:

import::from(DBI, dbConnect, dbCreateTable)
import::from(RPostgres, Postgres)

table_a = c(col_1="character", col_2="numeric", col_3="integer")
table_b = c(col_1="character", col_2="numeric", col_3="logical")

# Connect to PG, replace with your credentials
conn = dbConnect(
    Postgres(),
    dbname="your_db",
    host="your_host",
    port=5432,
    user="your_name",
    password="your_pwd"
)

dbCreateTable(conn, "TableA", table_a, row.names=NULL)
dbCreateTable(conn, "TableB", table_b, row.names=NULL)

Adittional info:

  • PG: PostgreSQL 14.6 (Debian 14.6-1.pgdg110+1)
  • DBI: Version: 1.1.3, Source: Repository, Repository: CRAN, Hash: b2866e62bab9378c3cc9476a1954226b
  • R: Version: 4.2.2, Patched (2022-11-10 r83330), Platform: x86_64-pc-linux-gnu (64-bit)
@castagninojose
Copy link
Author

After two full days trying and failing to achieve this, I was able to find a workaround using dataframes instantiated with empty vectors to enforce datatypes, like so

table_b = data.frame(col_1=character(), col_2=numeric(), col_3=logical())

which is not quite what I wanted but it's close enough.

Anyway, I'm leaving this open in case this is in fact a bug and someone wants to take a stab at it. Feel free to close otherwise.

Cheers

@krlmlr krlmlr transferred this issue from r-dbi/DBI Apr 1, 2023
@krlmlr krlmlr added the docs label Apr 1, 2023
@krlmlr
Copy link
Member

krlmlr commented Apr 1, 2023

Thanks. I see how this is confusing.

In https://dbi.r-dbi.org/reference/dbcreatetable#arguments-1, the fields argument is documented as

A named character vector: Names are column names, values are types. Names are escaped with dbQuoteIdentifier(). Field types are unescaped.

It doesn't suggest anywhere that the "types" must be SQL types. So, something along the lines of

table_b <- c(col_1 = "character", col_2 = "numeric", col_3 = "boolean")

should work too.

Also, an example is missing, but this is difficult to demo with SQLite because it doesn't really have a notion of strict types. The example could also use a zero-row data frame as the structure, like you did.

@castagninojose
Copy link
Author

Oooh, I see. That actually makes a lot of sense and it might have to do with some weird type behavior I was seeing that seemed to originate in postgres itself. Specifically with floats, which I was using double for, thinking it had to be an R type.

I'll definetly look into this closer and will revisit these definitions. I'd be happy to contribute to the docs or provide postgres examples if needed.

Thank you for your time,

José

@castagninojose
Copy link
Author

Hey,

Sorry for the late reply, we ended up moving away these functionalities in favor of python+sqlalchemy.

I can positively confirm the following creates the table with the intended types in PostgreSQL 14.8 (Debian 14.8-1.pgdg110+1)

test <- c(col_1='character', col_2='boolean', col_3='double precision')
dbCreateTable(conn, 'test_table', test, row.names=NULL)

To get the correct names for the types I consulted this page. I think the documentation could say something like:

A named character vector: Names are column names, values are types (as defined by your PostgreSQL instance).

Maybe it could also include a reference or a link to the postgres docs I mentioned above?

I haven't found any contributing guidelines other than this so I don't know the procedure to update docs, but if this sounds reasonable I would gladly help opening a pull request, submitting more examples or in another way.

Cheers,
José.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants