Tag Info

Hot answers tagged

15

No. No gain at all. The manual explicitly states: Tip: There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database ...


14

Without concurrency Use a subquery in the FROM clause of the UPDATE: UPDATE server_info s SET status = 'active' FROM ( SELECT server_ip -- your pk column or any (set of) unique column(s) FROM server_info WHERE status = 'standby' LIMIT 1 -- arbitrary pick (cheapest) ) sub WHERE s.server_ip = sub.server_ip ...


10

Don't know if this is the best way. I first did a select to find out if a stat is double digit and assign it a 1 if it is. Summed all those up to find out total number of double digits per game. From there just sum up all the doubles and triples. Seems to work select a.player_id, a.team, sum(case when a.doubles = 2 then 1 else 0 end) as doubleDoubles, ...


9

You can use the EXCEPT operator. For example, if the tables have identical structure, the following will return all rows that are in one table but not the other (so 0 rows if the tables have identical data): (TABLE a EXCEPT TABLE b) UNION ALL (TABLE b EXCEPT TABLE a) ; Or with EXISTS to return just a boolean value or a string with one of the 2 possible ...


9

I think I probably meant to add that comment on the prior answer, about two separate statements. It was over a year ago, so I'm not totally sure anymore. The wCTE based query doesn't really solve the problem it's supposed to, but upon reviewing it again over a year later I don't see the possibility of lost updates in the wCTE version. (Note that all of ...


9

"Can support" != "optimal throughput". You can use lots of connections, but it's slower. If you use fewer connections and queue work, you get the same amount of work done in a smaller time. Even more extreme is the discrepancy for another DBaaS provider, who proposes a 2 core server with 500 concurrent connections. How could this possibly work well? ...


9

TL;DR: New users can create tables in the public schema because people complained that it was too hard when they couldn't. If you dislike the defaults, you should probably create a new template database with the initial configuration that you want. For example, you might: DROP SCHEMA public; or REVOKE ALL ON SCHEMA public FROM public; GRANT USAGE ON ...


9

A LANGUAGE sql function must be a complete SQL statement (or more than one). Yours is just an expression. Just prepend SELECT, like you would when running it stand-alone. $BODY$ SELECT (SELECT ... The reason it complains at the + is that, surprisingly, it's legal to parenthesise a top-level query. This, for example, is a valid query: regress=> ...


9

Assumptions / Clarifications No need to differentiate between infinity and open upper bound (upper(range) IS NULL). (You can have it either way, but it's simpler this way.) NULL vs. infinity in PostgreSQL range types Since date is a discrete type, all ranges have default [) bounds. Per documentation: The built-in range types int4range, int8range, ...


9

This was posted to pgsql-hackers mailing list and I tried to answer in brief there. It seems if the target list (specified columns) matches the tuple descriptor of the relation exactly, that is, both in number of columns and order, then the underlying scan can return a tuple that's directly consumable by the enclosing Sort node. On the other hand, if the ...


8

This is a common problem with floating point numbers everywhere. Floating point numbers stored in computer systems should only ever be considered approximations because there are numbers easy to represent in decimal that come out longer than the available precision (sometimes they are in fact never ending) when converted to binary. See ypercube's links and ...


8

I would suggest separate Player, Game and Participant tables: Player PlayerID Name Etc. Game GameID -- a unique number which distinguishes any one game from all others. Game_Data -- the logged data of how this one game played out. Participant PlayerID GameID Since each player in a game will see the same game_data it ...


8

Basically, NULL is causing this, because NULL<>NULL. One of the columns in your self-joined table will be all NULLs. Here's a little test case that shows why this can happen. Naughty NULL equality and the way NATURAL JOIN works, picking column names to join on for you. Setup: create table one ( a integer, b integer ); CREATE TABLE two ( A INTEGER , ...


8

A very simple query would be to use UNION DISTINCT. I think it would be most efficient if there is a separate index on each of the four columns: SELECT a AS abcd FROM tablename UNION -- means UNION DISTINCT SELECT b FROM tablename UNION SELECT c FROM tablename UNION SELECT d FROM tablename ; Another would be to first UNION ...


7

I suggest an SQL function: CREATE OR REPLACE FUNCTION foo(_date date) RETURNS TABLE ( name text -- types have to match your actual types! , keyword_id int , project_id int , the_date date , today int , yesterday int , week int , month int) AS $func$ SELECT k.name, f.keyword_id, f.project_id, _date -- AS the_date -- col ...


7

Like @ypercube commented, check the CREATE TABLE page of the manual: There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE (which is the default). MATCH FULL will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null; if they are all null, the row is not required to have a match in ...


7

It is not safe to assume that the data is OK just because PostgreSQL starts. Pg does not do any kind of comprehensive verification run on the DB contents at startup. If it did it could take hours (or days or weeks for bigger DBs) to start. It doesn't have any verification tools. The argument is that these should not be needed if the data is managed ...


7

Try this out (worked for me on MySQL 5.5): SELECT player_id, team, SUM( ( (points >= 10) + (rebounds >= 10) + (assists >= 10) + (steals >= 10) + (blocks >= 10) ) = 2 ) double_doubles, SUM( ( (points >= 10) + (rebounds >= 10) + (assists >= 10) + (steals ...


7

One option is to use a FULL OUTER JOIN between the two tables in the following form: SELECT count (1) FROM table_a a FULL OUTER JOIN table_b b USING (<list of columns to compare>) WHERE a.id IS NULL OR b.id IS NULL ; For example: CREATE TABLE a (id int, val text); INSERT INTO a VALUES (1, 'foo'), (2, 'bar'); CREATE ...


7

The size of the physical table is typically (except for opportunistic pruning of removable pages from the end of the table) not reduced by running VACUUM (or VACUUM ANALYZE). You need to run VACUUM FULL to actually shrink the table. That's not necessarily what you want to do on a regular basis if you have write load on your table. Dead rows provide wiggle ...


7

This has a simple reason. In PostgreSQL a row has to go through a visibility check. On the first read, the system checks if a row can be seen by everybody. If it is, it will be "frozen". This is where the writes come from. Similarly, VACUUM also sets bits. There is a detailed explanation: http://www.cybertec.at/speeding-up-things-with-hint-bits/.


7

I think this is because you only ever return the first row from the query's result. The select ... into ... will only retrieve one row and the query select * from result returns only that single record: You also don't need a PL/pgSQL function, a plain SQL function will work just fine: CREATE OR REPLACE FUNCTION getMessageFromSites(ids TEXT) RETURNS ...


7

none of the columns are mentioned Yes they are, right here: where pd.active and pd.fk_status = 1 that matches the condition on the index and thus the index can be used to support the counting of the rows. Reading through all the rows in the index should be faster than doing a seq scan on the process_data table. Why it's not using the other index I ...


7

Add or edit the following line in your postgresql.conf : listen_addresses = '*' Add the following line as the first line of pg_hba.conf. It allows access to all databases for all users with an encrypted password: # TYPE DATABASE USER CIDR-ADDRESS METHOD host all all 0.0.0.0/0 md5


7

Let's first distinguish between keys and indexes, key is part of the logical model and is often implemented with an unique index. You can however create a unique index without creating a key, but that can not be referenced by a foreign key. A candidate key is something that uniquely identifies a row in a table, in SQL one of the candidate keys are normally ...


7

There might be hardware issues, too - how should we know? But there are certainly issues with the query. First of all, remove DISTINCT from your VIEW definition. It's doing nothing at all (but complicating and slowing things down). Related answer on SO with explanation: PostgreSQL - Slow query joining on a VIEW Arriving at this (cleaned up) query: ...


6

select c.id, (select row_to_json(_) from (select c.first_name, c.last_name) as _) as first_last, c.age from customers as c will do what you want without any performance impact (and is not too verbose): id | first_last | age ------+---------------------------------------------+--------- 1 | ...


6

select id, name, v[1] as major_version, v[2] as minor_version, v[3] as patch_level from ( select id, name, string_to_array(version, '.') as v from versions ) t order by v[1]::int desc, v[2]::int desc, v[3]::int desc; SQLFiddle: http://sqlfiddle.com/#!15/c9acb/1 If you expect more elements in the ...


6

To gain more info on database and table list, You can do : \l+ to list databases List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description ...


6

Running an EXPLAIN ANALYSE VERBOSE massively underestimates the time taken to return the results There's a misunderstanding here, because EXPLAIN ANALYZE does not estimate, it runs the query for real and reports the actual time taken by each steps, as opposed to EXPLAIN without ANALYZE that just reports the estimates without running the query. ...



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