Index/trigger implementation for accessing latest records

2018-05-02 Thread Alastair McKinley
Hi, I have a table that stores a location identifier per person which will be appended to many times. However, for many queries in this system we only need to know the most recent location per person, which is limited to about 1000 records. Is the following trigger/index strategy a reasonabl

Re: Index/trigger implementation for accessing latest records

2018-05-02 Thread Gavin Flower
Hi Alastair, See embedded comments. On 02/05/18 21:51, Alastair McKinley wrote: Hi, I have a table that stores a location identifier per person which will be appended to many times. However, for many queries in this system we only need to know the most recent location per person, which i

Re: Index/trigger implementation for accessing latest records

2018-05-02 Thread Alvaro Herrera
Alastair McKinley wrote: > create function latest_record_update() returns trigger as > $$ > BEGIN > update location_records set latest_record = false where person_id = > new.person_id and latest_record is true and id != new.id; > return new; > END; > $$ language plpgsql; > > crea

CSVQL? CSV SQL? tab-separated table I/O? RENAME COLUMN

2018-05-02 Thread Jim Michaels
what do you think about foreign data wrappers getting CSV file table I/O? - I had thought that CSVQL db could be implemented completely with small amount of memory and file I/O, line at a time. EOL detection would be needed. can be: CR, LF, CR+LF. sometimes beginners get it backwards (LF+CR), but i

Re: CSVQL? CSV SQL? tab-separated table I/O? RENAME COLUMN

2018-05-02 Thread David G. Johnston
On the whole this email is very confusing/hard-to-follow... On Wed, May 2, 2018 at 2:29 PM, Jim Michaels wrote: > what do you think about foreign data wrappers getting CSV file table I/O? > ​ > ​I don't understand the question...​ ​ > I had thought that CSVQL db could be implemented complet

Re: CSVQL? CSV SQL? tab-separated table I/O? RENAME COLUMN

2018-05-02 Thread Ron
On 05/02/2018 04:49 PM, David G. Johnston wrote: [snip] - the microsoft patented CSV would be required for implementation. it handles special data with commas and double-quotes in them ​If true this seems like a show-stopper to anything PostgreSQL would implement If MSFT really holds

Re: CSVQL? CSV SQL? tab-separated table I/O? RENAME COLUMN

2018-05-02 Thread John McKown
On Wed, May 2, 2018 at 4:29 PM, Jim Michaels wrote: > what do you think about foreign data wrappers getting CSV file table I/O? > - I had thought that CSVQL db could be implemented completely with > ​ > ​I don't know what you want to do with this. SQLite already supports it. SQLite is an embedde

Re: Index/trigger implementation for accessing latest records

2018-05-02 Thread Tim Cross
Alastair McKinley writes: > Hi, > > > I have a table that stores a location identifier per person which will be > appended to many times. > > However, for many queries in this system we only need to know the most recent > location per person, which is limited to about 1000 records. > > > Is th

Re: CSVQL? CSV SQL? tab-separated table I/O? RENAME COLUMN

2018-05-02 Thread Adrian Klaver
On 05/02/2018 02:29 PM, Jim Michaels wrote: - the microsoft patented CSV would be required for implementation. it handles special data with commas and double-quotes in them Huh?: https://en.wikipedia.org/wiki/Comma-separated_values#History -- Adrian Klaver adrian.kla...@aklaver.com

Re: CSVQL? CSV SQL? tab-separated table I/O? RENAME COLUMN

2018-05-02 Thread raf
Ron wrote: > On 05/02/2018 04:49 PM, David G. Johnston wrote: > [snip] > > > > - the microsoft patented CSV would be required for implementation. it > > handles special data with commas and double-quotes in them > > > > > > If true this seems like a show-stopper to anything PostgreSQL w