Postgres select query columns coming back as array?

mxkxf · February 12, 2025, 10:30am

Hi,

Bit of a weird one, I'm running some resource queries against a Postgres database and the columns are coming back as arrays:

I would expect id (and the other properties) to be their scalar types, rather than array containing a single element. Am I doing something wrong or is this expected?

Thanks

MiguelOrtiz · February 12, 2025, 1:48pm

Hi there @mxkxf and welcome to the forum!

Yeah, the DB connectors do a lot of data transforming to set up query results to be very easy for the table component to read. You can use your query's "transform data" section with return formatDataAsArray (data) to get the data as a single object (i.e. columns and values)

Hope this helps!

mxkxf · February 12, 2025, 1:53pm

Thanks for replying @MiguelOrtiz - understood.

What still puzzles me is that sometimes adding a transformer is not required, for instance:

GetOrganisation.data.name is a string
GetOrganisation.data.postcode is an array containing one string element

They are both the same data type in the database schema Is there any rhyme or reason here?

AbbeyHernandez · February 13, 2025, 2:51pm

Hey @mxkxf, that's very odd! Would you mind sharing screenshots of these occurances? Is this a Retool db resource? If you click schema and open Organization what do the column types look like? Can I see screenshots of that as well? Thanks!