[GENERAL] How to intelligently work with views that depend on other views

. Matthew Wilson m...@tplus1.com http://tplus1.com -- 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] How to insert either a value or the column default?

On Sun, Aug 24, 2014 at 2:50 PM, Daniele Varrazzo daniele.varra...@gmail.com wrote: It should be added to the library (it was first discussed in 2003...), but it's one of these things that will stop working when psycopg will start using the extended query protocol (together with other nifty

[GENERAL] How to insert either a value or the column default?

interpolation rule of thumb unless I absolutely have to. And I know I could switch to some gigantic library like SQLAlchemy, but I really don't want to. Any advice is welcome. Thanks in advance! Matt -- W. Matthew Wilson m...@tplus1.com http://tplus1.com -- Sent via pgsql-general

[GENERAL] How to join table to itself N times?

'), ('north', 'wholesale'), ('south', 'retail'), ('south', 'manufacturing'), ('south', 'wholesale')] All advice is welcome. Thanks in advance! Matt -- W. Matthew Wilson m...@tplus1.com http://tplus1.com

[GENERAL] Rank based on the number of matching OR fields?

I want to run a query like to_tsquery(A | B | C) and then rank the results so that if a document contained A, B, and C, then it would rank above a document that just had some subset. How would I do such a thing? -- W. Matthew Wilson m...@tplus1.com http://tplus1.com -- Sent via pgsql-general

[GENERAL] How to do a full-text search words within some proximity of each other?

a query and rank the results by how closely the words are to each other, but I want to exclude any matches where the words are not within two words of each other. Thanks in advance! Matt -- W. Matthew Wilson m...@tplus1.com http://tplus1.com -- Sent via pgsql-general mailing list (pgsql

[GENERAL] How to tame a gigantic (100+ lines) query in a web app?

their own temporary tables. More generally, how to tame this big ol' query? The temporary tables mean I'm only pulling data from the database one time. ORMs often pull data from one query and then use that data to write the next query. This seems slow to me. Matt -- W. Matthew Wilson m...@tplus1

[GENERAL] Need help writing exclusion constraint

I have a table like this: create table event( destination_id integer not null references destination (destination_id), starts timestamp, ends timestamp ); I want to make sure that no two rows **with the same destination_id** overlap in time. I'm not sure how to write this

[GENERAL] How to refer to computed columns from other computed columns?

I'm converting some procedural code to SQL as an experiment. Here's the pseudocode: c = a - b if c 0 then d = 'no' else d = 'yes' In SQL, I've got this: select a, b, a - b as c, case when a - b 0 then 'no' else 'yes' end as d from foo; This is a trivial

Re: [GENERAL] How to refer to computed columns from other computed columns?

On Mon Aug 16 10:26:36 2010, Tom Lane wrote: Matthew Wilson m...@tplus1.com writes: All I can come up with so far is to use a view and then another view on top of that one: Note that you don't actually need a view, as you can just write the subselect in-line: select a, b, c

[GENERAL] How to improve performance in reporting database?

I have a daily job that pushes data from the production database into the reporting database, which right now, is an exact copy. I have a webapp that builds lots of reports for users. Most of these reports involve elaborate joins of lookup tables and lots of summations, and they take too long to

[GENERAL] Want to schedule tasks for the future

Just recently I discovered the listen/notify feature in postgresql. Now I don't have external processes polling tables, watching for new inserted rows. Anyhow, I'm curious if there is some other feature that will help me out with a new puzzle. I want to store emails to deliver at a later time in

[GENERAL] How do I index to speed up OVERLAPS?

I have a table shift with a start_time column and a stop_time column and I do a lot of queries like: select * from shift where (start_time, stop_time) overlaps ($A, $B); $A and $B are user-submitted values. Anyhow, how do I put indexes on my table to make these queries run faster?

[GENERAL] More schema design advice requested

I track employee qualifications in one table and I track job requirements in another table. A job requires zero-to-many qualifications, and for an employee to be qualified for that job, the employee must have ALL the requirements. For example, In my job requirements table, I record that a nurse

Re: [GENERAL] Need schema design advice

Jeff, this is *exactly* the kind of feedback I was hoping to get. Thanks so much for the link and the explanation. Matt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Need schema design advice

