I am having difficulty creating an index on a table in a database I
upsized to PostgreSQL. Specifically I can't figure out what to put in
the TO clause. For instance the following doen't work.

select pgstable
index on id+name tag i_in to pgstable

Does anyone know how to do this?

Thanks in advance,
Tim

On Mon, 24 Oct 2022 12:53:56 -0700, Tim Callahan wrote:

>I am having difficulty creating an index on a table in a database I
>upsized to PostgreSQL. Specifically I can't figure out what to put in
>the TO clause. For instance the following doen't work.
>
>select pgstable
>index on id+name tag i_in to pgstable
>
>Does anyone know how to do this?
>
>Thanks in advance,
>Tim

A clarification. This is only  happening with a table named ORDER
which is a reserved word in PostgreSQL. The below command for the
CUSTOMER file works but the one for the ORDER table does not.

Working:

select customer
index on sales_rep+customer tag c_sc to customer

Not working:

select order
index on order+cust_id tag o_oc to order

In pure sql it is to enclose offending part in double quotes .


For future proof, avoid using sql reserved words. And also mind that most of sql servers running on linux have case sensitive database and table names.

https://www.postgresql.org/docs/current/sql-keywords-appendix.html


Best and permanent solution: rename table to 'orders' during migration time. Check with sql keyword list for other offenders among dbf/ntx.


That upsizing tool should really have been written with part that is checker for sql reserved words.



Tim Callahan wrote in message news:gv1elhl25rj1ipat3rmsidcdfso6qhfv7o@4ax.com...
>On Mon, 24 Oct 2022 12:53:56 -0700, Tim Callahan wrote:
>
>>I am having difficulty creating an index on a table in a database I
>>upsized to PostgreSQL. Specifically I can't figure out what to put in
>>the TO clause. For instance the following doen't work.
>>
>>select pgstable
>>index on id+name tag i_in to pgstable
>>
>>Does anyone know how to do this?
>>
>>Thanks in advance,
>>Tim
>
>A clarification. This is only  happening with a table named ORDER
>which is a reserved word in PostgreSQL. The below command for the
>CUSTOMER file works but the one for the ORDER table does not.
>
>Working:
>
>select customer
>index on sales_rep+customer tag c_sc to customer
>
>Not working:
>
>select order
>index on order+cust_id tag o_oc to order

On Tue, 25 Oct 2022 07:49:09 +0200, Matej Jurac wrote:

Thanks for the response. I updated the migration script to set the
table name to _order. Then I updated the USE command to be the below. 
I think this should work in my existing logic - still need to test
that.
Tim

USE _order ALIAS order

>In pure sql it is to enclose offending part in double quotes .
>
>
>For future proof, avoid using sql reserved words. And also mind that most of sql servers running on linux have case sensitive database and table names.
>
>https://www.postgresql.org/docs/current/sql-keywords-appendix.html
>
>
>Best and permanent solution: rename table to 'orders' during migration time. Check with sql keyword list for other offenders among dbf/ntx.
>
>
>That upsizing tool should really have been written with part that is checker for sql reserved words.
>
>
>
>Tim Callahan wrote in message news:gv1elhl25rj1ipat3rmsidcdfso6qhfv7o@4ax.com...
>>On Mon, 24 Oct 2022 12:53:56 -0700, Tim Callahan wrote:
>>
>>>I am having difficulty creating an index on a table in a database I
>>>upsized to PostgreSQL. Specifically I can't figure out what to put in
>>>the TO clause. For instance the following doen't work.
>>>
>>>select pgstable
>>>index on id+name tag i_in to pgstable
>>>
>>>Does anyone know how to do this?
>>>
>>>Thanks in advance,
>>>Tim
>>
>>A clarification. This is only  happening with a table named ORDER
>>which is a reserved word in PostgreSQL. The below command for the
>>CUSTOMER file works but the one for the ORDER table does not.
>>
>>Working:
>>
>>select customer
>>index on sales_rep+customer tag c_sc to customer
>>
>>Not working:
>>
>>select order
>>index on order+cust_id tag o_oc to order