how can we read column data (bytea field) of table device_up in postgres? anything required?
you can read data (bytea fileld) of table device_up using below select query.
SELECT encode(dev_eui::bytea, 'hex') as dev_eui, encode(data::bytea, 'base64') as data FROM public.device_up
3 Likes
Hi! I am trying to store device events in the PostgreSQL database, created the database and configured the PostgreSQ integration.
After this ChirpStack Application Server does not open. And when I remove the integration setting it opens again. In addition to creating one in the database, with tables and interaction configuration. Is there anything else?
Usually checking the logs is a good start to debug issues 
1 Like
howdy,
not sure what I got wrong here, i am getting this error in the logs
Jan 21 21:41:02 rak-gateway chirpstack-application-server[3268]: time=“2020-01-21T21:41:02Z” level=error msg=“integration/multi: integration error” ctx_id=df35ab4e-b9f7-40fe-97cb-c69764091458 error=“insert error: pq: unsupported Unicode escape sequence” integration="*postgresql.Integration"
in my config file I got:
[application_server.integration]
enabled=[“mqtt”,“postgresql”]
[application_server.integration.postgresql]
dsn=“postgres://chirpstack_as_events:dbpassword@localhost/chirpstack_as_events?sslmode=disable”
any idea?
guess it was posted on the forum before, don’t see a solution for it though
here: Insert error: pq: unsupported Unicode escape sequence
and here: Integration Application Server with PostgreSQL
ok problem identified, there was a payload codec function my friend put in for this device-profile that created some illegal characters so inserting into postgresql was failing. for now I removed the decoder code and it is working now.
the issue was found in the postgresql logs /var/log/postgresql/postgresql-9.6-main.log
so move on, nothing here to see
3 Likes
Thanks for the integration,
Whether it is possible for each application to have its own table or for each device to have its own table?
How do you think this integration will work, with a large number of devices such as 20,000 pieces?
Hello jlandercy, did you finally get the data in the table? im having the same issue: 0 rows
Can you store downlinks to?
No, the PostgreSQL integration stores uplink related events only. However, it is going to store txack events in the next release (acknowledgement that a downlink was accepted by the gateway for downlink transmission).
I am getting the following error. I have followed all the steps in PostgreSQL - ChirpStack open-source LoRaWAN<sup>®</sup> Network Server
Initially, I have not created tables and index
Later I have created the tables as per the description of this ticket. Then also I was getting the same error
What am I missing here? @brocaar
time=“2021-11-01T11:46:11Z” level=error msg=“integration/multi: integration error” ctx_id=ac4504a4-bd17-491d-96d9-3d5c7f068203 error=“insert error: pq: column "tags" of relation "device_up" does not exist” integration=“*postgresql.Integration”
Hi brocaar! I have a simple but vital question to continue with the integration. We have been able to get the integration with postgreSQL up and running. In the event_up table we can see each message that the devices send but we see the DATA field with the Base64 encoded information. Our intention is to share the data with an external system connected to Postgre but that the information is not encoded. What do you recommend?
If you have configured a payload coded, you will find the decoded data in the object column.
So this change is for ChirpStack v3?
Does ChirpStack v4 have this feature?
Thanks a lot and have a nice weekend.
Yes, this feature is also in v4 present.
1 Like
have you changed the schema though in v4? I saw in postgres logs some insert statements on event_up table but i created the device_up as you had in your example
Thanks!
The ChirpStack v4 data-base schema is different from v3. Please find the v4 schema for the integration here (it is automatically applied):
1 Like