Upper Case field names

[rdonnay]

A customer of mine wants all the field names in his PostGreSql database to be upper case due to areas of his code that require this.

A fix in the code would be to change every Fieldname(1) = 'CUSTOMER" to Upper(Fieldname(1)) == 'CUSTOMER' but he doesn't want to make those changes to his source code.

Is there a way to tell the upsizing tool for force upper case on all field names?

I'm using the DbfUpsize() function and I don't see any docs about this.

Maybe there is something that would go into the .UPSIZE file to force uppercase field names?

Are lower case field names a requirement for all columns in PostGreSql?

[k-insis]

Postgresql has case sensitive field names when double-quoting inside queries/stored procedures is used. But if you do not double quote them, it works just fine as server side does lowercase comparison if you do not force that on it. Oracle, MSSQL (afaik has option to force case sensitivity) and Mariadb all work just fine too.

Table names, procedure names and such are case sensitive and should be treated as such on windows server systems too.

src: https://stackoverflow.com/questions/208 ... -sensitive

Never used pgdbe (only odbc since 2004) so I will suggest

Mr. Donnay I think you have enough weight to get Alaska to finally give access to source for dbfupsize() code as from numerous post I can clearly see it lacks features developers need for upload of dbf data to sql.


Personally, I would just generate a script like this and rename fields and be done with it; but am unsure how those 'special' columns , triggers from alaska will react to it.

https://stackoverflow.com/questions/686 ... postgresql

MFG

A customer of mine wants all the field names in his PostGreSql database to be upper case due to areas of his code that require this.

A fix in the code would be to change every Fieldname(1) = 'CUSTOMER" to Upper(Fieldname(1)) == 'CUSTOMER' but he doesn't want to make those changes to his source code.

Is there a way to tell the upsizing tool for force upper case on all field names?

I'm using the DbfUpsize() function and I don't see any docs about this.

Maybe there is something that would go into the .UPSIZE file to force uppercase field names?

Are lower case field names a requirement for all columns in PostGreSql?

[rdonnay]

generate a script like this and rename fields

That's a good idea.
I should have thought of that.

[rdonnay]

This script appears to be the best for this purpose.
It makes all fields upper case except for those created by Xbase++ because they start with '_"

Code: Select all

DO $$
DECLARE row record;
BEGIN
  FOR row IN SELECT table_schema,table_name,column_name
             FROM information_schema.columns
             WHERE table_schema = 'public' AND
			 Left(column_name,1) <> '_'
  LOOP
    EXECUTE format('ALTER TABLE %I.%I RENAME COLUMN %I TO %I',
      row.table_schema,row.table_name,row.column_name,upper(row.column_name));  
  END LOOP;
END $$;

[rdonnay]

It appears that this is not going to work.

Changes the field names to upper case causes a runtime error which I cannot interpret.
When I put them back to lower case all worked again.

[unixkd]

Hi Roger

Though I don't use pgdbe but I had the same issues while migrating add to pg server. You may have to drop the table first then create the new table with upper case field names then add your data.

I advice that you don't use text field of pg too.

Joe

[unixkd]

Hi Roger

Though I don't use pgdbe but I had the same issues while migrating add to pg server. You may have to drop the table first then create the new table with upper case field names then add your data.

I advice that you don't use text field of pg too.

Joe