rowMode = 'array' apparently provides no way of getting column names

[neilmiddleton]

We were experimenting with node-postgres and found that columns were lost in the result set if they had the same name, because of how JSON works.

However, after switching to array we don't apparently have any way of knowing the column names. Is there a way of doing this or is this a shortfall?

(for context, in this scenario, we don't know the query we're running)

[charmander]

It looks like result.fields should continue to work (an array of field descriptions with name properties).

[neilmiddleton]

Sorry, should have mentioned, I'm using cursor here. Given cursor.read(n) returns rows it's not clear how to also get column names.

[brianc]

Sorry, should have mentioned, I'm using cursor here. Given cursor.read(n) returns rows it's not clear how to also get column names.

Oofff yeh that might have been an oversight on my part. I'll leave this open so I can get to remedying this in some way in the near future.

[JoshuaKlingbeil]

EDIT: PEBKAC issue - not ultimately related to original issue above. The 3rd party view generation query under hood uses a property from an internal user record retrieved with a compare to CURRENT_USER.

-- relevant subquery within view-generation query
SELECT u.obfuscated_prop  -- this was the field with the missing metadata on prod users
 FROM obfuscated_schema.application_user u
 WHERE u.name = CURRENT_USER

The primary dev user I use for pgsql connections works because it has more metadata attached to it within the 3rd party app. The user creds for our prod and test servers did not have the necessary metadata attached, and so the view being generated in postgres (in those user contexts) were populating with null values on the name column. This would have been easier to detect if the thirdparty naming for the column was something obvious not subject to name collisions like "custom_property_labels" vs "name." But it ultimately was NOT a node-postgres driver issue. We could have updated metadata on the service users for prod apps but instead, in our case we were safely able to workaround the issue by just using a modified version of the underlying view-gen query and hardcode necessary prop values.

I'll leave this update and my original (misplaced) comment below for any future devs struggling with a similarly weird outlier case. My apologies for the inadvertent hijack.

=================================================================

I'm not sure if my experience is related to this same underlying issue with column naming collisions, but it seems like it might be a similar - if not identical - obj/json prop name collision issue. I'll note it here first and if it ends up seeming unique (or misunderstanding on my part) I'll raise a separate issue or drop it as meaningful.

This seems like something so basic and common that it would have been hit by others before. While using pg-promise in main codebase, I ran into an issue where no manner of query building could get the data in a column named "name" in a third-party-supplied readonly view (which I have no control over in terms of field naming) to return with the result set. Other fields populated with their expected data, but the "name" field only returns nulls. Same queries run in DBeaver result in "name" column getting expected data in result set's rows.

I've subsequently dropped testing down to node-postgres / pg to see if it was a pg-promise level issue or more fundamental to node-postgres under the hood.

Same issue, not resolved with aliasing the field or escaping its "name" name. Am I just too deep into it and missing something obvious?

Here's the last version of query (with rowMode=array) for reference. Again, DBeaver displays the expected data/values for the "name" named column using the same query. No rows in any of the views with a "name" named column will display anything but nulls for that field using node-postgres, but no rows in the database have even a single null entry for those same "name" named columns.

const result = await client.query({
    text: `SELECT v."name" as "not_just_name" from third_party_generated_views_schema.any_of_several_views v limit 5;`, 
    rowMode: 'array'});

result.fields:

[
  Field {
     name: 'not_just_name',
     tableID: 508045,
     columnID: 4,
     dataTypeID: 1043,
     dataTypeSize: -1,
     dataTypeModifier: 259,
     format: 'text'   
  }
]

result.rows:

[ [ null ], [ null ], [ null ], [ null ], [ null ] ]