postgresql-10-pldebugger in alpine linux distribution

Hi, I'm looking for the package of postgresql-x-pldebugger for alpine Linux distribution. I found the package for Ubtuntu 18.04LTS on this link https://packages.ubuntu.com/bionic/postgresql-10-pldebugger but it does not exist in Alpine Linux https://pkgs.alpinelinux.org/packages?name=postgresql-pld

Re: Design of a database table

Actually, the data_periods contains a complete range such as [2018-09-01,2018-09-30] and data_sub_periods contains sub periods contained in this period like: [2018-09-05, 2018-09-07] [2018-09-09, 2018-09-11] [2018-09-12, 2018-09-19] I make two conditions in order to fetch first if the period [2018

Design of a database table

I'm trying to design a database table. First of all there are two alternatives: 1-) Divide the table into two tables and make a join. 2-) Design a single table. 1rst alternative: Create table data_periods( id serial primary key not null, period daterange, project_id integer ) create table data_pe

Using CTE vs temporary tables

Hi, I have a big query that used about 15 cte and its execution time is acceptable. I'm trying to optimize my query because it contains about 150 lines of code and becomes hard to understand it and add new filter or condition easily. I think to change some cte with temporary tables and using indexe

Re: Split daterange into sub periods

#x27;t master the usage of window functions such as 'over and partition'. I tried to resolve the problems by myself but no vain. Could you try to clarify me what is wrong with my query? Thanks. 2018-07-05 16:39 GMT+01:00 Andreas Kretschmer : > > > On 05.07.2018 15:49, hmidi slim w

Re: Split daterange into sub periods

In fact I'm trying to split a period in sub periods. Following this example : If I have a period =[2018-01-01, 2018-01-31] and two other periods [2018-01-04, 2018-01-06] and [2018-01-08, 2018-01-08]. If I split the base period '[2018-01-01, 2018-01-31]' by the other two periods '[2018-01-04, 2018-0

Split daterange into sub periods

Hi, I'm looking for splitting a daterange into many subperiods following this example: Base Date: [2018-01-01, 2018-01-31] overlapped_periods: 1- [ 2018-01-04, 2018-01-06] 2- [ 2018-01-09, 2018-01-12] 3- [ 2018-01-18, 2018-01-19] I try to get such a result: 1- [ 2018-01-01, 2018-01-03] 2- [ 2018-

Re: Return select statement with sql case statement

Actually, I need the use of case because based on the numberOfPremiumDays there are different type of treatment: select numberOfPremiumDays case when numberOfPremiumDays = date_part('day', ('2018-11-05'::timestamp) - ('2018-11-01'::timestamp)) then select product_id,

Return select statement with sql case statement

Hi, I need to use conditional expression in my query, So I want to make a query like this: select numberOfPremiumDays case when numberOfPremiumDays = date_part('day', ('2018-11-05'::timestamp) - ('2018-11-01'::timestamp)) then select product_id, price

When use triggers?

HI, I'm working on a microservice application and I avoid using triggers because they will not be easy to maintain and need an experimented person in database administration to manage them. So I prefer to manage the work in the application using ORM and javascript. However I want to get some opini

Best conception of a table

Hi, I want to get some different opinions and tips about two different conception. I want to create a table named proudct_price_period create table product_price_period { id integer , product_id integer, occupation_type_id integer, price_mode_id integer, price_period daterange, days_checked integer

Table schema inhancement

Hi, I have these tables: price_per_occupation: id (integer), product_price_period_id(integer), occupation_type(integer), price (numeric) product_price_period; id(integer), product_id(integer), is_monday(boolean), is_tuesday(boolean), is_wednesday(boolean), is_thursday(boolean), is_friday(boolean),

Using enum instead of join tables

Hi, Is it a good thing to use enum type such a column in a table instead of making a foreign key which references to another table? I found these links talking about enum and when I will use them: http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/ https://www.percona.com/blog/2008

Extract dates of a given day

Hi, I have a table availability: id (integer), product varchar(255), period (daterange) I want to extract dates from a given period matching a given day. E.g: for the period from *01/04/2018 - 30/04/2018* and for *day = Monday* I want to get *02/04/201809/04/201816/04/201823/04/201830/04/2018*

decompose big queries

Hi, I want to know what are the best practice to use in order to decompose a big query which contains so many joins.Is it recommended to use stored procedures ? or is there any other solution?

Re: How to get an inclusive interval when using daterange

HI, I tried* insert into availability values ('product x', '[2018-02-02,2018-03-01]'::daterange); *and I got the same result such as* insert into availability values ('product x', daterange('2018-02-02', '2018-03-01', '[]').*

Re: How to get an inclusive interval when using daterange

I tried it and I got the same result.

How to get an inclusive interval when using daterange

Hi, I have a table* availability* which contains 3 columns: * id, product_id and period_availability(type daterange).* When I insert a data into this table I use this query: insert into availability values ('product x', daterange('2018-02-02', '2018-03-01', '[]') So I get a period like this: [201

Query performance with cluster

Hi, I'm creating this table : *create table availability(product_id integer, product_name varchar(255), during daterange);* *create index time_idx on availability using gist(during);* *insert into availability* *select p.id , concat('prod ', p.id::text), daterange((now() + concat(r

Re: Proposition for better performance

This is the message that I got: Successfully run. Total query runtime: 2 min. 1500 rows affected.

Re: Proposition for better performance

I update the example: *create table product_availabilities(product_id integer, start_date date, end_date date); insert into product_availabilities(product_id, start_date, end_date) select a, '2018-01-01', '2018-05-03' from generate_series(1,1500) as aalter ta

Re: Proposition for better performance

the query that I used to fetch products was: select * from availability where ('27-03-2018' between start_date and end_date) and ('31-03-2018' between start_date and end_date); I added another column named during of type daterange and I created a gist index : create index idx on availability(durin

Proposition for better performance

Hi, I'm trying to create an availability table for existing products. I'm fetching the suitable schema to design in order to get good performance when I fetch products in a table contains millions of rows. I think to make a schema like this: *create table availability (product_id integer, product_n

Query performance

I want to compare the performance of some queries so I created those tables: *CREATE TABLE reservation1 (room int, name varchar(255), during daterange);CREATE TABLE reservation2 (room int, name varchar(255), start_date date, end_date date);* then: *INSERT INTO public.reservation1(name, room,

Re: Could not open extension control file “/usr/share/postgresql/10/extension/pg_similarity.control”: No such file or directory

I didn't find the file pg_similarity.control when I run find / -name 'pg_similarity.control' 2> /dev/null

Could not open extension control file “/usr/share/postgresql/10/extension/pg_similarity.control”: No such file or directory

Hi, I’m trying to create the extension pg_similarity ( https://github.com/eulerto/pg_similarity) in a docker container. Dockerfile: FROM postgres:10 ENV POSTGRES_USER user ENV POSTGRES_PASSWORD user ENV POSTGRES_DB user_db RUN apt-get update \ && ap

query performance

Hi, I have two tables: establishment which contains these columns: id, name, longitude, latitude, geom (Geometric column) Product contains: id, name, establishment_id First of all I want to select the establishment within a radius. I run this query: select e.name, e1.name from establishment as e, e

Join query

Hi, I have two tables: establishment which contains these columns: id, name, longitude, latitude, geom (Geometric column) Product contains: id, name, establishment_id First of all I want to select the establishment within a radius. I run this query: select e.name, e1.name from establishment as e, e

query's performance

Hi, I have a table establishment which contains these columns: id, name, longitude, latitude. I want to select all the establishments in the table establishment within a radius from a reference point which is a given establishment. For example: I have a given establishment called establishment1 an

Re: Notify client when a table was full

I need to be notified when the table is full to launch a script whis dumps this table. H.S 2018-01-19 16:44 GMT+01:00 David G. Johnston : > On Fri, Jan 19, 2018 at 8:34 AM, hmidi slim wrote: > >> Hi, >> I'm looking for a function in postgresql which notify the client if

Notify client when a table was full

Hi, I'm looking for a function in postgresql which notify the client if a table was full or not.So I found the function Notify https://www.postgresql.org/docs/9.0/static/sql-notify.html. This function send a notification when a new action was done to the table. Is there a way to send a notification

Bad performance when inserting many data simultanously

Hi, I'm creating an application( microservice architecture using dokcer containers) and I need to save a huge number of data in multiple tables in the same time. I have a table provider which has the different columns: name, status, address, contact. The table establishment contains: name, status,

Re: OPtimize the performance of a query

Thank you for your advices and thanks for all people who give me some best practises and useful ideas.

Re: OPtimize the performance of a query

I changed the operator like and I'm using the operator = .I got the results much faster but I still have another question about operator. For difference should I use '<>' or 'is distinct from' with indexes? 2018-01-16 17:49 GMT+01:00 Pavel Stehule : > Hi > &g

Re: OPtimize the performance of a query

Sorry I forget the lower command when I wrote the code, it is like this: lower(g.country_code) like lower('US') (lower(g.feature_class) like lowwer('P') or lower(g.feature_class) like lower('L')) 2018-01-16 17:40 GMT+01:00 Martin Moore : > > > >Hi, > > >I have two tables in the same database: ge

OPtimize the performance of a query

Hi, I have two tables in the same database: geoname and test_table. The geoname table contains many columns which are: name, feature_class, feature_code, admin1, admin2,admin3, name and so on. The second table 'test_table' contains only the columns: city, state. There is no join between the two tab

pool connection

HI, I'm trying to understand the utility of pool connection, I found that it's useful to enhance the performance of a database. However I want to know if it is a good option to put a large number in max_connection option to handle a large number of connection in an app?

Re: Does postgresq database supports reading data from the same table from many observable simultanousely?

But is it possible to make simultanoues select queries from the same connection? 2017-12-22 20:00 GMT+01:00 Andreas Kretschmer : > On 22 December 2017 19:53:03 CET, hmidi slim > wrote: > >Hi, > >I don't know if is it the suitable place to ask my question but I want > &

Does postgresq database supports reading data from the same table from many observable simultanousely?

Hi, I don't know if is it the suitable place to ask my question but I want to know if a table in a postgres database enable multiple reading actions for mayn rxjs observbles or not. I create two observables that need to access the same table simultanously to get data, but I found that only one of t

Does the delete+insert is fater and less cpu consumer than update?

Hi, I have a table named 'establishment' which has a join with a table 'category' and a table 'product'. I added indexes to foreign keys to enhance the execution time of the update or delete of data. I have to update the table establishment when I get a new data received from an external api. The a

Why the creation of spatial indexes decrease the performance of a query?

Hi, I'm using postgresql 9.6.4 on linux and I'm trying to execute this query on a table contains 51000 rows. and I got this query plan: ​​ Creation of index: ​I get this queryPlan: ​Why the execution time increases when creating a spatial index on a table containing a large amount of data?

Why the planner does not use index for a large amount of data?

Hi, When I used explain I found that the query planner use a seq scan to execute a query on a table containing about 2 millions rows.However I'm creating an index.Why does the planner uses seq scan in place of index scan?Does the execution of index scan is slower with table containing a huge amount

Re: How clear the cache on postgresql?

I execute the command sync && echo 3 > /proc/sys/vm/drop_caches​ but I still got buffers hit added to that buffers read now.Why I got the buffers hit? 2017-11-24 17:55 GMT+01:00 Tom Lane : > bricklen writes: > > On Fri, Nov 24, 2017 at 6:54 AM, hmidi slim > wrote: >

How clear the cache on postgresql?

I'm trying to analyze some queries using the explain instructions and the option analyze and buffers. I realized that the query refers to the cache memory to return the results. Is there any solution to clear the cache in postgresql inorder to get execution time of the query when it get data from d

Re: Buffers option

4 13:46 GMT+01:00 Luca Ferrari : > On Fri, Nov 24, 2017 at 12:30 PM, hmidi slim > wrote: > > I didn't get the number of read and hit buffers when I rexecute the query > > many times.How can I Fix that? and I tried the options costs, timing and > I > > got the same pl

Buffers option

I'm trying to anlayze a query in postgresql v9.6.4: explain (ANALYZE,BUFFERS) SELECT p.* FROM place as p INNER JOIN user as u ON p.id = u.place_id where to_tsvector('simple', p.name) @@ to_tsquery('simple', 'a:*') and u.status = true; The QueryPlan was : Hash Join (cost=20.28..3