Hot answers tagged

8

What happens when a new programmer arrives and has to write an app against that db? They don't know that field x has to be NOT NULL. Another programme might assume that all field x's are NOT NULL for performing counts say, but some now are NULL because of the new programme, leading to inconsistent and difficult to trace errors. IMHO it is always best to ...


6

It is not really clear from the question what is the role of the call_type_id column. I will ignore it until you clarify. Without window functions Here is a simple variant that doesn't use window functions at all. Make sure that there is an index on (call_dt_key, aes_raw). CTE_Dates returns a list of all dates in the table and calculates average for each ...


6

As already cited by others in comments, adding NOT NULL to your table specification can improve in a significant way the performances of your queries (in addition to the very good methodological reasons stated in another answer). The reason is that the query optimizer, knowing that a column cannot have a NULL value, can exclude special tests for such values,...


4

Yes, adding indexes could cause IO wait to increase. Perhaps without the index, you are doing a lot of full scans of the table, thousands or millions of blocks, to get just one piece of data. But the IO wait is very low, because the kernel read ahead keeps the pump primed so your process doesn't wait on IO (instead it uses a lot of User CPU to filter ...


4

The GROUP BY id is useless in the query, since there is only one row for every id (it appears to be the primary key of the table). What you need a count over the whole result. This is easy using a window aggregate: SELECT count(*) OVER () AS total_count, id, parent_id FROM paths WHERE parent_id = 5 ; The last requirement is rather weird but ...


4

This is exactly what the LAG() window function does, available since version 8.4: SELECT id, name, date, LAG(name) OVER (PARTITION BY id ORDER BY date) AS previous_name, LAG(date) OVER (PARTITION BY id ORDER BY date) AS previous_date FROM table_name ; There is also the option of using a LATERAL join (available from 9.3). Might be better ...


4

1) The purpose is to provide a logical representation of a data entity (coordinates for example or a path) that can be indexed and queried in Postgresql. And also postgresql can understand this datatype. 2) Technically it's possible but you're better of storing it as two float so you can filter on them. It's never recommended to try to outsmart database ...


4

One way of doing it would be to use VALUES to create a table expression with the ids to check and EXCEPT to find the missing ones. SELECT id FROM (VALUES(4),(5),(6)) V(id) EXCEPT SELECT id FROM images;


4

Two major improvements: SELECT * FROM certificates c WHERE c.expires_on <= current_date + 30 -- sargable! AND NOT EXISTS ( SELECT 1 FROM certificates WHERE common_name = c.common_name AND expires_on > c.expires_on AND state = 'issued' ); Make the first predicate sargable, so that an index can be used. You second ...


3

The big bounty makes the currently accepted answer seem exemplary, but I am not entirely happy with several details. Hence, I added this answer. Table definition You should have provided an actual table definition to make this easier. Judging from the sample data, call_dt_tm is type timestamp with time zone (timestamptz). The column call_dt_key is not ...


3

Some reflections, a separation of tree[1] structure and content is most likely the way to go. I.e. remove name from catalogcategories2. Now nodes that don't have a parent does not have to exist in this table. I.e. you can make pid not null. The current foreign key is invalid since the primary key which it references consists of two columns. A sketch: ...


3

Use an exclusion constraint and be aware how bounds for the type daterange work. date is a discrete type, so ranges have default [) bounds. Per documentation: The built-in range types int4range, int8range, and daterange all use a canonical form that includes the lower bound and excludes the upper bound; that is, [). So, if you want to book a room ...


3

