invalid sql generated with char column type #85
Comments
|
I think and indeed: |
|
ya it's special, I'm guessing it's an internal alias of some sort. Here's what happens via psql: |
|
ya check here: https://www.postgresql.org/docs/12/datatype.html. there's a list of aliases, and it appears there are at least two levels: |
|
so i guess the question is when constructing statements from AST, do you have to use the low-level types (in which case it should probably assert for invalid types), or can you use the high level types to preserve information. Otherwise you'd have information loss because you could never preserve the fact that the column is char/character/etc. |
|
We cannot raise an exception, because we do not have access to a catalog, so we cannot determine whether the type is valid or not, it could really be a custom type, for example introduced by an extension. |
|
perhaps then it needs to natively recognize pg aliases? |
|
Maybe, yes: that bpchar speciality could be generalized. What about starting to collect such aliases? |
|
is this what you're looking for? this is from PG12. What's interesting is the quotes around char in the table above. Notice the following behavior: so it seems quoted char is just for 1 char, but if you specify a length then it needs to be unquoted. So somewhat of a special case. |
|
I see, and some heuristic is already in place: Going back to the original case however, the first problem is that you didn't specify the fully qualified name of the type, so in any case the machinery won't work. |
|
my point is IMO one should be able to construct an AST from aliases since you may not have a way to do that a priori. In my case I'm translating TSQL types to PSQL programmatically via AST. Otherwise I'd have to hard-code these mappings (which I have to for now), much like this library is already doing. I'm not quite sure I understand what you're suggesting though, are you suggesting using |
|
I meant that your should actually be Does that work? |
|
nope |
|
I see. So, pglast could have a better knowledge about aliases, as you suggested. OTOH, |
|
sorry not following, per above it doesn't work now |
|
Strange, this works for me: import subprocess
import pglast
from pglast.stream import RawStream
from pglast import ast as pg_ast
from pglast import enums as pg_enums
column_defs=(pg_ast.ColumnDef(
'column_name',
inhcount=0,
is_from_type=False,
is_local=True,
is_not_null=False,
typeName=pg_ast.TypeName(
names=(pg_ast.String('pg_catalog'), pg_ast.String('bpchar'),),
pct_type=False,
setof=False,
typemod=-1,
typmods=(pg_ast.A_Const(10),)
)
),)
tbl_stmt = pg_ast.CreateStmt(
if_not_exists=True, oncommit=pg_enums.OnCommitAction.ONCOMMIT_DROP,
relation=pg_ast.RangeVar(inh=True, relname='foo', relpersistence='t'), tableElts=column_defs
)
sql = RawStream()(tbl_stmt)
subprocess.run(['psql', '-e', '-c', sql, 'foo'])and emits |
|
remember, I'm trying to do via alias, |
|
Yes, I remember :-) |
|
If I were you, I'd probably build a mapping between |
|
ya I have one :) let me know if you want to keep this issue open then or add to some kind of backlog of yours. my idea to do this programmatically was to build a string with the type so it seems pglast does have this information, perhaps this could be solved by exposing a helper method to do this mapping (which seems like pglast already has). |
|
Can you clarify what would you do with the result of that parse? I do not understand how that can be useful for your cause... |
|
so in my case i'd get back that |
If you create an AST with a column like:
it will serialize to
"char"(20)which is invalid sql. It should just bechar(20)The text was updated successfully, but these errors were encountered: