Tag Info

Hot answers tagged

10

In addition to what @Craig provided (and correcting some of it): Effective Postgres 9.4, UNIQUE, PRIMARY KEY and EXCLUDE constraints are checked immediately after each row when defined NOT DEFERRABLE. This is different from other kinds of NOT DEFERRABLE constraints (currently only REFERENCES (foreign key)) which are checked after each statement. We worked ...


9

As I understand it, your issue here is that the constraint is checked after each statement, but you want it checked at the end of the transaction, so it compares the before-state to the after-state, ignoring the intermediate states. If so, that is possible with a deferrable constraint. See SET CONSTRAINTS and DEFERRABLE constraints as documented in CREATE ...


6

This unicity constraint can be enforced with this unique index: CREATE UNIQUE INDEX idxname ON mytable(least(col1,col2),greatest(col1,col2)); Demo: test=> insert into mytable (col1,col2) values(1,2); INSERT 0 1 test=> insert into mytable (col1,col2) values(2,1); ERROR: duplicate key value violates unique constraint "idxname" DETAIL: Key ((LEAST(col1, ...


5

Possible reasons: Numerous and probably overlapping indexes on the table; have a look with \d Bloat due to high update churn can sometimes affect indexes more than tables, depending on update patterns. Examine the size of each individual index to see if it makes sense. GiST indexes, if used, can be quite large Unlike what I originally thought this is not ...


5

Just call pg_database_size(dbname) to know the size of the database. VACUUM (without the FULL clause) does not free any space, it only marks it as reusable, and thus will not change the database's size (except in a rare boundary case, see Routine Vacuuming). ANALYZE does statistical sampling and would be useful if you needed the row counts, but for the ...


5

Functions that give the size of columns, tables, and indexes are documented in the manual: http://www.postgresql.org/docs/9.4/static/functions-admin.html There is no function to calculate the size of an entire record (while there is a function to know the storage cost of an individual data value (pg_column_size)), since records are in general of variable ...


5

This is a known issue regarding Postgres optimization. If the distinct values are few - like in your case - and you are in 8.4+ version, a very fast workaround using a recursive query is described here: Loose Indexscan. Your query could be rewritten (the LATERAL needs 9.3+ version): WITH RECURSIVE pa AS ( ( SELECT labelDate FROM pages ORDER BY labelDate ...


5

Use the ONLY key word: TRUNCATE ONLY public.history_uint; Per documentation: If ONLY is specified before the table name, only that table is truncated. If ONLY is not specified, the table and all its descendant tables (if any) are truncated. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are ...


4

The common solution is to express this as: interval '1 day' * ? with ? as a placeholder for a numeric value (possibly with a fractional part). The syntax tried in the question is rejected because for the SQL grammar, the entire expression interval '1 day' is a constant. It cannot be changed by injecting a placeholder into it, just like we couldn't write ...


4

GRANT doesn't take wildcards in table identifiers. You can use ON ALL TABLES IN SCHEMA, but that requires a single schema name. If you want to do things with wildcard pattern table names you will need to use PL/PgSQL's EXECUTE format(...) in a DO block to loop over the information_schema.tables view. See many related answers here on DBA.se and Stack ...


4

You must be running in a Security Group firewall problem. Go to your RDS Dashboard, select Instances and open the instance you want to connect to. Look for a line like this : Security Groups rds-launch-wizard (sg-3e9axxx) ( active ). You should be able to click on rds-launch-wizard (sg-3e9axxx) which leads you to the EC2 Dashboard in the Security Groups ...


4

current_date is a keyword. There are no parentheses after it. fcm.effect_end_date >= current_date


4

There is no such thing as a stored procedure in PostgreSQL. All PostgreSQL has are SQL-callable functions defined in a number of languages - LANGUAGE sql, LANGUAGE plpgsql, LANGUAGE plperl, etc. Additionally, there's no RENAME TABLE command. You want ALTER TABLE ... RENAME TO .... This particular example must be written in PL/PgSQL using EXECUTE because it ...


4

You could use a subquery but you don't need to. Just don't sum the bonus and add it in the GROUP BY list. Notice that you have to also add the student.id, even in your original query, in case you have 2 students with same name. You probably also need coalesce() for students without any scores: SELECT st.name, coalesce(sum(sc.score1),0) + ...


4

this has nothing to do with databases. its how you os resolves the domain name in this case 'localhost' you /etc/hosts file should have an entry for localhost as below if you don't have it , then adding this entry will resolve your issue 127.0.0.1 localhost localhost.localdomain


4

In the absence of any answers I've explored the issue further myself. It looks like user-defined functions can handle all base types, including bytea and smallint[], so this doesn't affect the choice of representation much. I tried out several different representations on a PostgreSQL 9.4 server running locally on a Windows 7 laptop with a vanilla ...


4

Your query, given your data, is equivalent to this one: SELECT emp.name, role.role_name, emp.emp_id FROM emp INNER JOIN role ON emp.role_id = 1; -- because this is the role_id for 'tech' This means that you don't define a condition about how to join the two tables. In turn, this results in a Cartesian product of role (without any restriction about ...


4

Use substring() with a regular expression instead: substring(ls.attribute_actions_text FROM 'name="(.*?)"/>') The dot (.) matches any character, *? is the non-greedy quantifier for a sequence of 0 or more matches and the parentheses (()) mark the substring to be returned. Like your code, this selects the first string matching the pattern and does not ...


4

You could use the function age() to simplify your expression (returns interval). But it's much more efficient to use a sargable expression to begin with. This operates with the exact time difference (current time is relevant): SELECT * FROM accounts WHERE status = 'PENDING_PAYMENT' AND status_updated_at < now() - interval '10 days' To operate ...


4

Use a FULL [OUTER] JOIN, combined with two rounds of window functions: SELECT ts , min(foo) OVER (PARTITION BY foo_grp) AS foo , min(bar) OVER (PARTITION BY bar_grp) AS bar FROM ( SELECT ts, f.foo, b.bar , count(f.foo) OVER (ORDER BY ts) AS foo_grp , count(b.bar) OVER (ORDER BY ts) AS bar_grp FROM foo f FULL JOIN bar b ...


3

You forgot to mention that you installed the additional module pg_trgm, which provides the similarity() function. Similarity operator % First of all, whatever else you do, use the similarity operator % instead of the expression (similarity(job_title, 'sales executive') > 0.6). Much cheaper. And index support is bound to operators in Postgres, not to ...


3

The canonical unit for shared_buffers is pages of 8kB, so the actual memory allocated in bytes is: 524288 * 8192 = 4294967296 or 4096*1024*1024 as requested. You can also check the size of the segment of memory with ipcs -m


3

I think you are looking for conditional aggregation: SELECT b.WorkflowID, count(case when a.creationtime <= timestamp '2015-06-10 14:00:00' then b.worflowid end) as count_before, sum( case when a.creationtime <= timestamp '2015-06-10 14:00:00' then (LENGTH(b.data)/1024.)::numeric(10,2) end) as size_before count(case when ...


3

If you dont want the update trigger to be executed when the its called from within the insert trigger, you can surround your statements with a condition of pg_trigger_depth() which returns the depth, which wont be 0 when you are running the trigger directly/indirectly from another trigger. So, within your function gener_history(), you can do something like ...


3

I tried creating a small sample of your database, we should probably force the planner to use a semi-join nested loop which would stop processing further rows as soon as it finds the first row for a given hour, camera_id as zugguy suggested. You can have a workaround with the temporary table using generate_series to get hours for the given datetimes. ...


3

You can use a CASE WHEN clause to custom the output : SELECT CASE WHEN status THEN 'true' WHEN NOT status THEN 'false' ELSE 'uknknown' END AS status FROM mytable; If you want to change the default output of PostgreSQL in order to output 'true' instead of 't', I'm pretty sure it is not possible.


3

Just an idea: create a table with all hours in the day (0-23). create table hours( hr integer ); Then find all hours that have snapshots for the given camera_id and date (of course, you will need to substitute your own camera_id and date into the query): select h.hr, 1 as camera_id from hours h where exists ( select 1 from snapshots s ...


3

So I've built a short SQL script to do this operation, based on the original sample input data: Order_year, Contact 2015, 123 2014, 123 2013, 123 2011, 123 2010, 123 2015, 234 2014, 234 2011, 234 The script is as follows: WITH RECURSIVE ranked_order AS( SELECT order_year, contact, 1 AS counter FROM orders UNION SELECT orders.order_year, ...


3

For Postgres, adding a column without a default value is essentially a no-op as only the catalog tables get rewritten. Apart from the short exclusive(!) lock there is no performance impact. Although that lock is only held for a very short time, getting the lock on a busy system might be a problem because all open transactions and queries need to be ...


3

Install the latest minor release of the same PostgreSQL major version - e.g. if you were using 9.3.1 before, install 9.3.9. Make sure it's the same architecture - i.e. if you were using a 32-bit PostgreSQL before you must install 32-bit PostgreSQL now. Stop the service using the services control panel (services.msc). Delete ...



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