Re: How to update a table with the result of deleting rows in another table
On Tue, Oct 06 2020, Hemil Ruparel wrote: > 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 = Not sure about better way but will this also not work? I just removed the second clause. #+BEGIN_SRC sql with data as ( delete from orders where customer_id = and date = '2020-10-05' returning price ) update paymentdetail set temp_credit = temp_credit + (select sum(price) from data) where customer_id = #+END_SRC
Re: Storing a time interval
Steve Baldwin writes: > I agree with Michael. Another consideration is how the composite type is > going to be handled in the DB layer of your processing code (e.g. node, > python, ...). In the scenario you described it seems unlikely you will be > either having multiple columns of that type on your PO table, or using that > composite type on a different table, so apart from the 'interest' factor, > I'm not seeing any practical benefit. Composite types are also slightly > painful in the change they bring to the way you reference them. For example > typically you need to surround the outer column in brackets - e.g. > (dates).discount_last_date. If you are using an ORM library, does it know > how to deal with that? > I faced a similar issue when using Enums with with rust lang. The Diesel ORM didn't support it directly. Had to struggle with custom code in the Model layer. -- Pankaj Jangid
Using PostgreSQL for Machine Learning Data Pipelines
Hi, I am working on a machine-learning project. Because of the available study material in the ML area, the team is inclined towards Apache Kafka, Apache Spark for data-pipelines and analytics. Our requirement is to store huge amounts of continuously increasing data that cannot fit into a single machine. The algorithms require data in batches so it is not necessary to keep full data ready for consumption. Using Kafka, the data can be distributed and fetched in varying batch sizes as and when required. I am more comfortable with PostgreSQL. And wanted to know more about case-studies where PostgreSQL is deployed for ML use. Any pointers referring to study material will be helpful. Please share in this thread. -- Thanks & Regards, Pankaj Jangid
Re: PG 12 not yet for mac
Tomas Vondra writes: > On Mon, Oct 07, 2019 at 11:11:43AM -0400, Ravi Krishna wrote: >>https://postgresapp.com/downloads.html >> >>The link which says PG 12 is actually PG 11. >> > > Not sure if the link is correct or not (it seems to point to > Postgres-2.3-12.dmg, which seems like it might be v12). But more > imporantly, that's not a project/page managed by the PostgreSQL > community, you need to report the issues to the authors (most likely > through github issues). > I am waiting for the Homebrew cask to update. Eagerly waiting to test the new features. -- Regards, Pankaj
Re: PG11 Parallel Thanks!!
Jason Ralph writes: > I wanted to drop a quick note thanking the developers who have > contributed to Postgres. I have recently upgraded our production PG > instances from pg9.3 to pg11. > > We do a lot of table syncs, and we have one process at the end of the > month that syncs 3 very large tables (400GB). This sync happens from > a shell script using pg_dump and pg_restore, we have it set to use -j3 > but it's called sequentially so it never really takes advantage of > parallel. > > Since pg11 on both the target and source, the run time has decreased a > lot, I chalk it up to the parallel index creations in pg11 which was a > very time consuming process on pg9.3. > The process has finished almost 10 hours earlier than pg93. So thank > you for your hard work and dedication to this awesome piece of > software. This is a great user story. Thanks for sharing your experience, Jason. -- Pankaj Jangid
A post describing PostgreSQL 12 Generated Columns
Found a very nice article about PostgreSQL 12 Generated Columns. I thought this might be useful for everyone. Hence sharing. https://pgdash.io/blog/postgres-12-generated-columns.html -- Pankaj Jangid
Re: managing primary key conflicts while restoring data to table with existing data
Krishnakant Mane writes: >> You might think about adding the new UUID column and use the existing >> primary key to inform the updates in dependent tables. Then remove the >> old PK column and constraint followed by promoting the UUID to >> primary key. This could be safely scripted and applied to all >> instances of your data. >> That said, this is only truly necessary of you have production >> databases to worry about. > > > Thanks a million, this is the most logical and safe way. > > yes I have a lot of production databases to worry about. > > I am only confused about what you mean by "use the existing primary key > to inform the updates in dependent tables." > > Are you refering to a cascading effect? > > If yes then does it mean I first program my upgrade script to manually > go through all new uuid keys and update the same in the depending tables > with reference to the old primary key working as foreign key in those > tables? I guess that is safest option given that the databases are in production. 1. add UUID UNIQUI column 2. add references to it by identifying ising pkey 3. remove pkey references and the columns 4. make UUID column the pkey 5. remove old pkey column. -- Pankaj Jangid
Re: How to represent a bi-directional list in db?
Francisco Olarte writes: > That being said, linked lists are procedural data structures, SQL is > declarative, so they are not a great match, that's one of the reasons > why they are rarely seen. Things like master-detail have less > impedance mismatch. Thanks Francisco. Got the idea. -- Pankaj Jangid
Re: How to represent a bi-directional list in db?
Francisco Olarte writes: >> Could you please elaborate? Suppose I have this table, >> CREATE TABLE stages ( >> id SERIAL PRIMARY KEY, >> name VARCHAR(80) NOT NULL, >> next_id INTEGER REFERENCE stages NULL, >> ); >> What would be the backward query in that case? Forward is clear. This is >> forward query, >> SELECT name FROM stages WHERE next_id = 123; > > No. That is a BACKWARDS QUERY. You are in row 123, you go BACK to its > preceedeing one. > If you need a traversable list containing (ListID, id,name) = x,1,A; > x,2,b; x,3;c ( I've added the ListId column to make it more > interesting/reallistic, you normally do not have a single table) > In sql you can build a (ListId, id, prev_id, name ) table ( PREV is > easier, as when you insert a row, in a normal application, you know > the previous one, but not the next one ) with the data > (x,1,null,a),(x,2,1,b),(x,3,2,c) ( the last one is a synthetic > sentinel and assumes nullable id), you can do it in a lot of ways. > > To traverse it forward you just querying "select id where listid=x and > next_id is null" to locate the head (1), and then just go forward by > selecting with prev_id = last got id until you hit zero results. > > To traverse backwards there are several ways. In the real cases I've > used I always had a "list row" where I could store the node for the > 1st stage. In that cases i linked them circularly, (id=1, prev=3), so > bidirectional traversing was easy. Or you can use a special sentinel > node ( with a marker, like name=null). The thing is you locate the > last row, and then just query with id=last got prev_id. I do not > remember the details, but probably your "stages" are stages of > something which has a row, which can readily have a "first_stage_id" > or something similar. > > Lists in tables are not the same as in C, where you directly store > pointers which point outwards. In this case any unique data serves as > a pointer, slow ( table scan ) by default, faster if you index the > column. > > Anyway, unless you need the "linked list" functionality for something > ( really heavy manipulation of large stage lists, splicing things > around ), I've normally found it's easier, in sql, to model this kind > of thing with a master-detail + order column. > ( whatever = (id, , first_stage_id), stages=(id, order, ) ) > Thanks a lot Francisco. This is great help. My stages are stages of processes. So yes processes are also stored in a table. I got the idea. I'll add another column in the processes table which points to the first stage (first_stage_id). And quries Forward pass: 1. select name from stages where id = 2. select name from stages where prev_id = 3. repeat (2) Backward pass: 1. select name from stages where prev_id = 2. select name from stages where id = 3. repeat (2) This is assuming I also create a circular list. I can also store last_stage_id in the process table if we don't want to create circular list in db. Regards. -- Pankaj Jangid
Re: How to represent a bi-directional list in db?
Francisco Olarte writes: > On Sun, Sep 22, 2019 at 4:25 PM Pankaj Jangid wrote: >> CREATE TABLE stages ( >>id SERIAL PRIMARY KEY, >>name VARCHAR(80) NOT NULL, >>created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, >>updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, >>prev_stage_id SERIAL REFERENCES stages NULL, >>next_stage_id SERIAL REFERENCES stages NULL, >>process_id SERIAL REFERENCES processes NOT NULL >> ); >> Failed with: conflicting NULL/NOT NULL declarations for column >> "prev_stage_id" of table "stages" >> Is it not possible to create "nullable" self referencing foreign keys? > > Serial seems wrong. It means integer, not null, defaul next value from > a sequence. > > What you probably want is just "prev_stage_id INTEGER" ( NULL by > default ), as you do not want the prev/next stage ids to be generated, > you normally would want to assign values from other tuples. > Thanks. This resolved my problem of NULL/NOT NULL conflict. I wasn't aware that SERIAL is by default NOT NULL. > Also, you may have problems populating this kind of table, as you will > not have the ids from either prev or next stage when building it. > If NULL value is allowed I can fill it up with NULL initially. Right? Or is there something wrong here. > And lastly, in SQL you do not really need a doubly linked list, just > populate prev_stage_id, and index it and you can query next stage of a > tuple using it. > Could you please elaborate? Suppose I have this table, CREATE TABLE stages ( id SERIAL PRIMARY KEY, name VARCHAR(80) NOT NULL, next_id INTEGER REFERENCE stages NULL, ); What would be the backward query in that case? Forward is clear. This is forward query, SELECT name FROM stages WHERE next_id = 123; -- Pankaj Jangid
How to represent a bi-directional list in db?
I am creating an application for a manufacturing scenario. To represent stages in an assembly line, I wanted to create following table, CREATE TABLE stages ( id SERIAL PRIMARY KEY, name VARCHAR(80) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, prev_stage_id SERIAL REFERENCES stages NULL, next_stage_id SERIAL REFERENCES stages NULL, process_id SERIAL REFERENCES processes NOT NULL ); But it: Failed with: conflicting NULL/NOT NULL declarations for column "prev_stage_id" of table "stages" Is it not possible to create "nullable" self referencing foreign keys? -- Pankaj Jangid
