0

SQL Query on PostgreSQL:

insert into TOKEPOOLAMT (TOKEPOOLAMT.TOKEPOOLAMTID,TOKEPOOLAMT.TOKERULEID)
values (151, 176);

Giving error:

com.edb.util.PSQLException:
ERROR: column "tokepoolamt" of relation "tokepoolamt" does not exist

But:

insert into TOKEPOOLAMT (TOKEPOOLAMTID,TOKERULEID) values (151, 176);

is working fine.

Can anybody explain why alias name with column in insert statement not working?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Vikas Bhardwaj
  • 73
  • 1
  • 5
  • Looks like an issue with UPPER and lower case object names. How did you create the table and it's columns? Advice: Only use lower case. – Frank Heikens Feb 11 '15 at 13:03
  • I don't think it's an issue of case. I tried with both the cases. And first query is not working but second without alias is working. – Vikas Bhardwaj Feb 11 '15 at 13:10
  • 2
    `TOKEPOOLAMTID` is not an alias, that's your column's name. What you tried first is to qualify your column names with its table-name (which is more than a simple column name, but still not an *alias*), but that simply not necessary, in fact not supported at all. – pozs Feb 11 '15 at 13:19
  • 2
    ***column** The name of a column in the table named by table_name. The column name can be qualified with a subfield name or array subscript, if needed. (Inserting into only some fields of a composite column leaves the other fields null.)* http://www.postgresql.org/docs/current/static/sql-insert.html – pozs Feb 11 '15 at 13:22
  • @pozs : I do agree with your comments and it's also correct that postgresql doesn't support insert statement with TableName.ColumnName, because it does not found it qualified column name. – Vikas Bhardwaj Feb 26 '15 at 09:19

1 Answers1

3

There are no aliases involved here. Your error is that column names in the column list of an INSERT command cannot be table-qualified. @pozs already provided the fitting quote from the manual in his comment.

I don't think it's an issue of case. I tried with both the cases.

That's missing the point. In Postgres, identifiers are folded to lower case unless double-quoted. If you double-quoted a name at creation time you preserved a case sensitive spelling and need to double-quote for the rest of the object's life - unless it was a legal, lower-case name to begin with, then quoting won't make a difference. Details:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228