Change record (psql UPDATE) on aggregate DB in docker build

Support
1

I'd like to modify records submitted through ODK-Collect on the PostgreSQL database on our aggregate server built on Docker Image; the server is working great, receives filled forms, pulls and pushes through Briefcase, everything OK; BUT on the server the "psql aggregate" and "aggregate#= \dt" commands don't show any database at all,
So I just want to "UPDATE" with psql on the database of a form, but first I have to find them, I have no clue about where are they stored on our server

1 Like
2

I am not an expert in this field But if you find the solution, I am interested

3

Hi Clarc, I haven't found the way I was looking for (psql), but found it is possible on pgadmin, you just add your credentials and you get to the aggregate database, there you can change records by double-clicking or inject sql code (important: you must designate a column as unique identifier)

4

I've finally found the tables... (I'm a beginner in postgres); if you type "\dn" you'll get your schemas, "aggregate" among them; if you type "\dt aggregate.*" you'll get the tables; you have to set the search path with: "SET searth_path TO mi_schema, public;" and then the normal queries are usable, such as: "select * from aggregate."RECOLECTA_SEMILLA_CORE";"

1 Like
5

Hello @eliodiaz , can you share some image about that?.
Thanks.

6

Hi famador; this is what I typed in the psql shell (you have to type psql aggregate on the terminal)
\dt --shows tables
\dn --shows schemas
\dt mi_schema.* --shows all tables in a schema
SHOW search_path; --shows current search path
SET search_path TO mi_schema, public; --so my schema can be searched
select * from aggregate."RECOLECTA_SEMILLA_CORE"; --simple selection
--group and count
select aggregate."RECOLECTA_SEMILLA_CORE"."SPECIES", COUNT(*) from aggregate."RECOLECTA_SEMILLA_CORE" GROUP BY aggregate."RECOLECTA_SEMILLA_CORE"."SPECIES";

-- change a value that was collected through ODK collector app
UPDATE aggregate."RECOLECTA_SEMILLA_CORE" SET "VAR" = 'SIN VARIEDAD' WHERE "VAR" = 'SVN';

1 Like
7

Great, thanks for the query :blush:*