[GENERAL] How to intelligently work with views that depend on other views
I have a bunch of views, and some views use data from other views. For example, view A might get used by view B and view B gets used by view C. Several times now, as I got further into the project, I've changed how I make some views and I've had to redefine not just that view, but all the ones that depend on it. This is getting frustrating! When I want to change how I make view A, I have to drop view A cascade, and then view B and view C are dropped. Then I have to remember to re-create B and C after I rewrite A. There's likely a better solution... What is it? GO POSTGRESQL! -- W. 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 features such as string literals for table/columns names) so in my mind it can only be included when psycopg will be able to do both client-side parameter interpolation and server-side arguments passing, and when the distinction between the two strategies will be clear (this is planned for a future psycopg3 but there is no timeline for it yet). First of all, thanks for showing this trick! But I am confused. Will this trick stop working in a future version of psycopg2? Should I avoid using it? Thanks again! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to insert either a value or the column default?
I have a table that looks sort of like this: create table tasks ( task_id serial primary key, title text, status text not null default 'planned' ); In python, I have a function like this: def insert_task(title, status=None): and when status is passed in, I want to run a SQL insert statement like this: insert into tasks (title, status) values (%s, %s) but when status is not passed in, I want to run this SQL insert instead: insert into tasks (title, status) values (%s, default) I know how to pick the query with an if-clause in python, but I wish it were possible to do something like this: insert into tasks (title, status) values (%s, coalesce(%s, default)) I have tried different variations, but I keep getting syntax errors. Is there any way to do say: if the value is not null, insert the value. Otherwise, insert the default value for this column entirely in SQL? When there is just one optional column, it is not a big deal to use an if-clause in python. But there are numerous optional columns. I know I could build up lists of strings in python but I'm hoping there's a simpler way to do this in the query. But I have a hard time already getting other programmers to understand SQL injection attacks and if they see me building up SQL queries from strings, even though there's no risk of a SQL injection in this scenario, I still don't want to break my no string 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 mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to join table to itself N times?
I got this table right now:
select * from market_segment_dimension_values ;
+--+---+
| market_segment_dimension | value |
+--+---+
| geography| north |
| geography| south |
| industry type| retail|
| industry type| manufacturing |
| industry type| wholesale |
+--+---+
(5 rows)
The PK is (market_segment_dimension, value).
The dimension column refers to another table called
market_segment_dimensions.
So, north and south are to values for the geography dimension.
In that data above, there are two dimensions. But sometimes there could be
just one dimension, or maybe three, ... up to ten.
Now here's the part where I'm stumped.
I need to create a cartesian product of the dimensions.
I came up with this approach by hard-coding the different dimensions:
with geog as (
select value
from market_segment_dimension_values
where market_segment_dimension = 'geography'),
industry_type as (
select value
from market_segment_dimension_values
where market_segment_dimension = 'industry type')
select geog.value as g,
industry_type.value as ind_type
from geog
cross join industry_type
;
+---+---+
| g | ind_type|
+---+---+
| north | retail|
| north | manufacturing |
| north | wholesale |
| south | retail|
| south | manufacturing |
| south | wholesale |
+---+---+
(6 rows)
But that won't work if I add a new dimension (unless I update the query).
For example, maybe I need to add a new dimension called, say, customer
size, which has values big and small. A
I've got some nasty plan B solutions, but I want to know if there's some
solution.
There's a really elegant solution in python using itertools.product, like
this:
list(itertools.product(*[['north', 'south'], ['retail',
'manufacturing', 'wholesale']]))
[('north', 'retail'),
('north', 'manufacturing'),
('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 mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to do a full-text search words within some proximity of each other?
I noticed in elastic search (ES), you can do queries like a b~4 I think this query will match stuff like a b and a x x b but not something like a x x x x x x x x b. I'm not sure if this kind of thing is possible with postgresql full text search. Is it possible? I understand that I can do 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to tame a gigantic (100+ lines) query in a web app?
I'm sure I'm not the first person to end up with a gigantic query that does lots of left joins and subselects. It seems to work, but I would love to break it up into smaller chunks. I'm thinking about rewriting the query to make several temporary tables that are dropped on commit, and then joining them at the end. I can't just use views for everything because I use parameters passed in from the web app. I am using a few views where I can. Is there anything dangerous about making temporary tables in this way? I started two transactions simultaneously and they were both able to make 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.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
