Unable to create table with logical column

[castagninojose]

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]

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]

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]

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]

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é.