Hot answers tagged

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

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

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 ...


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

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. ...


2

Use t1."MaxID" literally the same as in with or drop quotes in with.


2

Your parameter _source in the added MWE is not referenced anywhere. The identifier source in the function body has no leading underscore and is interpreted as constant table name independently. More importantly, it would not work like this anyway. SQL only allows to parameterize values in DML statements. Details in this related answer: Error when setting ...


2

For Postgres 9.4+: UPDATE trajs t SET traj_id = upd.traj_id FROM ( SELECT id, 1 + count(*) FILTER (WHERE t >= t0 + interval '6 min') OVER (PARTITION BY obj_id ORDER BY t) AS traj_id FROM ( SELECT id, obj_id, t, lag(t) OVER (PARTITION BY obj_id ORDER BY t) AS t0 FROM trajs ) sub ) upd WHERE t.id ...


2

Your subquery is uncorrelated and returns all rows from the join between native_name_aliases and dpoint, where only a single row would be allowed. I guess this is what you are after: UPDATE dpoint d SET native_name = nna.native_name FROM native_name_aliases nna WHERE d.building_id = 42 AND d.dpoint_id = nna.dpoint_id AND d.native_name IS ...


1

The psql command \? shows the option to turn off expanded formatting: Formatting: ... \x [on|off|auto] toggle expanded output (currently off) Typically it will show table data in one record per line if its "off" or else each field in its own line when switched on, e.g.: postgres=# \x off Expanded display is off. postgres=# \l ...


1

First off, your question as well as your column name "key" are misleading. The column key does not contain any JSON keys, only values. Else we could use the function jsonb_object_keys(jsonb) to extract keys, but that's not so. Assuming all your JSON arrays are either empty or hold integer numbers as demonstrated. And the scalar values (non-arrays) are also ...


1

Sounds like you're trying to insert rows to a table that already exists, in which case you'll need something like... INSERT INTO new_records SELECT * FROM new_table t JOIN new_record_ids r ON(r.id = t.id) --this assumes the result of the select has the same column layout as new_records... --which seems unlikely. replace (SELECT *...) with (SELECT column1, ...



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