Re: How to execute the sql file in PSQL
Actually, a single quotation will work. \ir 'C:\\Program Files\\PostgreSQL\\12\\demo-big-en-20170815.sql' But I don't know why a single quotation will work. It would be very helpful if you guys can explain to me. On Mon, Oct 5, 2020 at 9:31 PM Tom Lane wrote: > Adrian Klaver writes: > > On 10/5/20 7:55 AM, Mark wrote: > >> I followed one PostgreSQL tutorial step by step. One session to use > PSQL > >> to execute sql files to create a new database in PostgreSQL. > >> 1. copy paste the sql file within "C:\Program Files\PostgreSQL\12" > >> directory. > >> 2. execute the following code \i C:\\Program > >> Files\\PostgreSQL\\12\\demo-big-en-20170815.sql > >> > >> Failed > >> The message shows C:: Permission denied > > > The issue is the user you are running psql as does not have permissions > > to read the file. This is a OS permissions thing. Either run psql as a > > user that can read the file or change the permissions on the file to > > those that allow the psql user to read it. > > ... or more likely, put the SQL file in a saner place. Dropping random > files into a program directory can only lead to trouble. You should > treat such directories as read-only unless you know exactly what you > are doing. > > It seems likely to me that this failure stems from PG being installed > with permissions settings that prevent it from reading/modifying its own > executables, which is good solid security practice. > > (If the tutorial actually told you to do that, the tutorial's author > is utterly clueless.) > > regards, tom lane >
RE: Handling time series data with PostgreSQL
Dear All, I'm Jayaram S, oracle DBA. Currently we are planning to develop a stock market based application which deals 80% of data with time data. We are in the process of choosing the right database for the requirement especially for time series data. After all multiple investigations, I found PostgreSQL with timescaleDB works better than other DBs. But still I'm new to PGSQL and we wanted only open source technology to handle our requirements. It will be helpful to me if anyone can suggest implementing the time series concepts in PostgreSQL database. It's better if I can get proper docs or links with explanation. Thanks in advance., -- *Thanks & Regards,Jayaram S,Banglore.India.*
Re: How to execute the sql file in PSQL
Hi Mark, > On 06. Oct, 2020, at 10:23, Mark wrote: > > Actually, a single quotation will work. > \ir 'C:\\Program Files\\PostgreSQL\\12\\demo-big-en-20170815.sql' > But I don't know why a single quotation will work. > It would be very helpful if you guys can explain to me. because Microsoft had the glorious idea of putting a blank character into "Program Files". Quoting this will make the whole C:\...sql line one single word which is then passed to \i. Cheers, Paul
Re: How to update a table with the result of deleting rows in another table
> On 6 Oct 2020, at 7:37, Hemil Ruparel wrote: > > I am trying to delete orders for a given customer on a given date and add the > cost of those orders to credit for the customer. > > So far, I came up with this: > ``` > with data as ( > delete from orders > where customer_id = > and date = '2020-10-05' returning price > ), total as ( > select sum(price) from data > ) > update paymentdetail > set temp_credit = temp_credit + (select * from total) > where customer_id = > ``` > > which works. but is there a better way to update one table using the result > of deleting rows from another table given that I only want the aggregate of > the result? Adding the customer id to your returning clause and using update..from could help: with data as ( delete from orders where customer_id = returning customer_id, price ), total as ( select customer_id, sum(price) as total_price from data group by customer_id ) update paymentdetail set temp_credit = temp_credit + total.total_price from total where customer_id = total.customer_id You could also do this using subqueries instead of CTE’s, that may perform better as CTE’s act as optimisation fences. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
Re: What's your experience with using Postgres in IoT-contexts?
On Mon, 5 Oct 2020 at 16:34, Thorsten Schöning wrote: > Guten Tag Tony Shelver, > am Montag, 5. Oktober 2020 um 15:44 schrieben Sie: > > > Not sure about PG in that environment. Have you thought about something > > like H2 java database? https://www.h2database.com/html/main.html > > Yes, like SQLite as well. The point is that I was really interested in > keeping as much as possible and we e.g. use some Postgres-specific > constructs currently. Of course things can be changed, but the > question is if it's necessary at all, especially after reading the > PDF: > > > https://www.postgresql.eu/events/pgconfeu2019/sessions/session/2781/slides/251/PostgreSQL%20for%20IoT.pdf > > > That is included as the standarDB in a vehicle tracking system we use, > > although we have re[placed with PG. > > And your tracking system is more like the server that I already have > or an embedded system within the vehicles themself? > > Mit freundlichen Grüßen, > > Thorsten Schöning > > -- > Thorsten Schöning E-Mail: thorsten.schoen...@am-soft.de > AM-SoFT IT-Systeme http://www.AM-SoFT.de/ > > Telefon...05151- 9468- 55 > Fax...05151- 9468- 88 > Mobil..0178-8 9468- 04 > > AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln > AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow > > > . My tracking system stores, analyzes and reports on multiple events occuring in the vehicle. This can include speed, change in GPS location (tracking), vehicle data / events such as RPM, engine temperature, change in direction, data from the OBDII system (vehicle diagnostics from engine, transmission, cooling and electrical systems, chassis and so on), data from the tracker's built-in accelerometer, connect / disconnect, ignition on / off and so on This data is used to trigger operator / admin messages and warnings, report historical vehicle routes on map, follow current vehicle position and route, geofencing, and more. Each vehicle reports position to the database at least every 30 seconds when moving, along with any other data as it happens. So it's quite a bit. H2 seemed to handle it fine for a limited number of vehicles, but we moved it to PG once we moved to production.
[SOLVED] Re: UUID generation problem
Thank you all for the help. This is what ultimate resolved the issue for me:
[root@accounting-2 ~ (master)]# psql -E --dbname=idempiere --username=postgres
--host=localhost
Password for user postgres:
psql (11.8)
Type "help" for help.
idempiere(5432)=# select current_schemas(true);
current_schemas
-
{pg_catalog,public}
(1 row)
idempiere(5432)=# ALTER ROLE idempiere_dbadmin SET search_path TO
adempiere,public;
ALTER ROLE
idempiere(5432)=# \q
[root@accounting-2 ~ (master)]# psql -E --dbname=idempiere
--username=idempiere_dbadmin --host=localhost
Password for user idempiere_dbadmin:
psql (11.8)
Type "help" for help.
idempiere(5432)=# select current_schemas(true);
current_schemas
---
{pg_catalog,adempiere,public}
--
*** e-Mail is NOT a SECURE channel ***
Do NOT transmit sensitive data via e-Mail
Unencrypted messages have no legal claim to privacy
Do NOT open attachments nor follow links sent by e-Mail
James B. Byrnemailto:byrn...@harte-lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
Re: How to update a table with the result of deleting rows in another table
> > Adding the customer id to your returning clause and using update..from > could help: > > with data as ( > delete from orders > where customer_id = > returning customer_id, price > ), total as ( > select customer_id, sum(price) as total_price > from data > group by customer_id > ) > update paymentdetail > set temp_credit = temp_credit + total.total_price > from total > where customer_id = total.customer_id > You could skip the "total" cte and just update the same rows repeatedly. I'm not sure if the same row being repeatedly updated in the same statement creates additional row versions or just updates the existing one. > ...CTE’s act as optimisation fences. > It might be worth noting PG12 changes that behavior in simple cases where the CTE is not recursive, not referenced more than once, and is side-effect free.
Re: Handling time series data with PostgreSQL
Hi, TimescaleDB as a Postgresql extension has been used in my firm for two years now, I've recently managed to upgrade it from pg10 to pg12 and from discrete VM's to Kubernetes as well. Frankly speaking, being new to TimescaleDB at that time, I've found it easy to manage, easy to scale (it's 100% compatible with pg replication, unfortunately not the logical one, yet...), easy to install, easy to upgrade... what else? >From a developer's perspective, it just adds "superpowers" to ordinary PG tables, all under the hood. On disk, it features a "chunked" layout, where each chunk belongs to a definite "time" range; and of course the "time" column on which to index time data is just passed as a parameter to the call to TimescaleDB, for each table on which you need such power. At the moment, we're also using it for time aggregate calculations, but only for the coarse ones (30m --> 1h and 1h --> 1 day), while we're still handling the finer ones (1s --> 1m and so on) in Kafka+Flink, which is a common scenario for a streaming data platform, anyway. Regards, Adalberto Il giorno mar 6 ott 2020 alle ore 11:47 Jayaram ha scritto: > Dear All, > > I'm Jayaram S, oracle DBA. Currently we are planning to develop a stock > market based application which deals 80% of data with time data. We are in > the process of choosing the right database for the requirement especially > for time series data. After all multiple investigations, I found PostgreSQL > with timescaleDB works better than other DBs. > > But still I'm new to PGSQL and we wanted only open source technology to > handle our requirements. It will be helpful to me if anyone can suggest > implementing the time series concepts in PostgreSQL database. > It's better if I can get proper docs or links with explanation. > > Thanks in advance., > > -- > > > > > *Thanks & Regards,Jayaram S,Banglore.India.* >
Re: What's your experience with using Postgres in IoT-contexts?
Guten Tag Tony Shelver, am Dienstag, 6. Oktober 2020 um 16:33 schrieben Sie: > Each vehicle reports position to the database at least every 30 seconds > when moving, along with any other data as it happens. So it's quite a bit. > H2 seemed to handle it fine for a limited number of vehicles, but we moved > it to PG once we moved to production. But where does Postgres run in your setup? :-) On the tracked vehicle itself with limited resources most likely or do you simply send things over network to some cloud/server/...? Your first paragraph reads like the former, but the second like the latter. :-) If it's on the vehicle, I would be interested to somewhat know which hardware you use, to compare what I have in mind. Thanks! Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning E-Mail: thorsten.schoen...@am-soft.de AM-SoFT IT-Systeme http://www.AM-SoFT.de/ Telefon...05151- 9468- 55 Fax...05151- 9468- 88 Mobil..0178-8 9468- 04 AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow
