Re: [GENERAL] Help on Index only scan
> On 14 Aug 2017, at 01:15, Melvin Davidson <melvin6...@gmail.com> wrote:
>
>
>> On Sun, Aug 13, 2017 at 5:59 PM, Ertan Küçükoğlu
>> <ertan.kucuko...@1nar.com.tr> wrote:
>> Hello,
>>
>> My table details:
>> robox=# \dS+ updates
>>Table "public.updates"
>> Column | Type | Modifiers
>> | Storage | Stats target | Description
>> ---+-+--
>> -+--+--+-
>> autoinc | integer | not null default
>> nextval('updates_autoinc_seq'::regclass) | plain| |
>> filename | text|
>> | extended | |
>> dateofrelease | date|
>> | plain| |
>> fileversion | text|
>> | extended | |
>> afile | text|
>> | extended | |
>> filehash | text|
>> | extended | |
>> active| boolean |
>> | plain| |
>> Indexes:
>> "updates_pkey" PRIMARY KEY, btree (autoinc)
>> "update_filename" btree (filename)
>> "updates_autoinc" btree (autoinc DESC)
>> "updates_dateofrelease" btree (dateofrelease)
>> "updates_filename_dateofrelease" btree (filename, dateofrelease)
>>
>>
>> robox=# select count(autoinc) from updates;
>> count
>> ---
>> 2003
>> (1 row)
>>
>> robox=# select autoinc, filename, fileversion from updates limit 10;
>> autoinc | filename | fileversion
>> -+--+-
>> 18 | Robox.exe| 1.0.1.218
>> 19 | Robox.exe| 1.0.1.220
>> 20 | Robox.exe| 1.0.1.220
>> 21 | 8423bfc5a669864f9b66b6b15ce908b9 | 1.1.1.1
>> 22 | 4fdabb0c7adbc5a89fbe679ce76ccef9 | 1.1.1.1
>> 23 | f469d77bfa86c8917c7846c0f871137c | 1.1.1.1
>> 24 | bc10af4c8789718a9ca6565ea14cb17d | 1.1.1.1
>> 25 | d9f87ee46cdb41cd15c2f71ed599faf9 | 1.1.1.1
>> 26 | 6f7428a5364aae1d5914a66cba3e6f3b | 1.1.1.1
>> 27 | 66ec4cdb8d64ca1414f75c1fb9eaa518 | 1.1.1.1
>> (10 rows)
>>
>> I want to have an index only scan for my below query:
>> select autoinc, fileversion from updates where filename = 'Robox.exe' order
>> by autoinc desc;
>>
>> I simply could not understand planner and cannot provide right index for it.
>> Below index names "update_filename" and "updates_autoinc" are added just for
>> the query that I would like to have a index only scan plan. I also failed
>> with following indexes
>> "autoinc desc, filename, fileversion"
>> "autoinc desc, filename"
>>
>> First 3 rows in above select results are actual data. You will find that I
>> have inserted about 2000 rows of dummy data to have somewhat meaningful plan
>> for the query.
>>
>> Current planner result:
>> robox=# vacuum full;
>> VACUUM
>> robox=# explain analyze
>> robox-# select autoinc, fileversion
>> robox-# from updates
>> robox-# where filename = 'Robox.exe'
>> robox-# order by autoinc desc;
>> QUERY PLAN
>>
>> --
>> Sort (cost=12.79..12.79 rows=3 width=12) (actual time=0.047..0.047 rows=3
>> loops=1)
>>Sort Key: autoinc DESC
>>Sort Method: quicksort Memory: 25kB
>>-> Bitmap Heap Scan on updates (cost=4.30..12.76 rows=3 width=12)
>> (actual time=0.040..0.040 rows=3 loops=1)
>> Recheck Cond: (filename = 'Robox.exe'::text)
>> Heap Blocks: exact=1
>> -> Bitmap Index Scan on update_filename (cost=0.00..4.30 rows=3
>> width=0) (actual time=0.035..0.035 rows=3 loops=1)
>>Index Cond: (filename = 'Robox.exe'::text)
>> Planning time: 1.873 ms
>> Execution time: 0.076 ms
>> (10 rows)
>>
>>
>> I appreciate any help on having right index(es) as I simply failed myself.
>>
>> Regards,
>> Ertan Küçükoğlu
>>
>
> First, you do not need index "updates_autoinc", since autoinc is the Primary
> Key, you are just duplicating the index.
Is that true even if that index is a descending on
[GENERAL] Help on Index only scan
Hello,
My table details:
robox=# \dS+ updates
Table "public.updates"
Column | Type | Modifiers
| Storage | Stats target | Description
---+-+--
-+--+--+-
autoinc | integer | not null default
nextval('updates_autoinc_seq'::regclass) | plain| |
filename | text|
| extended | |
dateofrelease | date|
| plain| |
fileversion | text|
| extended | |
afile | text|
| extended | |
filehash | text|
| extended | |
active| boolean |
| plain| |
Indexes:
"updates_pkey" PRIMARY KEY, btree (autoinc)
"update_filename" btree (filename)
"updates_autoinc" btree (autoinc DESC)
"updates_dateofrelease" btree (dateofrelease)
"updates_filename_dateofrelease" btree (filename, dateofrelease)
robox=# select count(autoinc) from updates;
count
---
2003
(1 row)
robox=# select autoinc, filename, fileversion from updates limit 10;
autoinc | filename | fileversion
-+--+-
18 | Robox.exe| 1.0.1.218
19 | Robox.exe| 1.0.1.220
20 | Robox.exe| 1.0.1.220
21 | 8423bfc5a669864f9b66b6b15ce908b9 | 1.1.1.1
22 | 4fdabb0c7adbc5a89fbe679ce76ccef9 | 1.1.1.1
23 | f469d77bfa86c8917c7846c0f871137c | 1.1.1.1
24 | bc10af4c8789718a9ca6565ea14cb17d | 1.1.1.1
25 | d9f87ee46cdb41cd15c2f71ed599faf9 | 1.1.1.1
26 | 6f7428a5364aae1d5914a66cba3e6f3b | 1.1.1.1
27 | 66ec4cdb8d64ca1414f75c1fb9eaa518 | 1.1.1.1
(10 rows)
I want to have an index only scan for my below query:
select autoinc, fileversion from updates where filename = 'Robox.exe' order
by autoinc desc;
I simply could not understand planner and cannot provide right index for it.
Below index names "update_filename" and "updates_autoinc" are added just for
the query that I would like to have a index only scan plan. I also failed
with following indexes
"autoinc desc, filename, fileversion"
"autoinc desc, filename"
First 3 rows in above select results are actual data. You will find that I
have inserted about 2000 rows of dummy data to have somewhat meaningful plan
for the query.
Current planner result:
robox=# vacuum full;
VACUUM
robox=# explain analyze
robox-# select autoinc, fileversion
robox-# from updates
robox-# where filename = 'Robox.exe'
robox-# order by autoinc desc;
QUERY PLAN
--
Sort (cost=12.79..12.79 rows=3 width=12) (actual time=0.047..0.047 rows=3
loops=1)
Sort Key: autoinc DESC
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on updates (cost=4.30..12.76 rows=3 width=12)
(actual time=0.040..0.040 rows=3 loops=1)
Recheck Cond: (filename = 'Robox.exe'::text)
Heap Blocks: exact=1
-> Bitmap Index Scan on update_filename (cost=0.00..4.30 rows=3
width=0) (actual time=0.035..0.035 rows=3 loops=1)
Index Cond: (filename = 'Robox.exe'::text)
Planning time: 1.873 ms
Execution time: 0.076 ms
(10 rows)
I appreciate any help on having right index(es) as I simply failed myself.
Regards,
Ertan Küçükoğlu
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ZeosLib database components - uuid inserts using libpq
Hello, I recently found a bug on open source ZeosLib Database components for Lazarus (Object Pascal) which is when ZeosLib is used with PostgreSQL database server. Bug was that library simply removes any uuid column from select result set. Problem is now solved and current SVN includes fixes (fix was to first define uuid field type in library then read 16 bytes for uuid defined columns and finally convert byte information to string representation for displaying on the result set). However, my additional tests on latest SNV version of the library resulted that there is a new bug while inserting values to uuid columns. Provided uuid value to be inserted is wrong in relevant columns. Considering string representation of uuid, only last section of the inserted uuid is correct. Discussing with ZeosLib developers and they are not sure how to pass that uuid value to libpq while saving. Current questions are; - Should it be direct string representation or 16 byte raw value? - If 16 byte raw value, is there any special way to build that 16 bytes from string representation? As nobody can be sure on ZeosLib side and I could not find such information on libpq documents on PostgreSQL web site. I decided to ask here for help. Thanks & regards, Ertan Küçükoğlu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Database structure advise
Hello All, Using PostgreSQL 9.6.2 on a Windows 64bit platform. I am about to start a new software development dealing with warehouse operations. Software should handle multi-company structure. There will be single company starting to use the software at the beginning. I need to decide between below scenarios before I develop any database structure. Biggest table I am assuming will be inventory movements table. I am expecting about 90% of the queries will run on that table. I do not see that table to have more than 100.000 rows in a year for a single company. I am not planning to have a new database for each year. Data will pile up in time. My current scenarios are: 1) Separate company records by adding a field in each table and keeping all records in a single database and schema. (Queries will be easier to build and run) 2) Separate company records by adding a new schema for each company there may be keeping all records in a single database. (Queries will need UNION across schemas for corporate reports) 3) Separate company records by creating a new database for each company. (This option, I do not want to do. Will think about corporate reports if I have to select this one). I appreciate any suggestion on above possible options. New suggestions, experience sharing are always welcome. Thanks & regards, Ertan Küçükoğlu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Advise on primary key for detail tables (OS: Raspberry Pi)
-Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Thursday, April 6, 2017 4:18 PM To: Ertan Küçükoğlu <ertan.kucuko...@1nar.com.tr>; pgsql-general@postgresql.org Subject: Re: [GENERAL] Advise on primary key for detail tables (OS: Raspberry Pi) > On 04/06/2017 03:27 AM, Ertan Küçükoğlu wrote: > > Sorry for top posting. > > > > I have a serial in master table because I need to know data insertion order. > > DateTime on Raspberry Pi is not accurate due to power loss and lack of > > internet access to fetch correct time from. > > Understand, though it does beg the question, why have the uuid column? > > -- > Adrian Klaver > adrian.kla...@aklaver.com Programmatically it is easier as I can start saving from very bottom detail table instead of having to save first the master table record. Users will directly work on detail tables. Master table will be saved & updated by software automatically as last piece of information. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Advise on primary key for detail tables (OS: Raspberry Pi)
Sorry for top posting. I have a serial in master table because I need to know data insertion order. DateTime on Raspberry Pi is not accurate due to power loss and lack of internet access to fetch correct time from. -Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Wednesday, April 5, 2017 7:17 PM To: Ertan Küçükoğlu <ertan.kucuko...@1nar.com.tr>; pgsql-general@postgresql.org Subject: Re: [GENERAL] Advise on primary key for detail tables (OS: Raspberry Pi) On 04/05/2017 08:04 AM, Ertan Küçükoğlu wrote: > Hello, > > I have a project which will be mainly built on Raspberry Pi and some > parts on Windows. > > I must have a PostgreSQL 9.4.10 running on Raspberry Pi and another > PostgreSQL running on Windows. Though, there is still a possibility > that Windows database server will be something else that is not known to me, yet. > Since Raspberry Pi is running on a SD Card, data saved on Raspberry Pi > will be copied over to Windows database system for a proper backup & > disaster recovery. > > I need to keep database server overhead as low as possible on > Raspberry Pi system. That is because software that will be a running > is going to do some time essential sensor communication. > > I am about to start table designs on Raspberry Pi. There is one > master-detail-detail-detail structure I should implement. Master > having serial, uuid and some varchar fields. Uuid field being primary > key. Details have serial, uuid and some smallint fields. So what the serial column in the master table for? > > I recall that it is "generally" advised to have a primary key on any > table used on a database server. > What is advised is to have some way of determining uniqueness for a row. A PK is the simplest way of doing that, also many ORMs will not work without one. Now a PK can be a single value such as the serial column in your details tables or it can be over multiple columns that determine uniqueness. Again you have to be aware of what the application/interface that is using the tables is capable of. In the case of ORMs, they often do not understand multi--column PKs. This is why PKs on a auto-incrementing(serial) integer are often recommended. > My question is: Is reading performance will be faster, if I remove > primary key on serial fields of detail tables and use a regular index > put on master table link fields only? In another words, is it > advisable *not* to have a primary key on PostgreSQL table? > > If answer changes according to OS underlying, I appreciate replies > indicates so. > > Thanks & regards, > Ertan Küçükoğlu > > > -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Advise on primary key for detail tables (OS: Raspberry Pi)
Hello, I have a project which will be mainly built on Raspberry Pi and some parts on Windows. I must have a PostgreSQL 9.4.10 running on Raspberry Pi and another PostgreSQL running on Windows. Though, there is still a possibility that Windows database server will be something else that is not known to me, yet. Since Raspberry Pi is running on a SD Card, data saved on Raspberry Pi will be copied over to Windows database system for a proper backup & disaster recovery. I need to keep database server overhead as low as possible on Raspberry Pi system. That is because software that will be a running is going to do some time essential sensor communication. I am about to start table designs on Raspberry Pi. There is one master-detail-detail-detail structure I should implement. Master having serial, uuid and some varchar fields. Uuid field being primary key. Details have serial, uuid and some smallint fields. I recall that it is "generally" advised to have a primary key on any table used on a database server. My question is: Is reading performance will be faster, if I remove primary key on serial fields of detail tables and use a regular index put on master table link fields only? In another words, is it advisable *not* to have a primary key on PostgreSQL table? If answer changes according to OS underlying, I appreciate replies indicates so. Thanks & regards, Ertan Küçükoğlu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Latest PostgreSQL on Raspbian Jessie - solved
-Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Thursday, February 23, 2017 5:25 PM To: Ertan Küçükoğlu <ertan.kucuko...@1nar.com.tr>; pgsql-general@postgresql.org Subject: Re: [GENERAL] Latest PostgreSQL on Raspbian Jessie On 02/23/2017 06:08 AM, Ertan Küçükoğlu wrote: > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Adrian Klaver > Sent: Thursday, February 23, 2017 3:55 PM > To: Ertan Küçükoğlu <ertan.kucuko...@1nar.com.tr>; > pgsql-general@postgresql.org > Subject: Re: [GENERAL] Latest PostgreSQL on Raspbian Jessie > > > Hi, > > Sorry about not indenting. Have to use Outlook as e-mail app. > > I cannot show any work at the moment, I am just researching right now, > before I start actual job. I have a single form making connection to > local PostgreSQL and remote one. Local seems to be OK in all respects. > Remote is a problem. > > Below you can see some psql output. > > postgres@raspberrypi:~$ psql -d test -U postgres -p 5432 -h > 192.168.1.105 Password for user postgres: > DEBUG: CommitTransaction > DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, > xid/subid/cid: 0/1/0, nestlvl: 1, children: > psql (9.4.10, server 9.6.1) > WARNING: psql major version 9.4, server major version 9.6. > Some psql features might not work. > Type "help" for help. > > test=# select count(*) from sale; > DEBUG: StartTransactionCommand > DEBUG: StartTransaction > DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, > xid/subid/cid: 0/1/0, nestlvl: 1, children: > DEBUG: CommitTransactionCommand > DEBUG: CommitTransaction > DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, > xid/subid/cid: 0/1/0, nestlvl: 1, children: > count > > 176588 > (1 row) > > test=# > > Btw, I am surprised that psql can make a connection *and* runs a query > just fine. Yeah with the caveat: "Some psql features might not work." The older version of psql does not 'know' about new features in 9.6. > > My main problem with my application is to run a query. Connection > seems to be OK. Trying to run a query and my application simply freeze. So the issue is with the Postgres library that Lazarus/FreePascal is using. What would that library be? > > PgAdmin3 gives lots of error messages some objects missing, assertion > failures, etc. After all these messages PgAdmin3 seems to establish a > connection to PostgreSQL 9.6 database on Windows OS. However, PgAdmin3 > cannot run a query, too. When I try to do a select, PgAdmin3 freeze. > Waiting only kills my application, or PgAdmin3. No log messages that I can find of. Not all that surprising as pgAdmin3 is no longer supported: https://www.pgadmin.org/download/source.php As you found out, I don't think the last version of pgAdmin3 is compatible with 9.6. > > Thanks. > -Ertan > Problem solved, Actually, library works just fine as I indicated that I am not having any problem with local database connection which is also over TCP/IP (localhost). It turned out that I was doing a "simple" select * from query on a table with more than 1.6 million rows. Obviously, Raspberry Pi memory can't handle such a result set. After changing my source of select table into a smaller one things started to be smooth and nice. Sorry about all the fuss. Thanks. -Ertan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Latest PostgreSQL on Raspbian Jessie
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Adrian Klaver Sent: Thursday, February 23, 2017 3:55 PM To: Ertan Küçükoğlu <ertan.kucuko...@1nar.com.tr>; pgsql-general@postgresql.org Subject: Re: [GENERAL] Latest PostgreSQL on Raspbian Jessie On 02/23/2017 01:56 AM, Ertan Küçükoğlu wrote: > Hello, > > I could not decide which forum is more appropriate. I end up posting > my question here. > > For a small but important Project, I need to develop a GUI application > on Raspberry Pi with PostgreSQL using Lazarus. PostgreSQL 9.4.10 > server will be running on Raspberry Pi 3 Model B and another > PostgreSQL 9.6.2 will be running on a Windows OS. Lazarus installed on > Raspberry Pi is version 1.7 (built from latest subversion trunk > sources) using with FreePascal 3.1.1 (also built from latest subversion trunk sources). > > Raspberry Pi application will save some data in PostgreSQL running on > Pi and very same application also needs to save some *other* data on > PostgreSQL > 9.6.2 running on Windows OS. Both OSes will be running in same LAN. > > Saving everything on Raspberry Pi and later copying using another > application/method is not allowed by design. > > I could not find 9.6.x version of the PostgreSQL to install on > Raspberry Pi in packages. Most recent I can install using APT package > system is 9.4.10. I do not mind that it is rather old. > > My problem starts when I try to reach a PostgreSQL 9.6.2 using 9.4.10 > pglib library (I think). Can you show what is you are doing? If not what happens if you use the Postgres command line client(psql) to connect to the 9.6.2 database? > - Connection may hang and application stops responding. > - If connection succeeds a simple select * from query hangs and > application stops responding and after a while terminates for good. > > I installed PgAdmin3 on Raspberry Pi to make some tests. I get lots of > warnings and some error messages when I try to connect that database > on Windows OS. What are the warnings/errors? > > I am new to PostgreSQL and am not sure if there may be some connection > problems from old version libraries to new version PostgreSQL servers. > My limited little experience says I need same version on both systems > for best stability. > > I would like to know; > 1- Is it possible to install 9.6.2 with same version pglib on Raspberry Pi? > If possible to do on Raspberry Pi, I think I can build my own version > from sources assuming I am provided a good detailed how to document for doing so. > 2- If above is not possible, is it possible to build just the latest > version pglib and overcome my connection problem? > 3- If I need to select number 2 solution above, is that latest version > library will talk to old version without any problems? Yes: aklaver@arkansas:~$ psql -d postgres -U aklaver -p 5434 Password for user aklaver: psql (9.6.2, server 9.4.11) Type "help" for help. postgres=> select 'test'; ?column? -- test (1 row) > 4- I am always open to other suggestions. > > Thanks. > > Regards, > Ertan Küçükoğlu > > > > -- Adrian Klaver adrian.kla...@aklaver.com Hi, Sorry about not indenting. Have to use Outlook as e-mail app. I cannot show any work at the moment, I am just researching right now, before I start actual job. I have a single form making connection to local PostgreSQL and remote one. Local seems to be OK in all respects. Remote is a problem. Below you can see some psql output. postgres@raspberrypi:~$ psql -d test -U postgres -p 5432 -h 192.168.1.105 Password for user postgres: DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: psql (9.4.10, server 9.6.1) WARNING: psql major version 9.4, server major version 9.6. Some psql features might not work. Type "help" for help. test=# select count(*) from sale; DEBUG: StartTransactionCommand DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: DEBUG: CommitTransactionCommand DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: count 176588 (1 row) test=# Btw, I am surprised that psql can make a connection *and* runs a query just fine. My main problem with my application is to run a query. Connection seems to be OK. Trying to run a query and my application simply freeze. PgAdmin3 gives lots of error messages some objects missing, assertion failures, etc. After all these messages PgAdmin3 seems to establish a connection to PostgreSQL 9.6 database on Windows OS. However, PgAdmin3 cannot run a
[GENERAL] Latest PostgreSQL on Raspbian Jessie
Hello, I could not decide which forum is more appropriate. I end up posting my question here. For a small but important Project, I need to develop a GUI application on Raspberry Pi with PostgreSQL using Lazarus. PostgreSQL 9.4.10 server will be running on Raspberry Pi 3 Model B and another PostgreSQL 9.6.2 will be running on a Windows OS. Lazarus installed on Raspberry Pi is version 1.7 (built from latest subversion trunk sources) using with FreePascal 3.1.1 (also built from latest subversion trunk sources). Raspberry Pi application will save some data in PostgreSQL running on Pi and very same application also needs to save some *other* data on PostgreSQL 9.6.2 running on Windows OS. Both OSes will be running in same LAN. Saving everything on Raspberry Pi and later copying using another application/method is not allowed by design. I could not find 9.6.x version of the PostgreSQL to install on Raspberry Pi in packages. Most recent I can install using APT package system is 9.4.10. I do not mind that it is rather old. My problem starts when I try to reach a PostgreSQL 9.6.2 using 9.4.10 pglib library (I think). - Connection may hang and application stops responding. - If connection succeeds a simple select * from query hangs and application stops responding and after a while terminates for good. I installed PgAdmin3 on Raspberry Pi to make some tests. I get lots of warnings and some error messages when I try to connect that database on Windows OS. I am new to PostgreSQL and am not sure if there may be some connection problems from old version libraries to new version PostgreSQL servers. My limited little experience says I need same version on both systems for best stability. I would like to know; 1- Is it possible to install 9.6.2 with same version pglib on Raspberry Pi? If possible to do on Raspberry Pi, I think I can build my own version from sources assuming I am provided a good detailed how to document for doing so. 2- If above is not possible, is it possible to build just the latest version pglib and overcome my connection problem? 3- If I need to select number 2 solution above, is that latest version library will talk to old version without any problems? 4- I am always open to other suggestions. Thanks. Regards, Ertan Küçükoğlu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Listing missing records
Hi Charles,
Your example worked just fine.
Thank you.
-Original Message-
From: Charles Clavadetscher [mailto:clavadetsc...@swisspug.org]
Sent: Sunday, February 19, 2017 2:04 PM
To: 'Ertan Küçükoğlu' <ertan.kucuko...@1nar.com.tr>;
pgsql-general@postgresql.org
Subject: RE: [GENERAL] Listing missing records
As suggested
> -Original Message-
> From: pgsql-general-ow...@postgresql.org
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Ertan
> Küçükoglu
> Sent: Sonntag, 19. Februar 2017 11:27
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Listing missing records
>
> Hello,
>
> Using PostgreSQL 9.6.1 on a Windows 10 64bit, EnterpriseDB binaries.
>
> I have following table:
> CREATE TABLE report
> (
> id uuid NOT NULL,
> no smallint NOT NULL,
> serial character varying(15) NOT NULL,
> branchcode character varying(10) NOT NULL,
> date timestamp without time zone NOT NULL,
> recordtime timestamp without time zone DEFAULT
date_trunc('second'::text, now()),
> CONSTRAINT reportdata_pkey PRIMARY KEY (no, branchcode, serial,
> date)
> )
>
> Normally, I should have one record for each "serial, branchcode" set
> every day. Unfortunately, for some reason beyond us, we are not
> getting these records inserted. I am asked to report missing records in
the table so that we can provide a list to people who are responsible to
enter data.
>
> Some details about data:
> - serial and branchcode values represents different devices. They are
always same within themselves.
> - there may be more than one record in a day for a given serial,
> branchcode
>
> What I am looking for is a list of serial, branchcode , date columns just
for the missing days.
>
> Some data from table is as follows:
> '76522985-14d9-40cb-a34f-8d57793ba83c',146,'JI20049362','50009','2017-
> 02-04
> 23:21:00','2017-02-13 13:13:58'
> '76522985-14d9-40cb-a34f-8d57793ba83c',145,'JI20049362','50009','2017-
> 02-04
> 22:50:00','2017-02-13 13:13:58'
> '76522985-14d9-40cb-a34f-8d57793ba83c',231,'JH20064415','50010','2017-
> 02-04
> 23:59:00','2017-02-13 13:13:58'
> '76522985-14d9-40cb-a34f-8d57793ba83c',265,'JI2001','50010','2017-
> 02-04
> 23:58:00','2017-02-13 13:13:58'
> '76522985-14d9-40cb-a34f-8d57793ba83c',232,'JH20065321','50010','2017-
> 02-04
> 23:50:00','2017-02-13 13:13:58'
> '9a07616d-ace5-462e-af59-35f3e0ba23a9',233,'JH20064415','50010','2017-
> 02-06
> 23:59:00','2017-02-13 13:13:58'
> '9a07616d-ace5-462e-af59-35f3e0ba23a9',267,'JI2001','50010','2017-
> 02-06
> 23:58:00','2017-02-13 13:13:58'
> '9a07616d-ace5-462e-af59-35f3e0ba23a9',234,'JH20065321','50010','2017-
> 02-06
> 23:50:00','2017-02-13 13:13:58'
> '909e90f1-177f-4a8a-9108-a1b7697e660f',148,'JI20049362','50009','2017-
> 02-06
> 23:58:00','2017-02-13 13:13:58'
>
> When looked in detail you can see that there is no record for date
> '2017-02-05' above. As a query result I am looking for something like
below:
> JH20065321, 50010, 2017-02-05, Sunday
> JI2001, 50010, 2017-02-05, Sunday
> JH20064415, 50010, 2017-02-05, Sunday
> JI20049362, 50009, 2017-02-05, Sunday
> JI20049362, 50009, 2017-02-05, Sunday
CREATE TABLE report
(
id uuid NOT NULL,
no smallint NOT NULL,
serial character varying(15) NOT NULL,
branchcode character varying(10) NOT NULL,
date timestamp without time zone NOT NULL,
recordtime timestamp without time zone DEFAULT
date_trunc('second'::text, now()),
CONSTRAINT reportdata_pkey PRIMARY KEY (no, branchcode, serial, date) );
INSERT INTO report VALUES
('76522985-14d9-40cb-a34f-8d57793ba83c',146,'JI20049362','50009','2017-02-04
23:21:00','2017-02-13 13:13:58'),
('76522985-14d9-40cb-a34f-8d57793ba83c',145,'JI20049362','50009','2017-02-04
22:50:00','2017-02-13 13:13:58'),
('76522985-14d9-40cb-a34f-8d57793ba83c',231,'JH20064415','50010','2017-02-04
23:59:00','2017-02-13 13:13:58'),
('76522985-14d9-40cb-a34f-8d57793ba83c',265,'JI2001','50010','2017-02-04
23:58:00','2017-02-13 13:13:58'),
('76522985-14d9-40cb-a34f-8d57793ba83c',232,'JH20065321','50010','2017-02-04
23:50:00','2017-02-13 13:13:58'),
('9a07616d-ace5-462e-af59-35f3e0ba23a9',233,'JH20064415','50010','2017-02-06
23:59:00','2017-02-13 13:13:58'),
('9a07616d-ace5-462e-af59-35f3e0ba23a9',267,'JI2001','50010','2017-02-06
23:58:00','2017-02-13 13:13:58'),
('9a07616d-ace5-462e-af59-35f3e0ba23a9',234,'JH20065321','50010','2017-02-06
23:50:00','2017-02-13 13:13:58'),
('909e90f1-177f-4a8a-9108-a1b7697e660f',148,'JI20049362','50009','2017-02-06
23:58:00','2017-02-13 13:13:58');
SELECT * FROM (SELECT serial,branchcode,
generate_series(min(date),max(date),'1
day'::INTERVAL)::DATE AS date
FROM report
GROUP BY serial,branchcode)
[GENERAL] Listing missing records
Hello,
Using PostgreSQL 9.6.1 on a Windows 10 64bit, EnterpriseDB binaries.
I have following table:
CREATE TABLE report
(
id uuid NOT NULL,
no smallint NOT NULL,
serial character varying(15) NOT NULL,
branchcode character varying(10) NOT NULL,
date timestamp without time zone NOT NULL,
recordtime timestamp without time zone DEFAULT
date_trunc('second'::text, now()),
CONSTRAINT reportdata_pkey PRIMARY KEY (no, branchcode, serial, date)
)
Normally, I should have one record for each "serial, branchcode" set every
day. Unfortunately, for some reason beyond us, we are not getting these
records inserted. I am asked to report missing records in the table so that
we can provide a list to people who are responsible to enter data.
Some details about data:
- serial and branchcode values represents different devices. They are always
same within themselves.
- there may be more than one record in a day for a given serial, branchcode
What I am looking for is a list of serial, branchcode , date columns just
for the missing days.
Some data from table is as follows:
'76522985-14d9-40cb-a34f-8d57793ba83c',146,'JI20049362','50009','2017-02-04
23:21:00','2017-02-13 13:13:58'
'76522985-14d9-40cb-a34f-8d57793ba83c',145,'JI20049362','50009','2017-02-04
22:50:00','2017-02-13 13:13:58'
'76522985-14d9-40cb-a34f-8d57793ba83c',231,'JH20064415','50010','2017-02-04
23:59:00','2017-02-13 13:13:58'
'76522985-14d9-40cb-a34f-8d57793ba83c',265,'JI2001','50010','2017-02-04
23:58:00','2017-02-13 13:13:58'
'76522985-14d9-40cb-a34f-8d57793ba83c',232,'JH20065321','50010','2017-02-04
23:50:00','2017-02-13 13:13:58'
'9a07616d-ace5-462e-af59-35f3e0ba23a9',233,'JH20064415','50010','2017-02-06
23:59:00','2017-02-13 13:13:58'
'9a07616d-ace5-462e-af59-35f3e0ba23a9',267,'JI2001','50010','2017-02-06
23:58:00','2017-02-13 13:13:58'
'9a07616d-ace5-462e-af59-35f3e0ba23a9',234,'JH20065321','50010','2017-02-06
23:50:00','2017-02-13 13:13:58'
'909e90f1-177f-4a8a-9108-a1b7697e660f',148,'JI20049362','50009','2017-02-06
23:58:00','2017-02-13 13:13:58'
When looked in detail you can see that there is no record for date
'2017-02-05' above. As a query result I am looking for something like below:
JH20065321, 50010, 2017-02-05, Sunday
JI2001, 50010, 2017-02-05, Sunday
JH20064415, 50010, 2017-02-05, Sunday
JI20049362, 50009, 2017-02-05, Sunday
JI20049362, 50009, 2017-02-05, Sunday
Thanks & regards,
Ertan Küçükoğlu
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