As far as I can tell, you only need a single derived table together with a conditional aggregation: SELECT hs.customer_id, hs.customer_email AS account, coalesce(max(x.monthlyAvgValue),0) AS monthlyAvg, coalesce(max(x.prevMonthlyAvg),0) AS prevMonthlyAvg, coalesce(max(x.weeklyAvgValue),0) AS weeklyAvg FROM history_sum AS hs LEFT JOIN ( ...


3

Basically you are looking for the expression: client_status IS DISTINCT FROM 'n' The column client_status should really be data type boolean, not text, which would allow the simpler expression: client_status IS NOT FALSE The manual has details in the chapter Comparison Operators. Assuming your actual table has a UNIQUE or PK constraint, we arrive at:...


3

To have the data held in two different physical sequences one most store the data twice. This can be achieved by defining a second, covering index. A covering index contains all of the columns required by a query. This way the optimiser has no need to refer to the base table to read further values and is unlikely to revert to a base table scan for the query ...


3

First, to answer the question you asked about the behavior of this command: psql -h localhost -p 5432 postgres testdb Also, why does the prompt say password authentication failed for user "testdb". From what I have understood, testdb is the name of the database and not the name of the user. The user name is postgres. Because you have given two ...


3

Explanation I'm going to step through the multiple layers of misunderstandings one by one - arriving at a simple, secure solution. 0. The reason why overlay is escaped is not because it's a function name, but because it's a reserved word. Also, overlay() is not a "PL/pgSQL function" (nor PL/pgSQL keyword), it's a standard SQL function built into the ...


3

If you create an index on (row_time DESC, row_id DESC), then the second case will will act just as the first one. LIMIT will always operate after sorting is taken care of, so when sorting without an index is necessary, it will sort the entire recordset prior to processing it through LIMIT. I wonder what is it you want to achieve? Applying LIMIT over a ...


3

You can do that with a column check: ALTER TABLE table ADD CHECK ((column->'id') is not null and (column->'name') is not null);


3

I highly recommend re-writing your query using proper join syntax: select pep.sequence, string_agg(DISTINCT g.symbol, ',') FROM tmp_psm_seqs2 pep JOIN protein_seq p ON p.sequence LIKE '%' || pep.sequence || '%' JOIN transcript_translation tt ON tt.protein_seq_id = p.protein_seq_id JOIN transcript t ON tt.transcript_id = t.transcript_id JOIN gene g ON t....


3

The OR REPLACE clause in CREATE FUNCTION is not meant for seamless parallel execution, it's meant to avoid dropping the function when we just want to update the body. From the doc: If you drop and then recreate a function, the new function is not the same entity as the old; you will have to drop existing rules, views, triggers, etc. that refer to the ...


3

I'd like to know what I'm not understanding correctly about excluding constraints. What is happening here is that primary key (col1) creates a primary key constraint but doesn't create a separate not null constraint. It adds a not null "modifier" to the column. This works as a constraint, ie. no nulls are allowed in col1 but it is not a named constraint. ...


3

You could merge the subqueries using this model: SELECT bool_or(B.tags&1<>0) as "has_children_tag_1", bool_or(B.tags&2<>0) as "has_children_tag_2", bool_or(B.tags&4<>0) as "has_children_tag_3", bool_or(B.tags&8<>0) as "has_children_tag_4" FROM A LEFT JOIN B ON A.id = B.parent_id WHERE [conditions] ...


3

No, those are not "duplicated", those are additional relations/objects created for the tables. reversion_revision_pkey is most probably the unique index supporting the primary key for the table reversion_revision. And the others are most probably indexes as well. You can add this expression to your query to see the actual type of the relation: case ...


2

The design should be according to your requirements. If your requirement includes to store only some columnar data that are mostly required (mostly not null) then use tables with relations (foreign keys). If you just need some logging with situational tree structure data then use JSONB. For example, suppose that you store io_counters when counters are ...


2

You have to do with users the same thing you're doing with days: WITH days AS ( SELECT generate_series(current_date-7, current_date, '1d')::date AS day ), eves AS ( SELECT user_id, created_at::date AS full_day, COUNT(*) as evs FROM logged_events WHERE logged_events.created_at >= current_date-6 GROUP BY user_id, full_day ), ...


2

The syntax you use is not valid for a unique index because a unique index does not create a constraint. You need to remove the ON CONSTRAINT and use the index expression instead. This works: INSERT INTO journals (ext_ids, title) VALUES ('{"nlmid": "000"}', 'blah') ON CONFLICT ((ext_ids ->> 'nlmid'::text)) DO NOTHING;


2

I may have misunderstood you but I imagine something like: select * from test x where exists ( select 1 from test y where x.company_id = y.company_id and coalesce(client_status, 'y') <> 'n' ); will work. coalesce is use to map null to 'y', but anything different than 'n' should do Using an OLAP function can save us a ...


2

You could create a view that looks like something like this: create view test_view as select a,b,c,d, case when a = greatest(a,b,c,d) then 'a' when b = greatest(a,b,c,d) then 'b' when c = greatest(a,b,c,d) then 'c' when d = greatest(a,b,c,d) then 'd' end from test; Note that the above will not ...



Only top voted, non community-wiki answers of a minimum length are eligible