I need to track employees and their preferred locations, shifts, and stations. For example, I need to track that Alice prefers to work the morning shift at the west-side location, and she likes to work the cash-register station. Also, I need to track that Bob likes the west-side and north-side

[GENERAL] NULL values seem to short-circuit my unique index

I'm trying to comprehend how NULL values interact with unique indexes. It seems like I can insert two rows with NULL values in a column with a unique constraint just fine. Is there something special about NULL? Can anyone post some links to explain what is going on? Here's the example that

[GENERAL] Foreign Key normalization question

I'm building an app that has a customers table, a locations table, a products table, and a product_locations table. They make a diamond shape. The locations table and the products table each have a customer_id column that links back to the customers table. Then the product_locations table table

Re: [GENERAL] Foreign Key normalization question

On Tue 02 Sep 2008 04:06:20 PM EDT, Martin Gainty wrote: you can use setup a foreign key constraint in your create table so that col= umn is only populated when there is a value which syncs to the referenced value http://www.postgresql.org/docs/7.4/interactive/sql-createtable.html I don't

Re: [GENERAL] Foreign Key normalization question

On Tue 02 Sep 2008 04:19:41 PM EDT, Scott Marlowe wrote: If the two subordinate tables ALWAYS have to point to the same place, why two tables? Can't a customer have 1 location? I'm pretty sure IBM has more than one corporate office you could ship things to. Yeah, so the idea is one customer

Re: [GENERAL] Foreign Key normalization question

On Tue 02 Sep 2008 04:40:55 PM EDT, Scott Marlowe wrote: On Tue, Sep 2, 2008 at 2:35 PM, Matthew Wilson [EMAIL PROTECTED] wrote: On Tue 02 Sep 2008 04:19:41 PM EDT, Scott Marlowe wrote: If the two subordinate tables ALWAYS have to point to the same place, why two tables? Can't a customer have

Re: [GENERAL] Foreign Key normalization question

On Tue 02 Sep 2008 05:35:25 PM EDT, D. Dante Lorenso wrote: You could add a trigger to your product_location table that just double-checked the customers matched or prevents the insert/update. A PL/PGSQL function like this might help: -- 8 8 --

[GENERAL] Need help with simple update / insert trigger

I have table sort of like this: create table snoz ( a bool default false, b bool default false, c bool default false ); I want to set up a trigger so that any time a row is inserted or updated, the value of c is set to true if a and/or b is true. How can I do this? Thanks! Matt

[GENERAL] Need help with constraint to prevent overlaps

I'm building a shift-scheduling app. I want to make a constraint in my database that prevents one human from being assigned to work two different jobs at the same time. In other words, if I schedule John Doe to mop bathrooms from 10 AM until 4 PM, some other manager will not be able to schedule

[GENERAL] How do I make sure that an employee and supervisor belong to the same company?

I have an employees table and one column in the employees table is supervisor_id which is an FK to the id column. I have employees from numerous companies all in the same table. I have a column called company_id that indicates the company. I want to make sure that an employee chooses a

Re: [GENERAL] Need help requiring uniqueness in text columns

On Wed 02 Jan 2008 04:23:46 AM EST, Pavel Stehule wrote: Hello IDEA 3: Use two hash functions: CREATE UNIQUE INDEX uidx ON TEST((decode(md5(a),'hex')),(hashtext(a))); removing spaces helps CREATE UNIQUE INDEX uidx ON test((decode(md5(lower(replace(a,' ',''))),'hex'))); Regards Pavel

[GENERAL] Need help requiring uniqueness in text columns

I have a table MESSAGE with a text column and a timestamp column. I want to make sure that I have no rows with duplicates for both values. I have two ideas right now for how to do this. IDEA 1: CREATE UNIQUE INDEX nodups on MESSAGE (my_timestamp_col, my_text_col); IDEA 2: CREATE UNIQUE INDEX

[GENERAL] I want to search my project source code

I have a lot of code -- millions of lines at this point, written over the last 5 years. Everything is in a bunch of nested folders. At least once a week, I want to find some code that uses a few modules, so I have to launch a find + grep at the top of the tree and then wait for it to finish. I

[GENERAL] Need advice on keeping backup database up to date

I have been using postgresql for my web application for a while now and it has been great. I want to set up a separate emergency failover server and database in a different data center. In the event that my primary data center becomes inaccessible, I want to update a DNS record and then