Hi, Daniel,
First, tbl and "tbl" aren't "totally different":
> xof=# create table tbl (i integer);
> CREATE TABLE
> xof=# create table "tbl" (i integer);
> ERROR: relation "tbl" already exists
The difference is that putting double quotes around an SQL identifier makes the
comparison type-sensitive, and allows for characters not otherwise allowed in
identifiers:
> xof=# select * from Tbl;
> i
> ---
> (0 rows)
>
> xof=# select * from "Tbl";
> ERROR: relation "Tbl" does not exist
> LINE 1: select * from "Tbl";
> ^
You can use SQL.identifier, but you need to make sure you are getting the case
right; in general, PostgreSQL types are all lower-case, and it's only the lack
of double quotes that makes this work:
xof=# create table x (i VARCHAR);
CREATE TABLE
xof=# create table y (i "VARCHAR");
ERROR: type "VARCHAR" does not exist
LINE 1: create table y (i "VARCHAR");
^
xof=# create table y (i "varchar");
CREATE TABLE
> On Jun 13, 2019, at 12:28, Daniel Cohen <daniel.m.co...@berkeley.edu> wrote:
>
> Hi!
>
> I'm working on a project in Python that interacts with a PostgreSQL data
> warehouse, and I'm using the psycopg2 API. I am looking to create
> dynamically-typed tables.
>
> For example, I would like to be able to execute the following code:
>
> from psycopg2 import connect,
> sql
>
> connection
> = connect(host="host", port="port", database="database", user="user",
> password="pw")
>
>
>
> def create_table(tbl_name, col_name, col_type):
>
> query
> = sql.SQL("CREATE TABLE {} ({} {})".format(sql.Identifier(tbl_name),
> sql.Identifier(col_name), sql.Identifier(column_type)))
>
> connection
> .execute(query)
>
>
> create_table
> ('animals', 'name', 'VARCHAR')
> and end up with a table named "animals" that contains a column "name" of type
> VARCHAR. However, when I attempt to run this, I get an error: 'type "VARCHAR"
> does not exist'. I assume psycopg2's built-in formatter is putting double
> quotes around the VARCHAR type when there should not be any. Normally, I
> would just work around this myself, but the documentation is very clear that
> Python string concatenation should never be used for fear of SQL injection
> attacks. Security is a concern for this project, so I would like to know if
> it's possible to create dynamically-typed tables in this fashion using
> pyscopg2, and if not, whether there exists another third-party API that can
> do so securely.
>
> A second issue I've had is that when creating tables with a similar
> methodology, the sql.Identifier() function does not perform as I expect it
> to. When I use it to dynamically feed in table names, for example, I get
> varying results. See below:
>
> CREATE TABLE tbl AS SELECT * FROM other_tbl;
> in raw SQL creates a table called tbl, whereas
>
> cursor.execute(sql.SQL("CREATE TABLE {} AS SELECT * FROM
> other_tbl").format(sql.Identifier(tbl))
> creates a table called "tbl". The two are different, and
>
> SELECT * FROM tbl;
>
> returns a totally different table than
>
> SELECT * FROM "tbl";
> Please let me know if I can fix either of these problems; I want to be able
> to dynamically feed types into SQL queries, and I want the tables created to
> be of the form tbl not "tbl". Thank you!
>
> Danny
>
>
--
-- Christophe Pettus
x...@thebuild.com
