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


11

Looking at it as a grammar problem, ANY is defined as (in Row and Array Comparisons): expression operator ANY (array expression) But is distinct from is not an operator, it's a "construct" as we're told in Comparison Operators: When this behavior is not suitable, use the IS [ NOT ] DISTINCT FROM constructs Since PostgreSQL has user-defined ...


11

There are basically three ways of upgrading PostgreSQL from different major versions (e.g. 9.1 to 9.3). Upgrading with pg_dump The first one, and recommended if possible, is to do a dump of the old (9.1) version using the binary of the newer (9.3) version and restore it on a new cluster created of the newer version. This approach is, generally, the slower ...


11

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

(Regarding PostgreSQL 9.3 and MySQL 5.6, written in 2014; if you're looking at other versions, this may be outdated): Lots more features. CHECK constraints True SERIALIZABLE isolation Arrays (including index support for arrays) Window functions (lead, lag, row_number, etc) Common table expressions (WITH queries) including recursive CTEs and writeable CTEs ...


9

Query cleanup First, lets rewrite your query to be readable, by using table aliases, qualifying field names, and using an ANSI join: select t.userID, t.date, t.time, t.servID, t.timestamp, l.servID_HEX, l.SERV_LOCY, l.SERV_LOCX from test t inner join locations l on (t.servID=l.servID_HEX) where t.userID='<someusers>' and extract(dow ...


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


8

I've worked round the issue like this, but I'm hoping there is a less kludgy way: explain analyze with recursive w(n) as ( select 1 union all select n+1 from w where n<5 ) select * from w limit (select count(*) from w); /* QUERY PLAN ...


8

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


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


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


7

Perhaps like this: select foo , exists (values (null), ('A') except select foo) chk_any , not exists (values (null), ('A') intersect select foo) chk_all from ( values ('A'),('Z'),(null) ) z(foo); foo | chk_any | chk_all -----+---------+--------- A | t | f Z | t | t | t | f Note that not only the null in the "array" ...


7

Operator This is building on @Daniel's clever operator. While being at it, create the function/operator combo using polymorphic types. Then it works for any type - just like the construct. And make the function IMMUTABLE. CREATE FUNCTION is_distinct_from(anyelement, anyelement) RETURNS bool LANGUAGE sql IMMUTABLE AS 'SELECT $1 IS DISTINCT FROM $2'; ...


7

Nothing is wrong with your table definition. (Except hat I would use jos_content_id or something instead of the non-descriptive column name id. And I probably would use text instead of varchar(50). Your INSERT statement is the problem. With your id column defined as serial, you shouldn't insert manual values for id. Those may collide with the next value ...


7

Actually, since NEW is a well defined composite type, you can just access any column with plain and simple attribute notation. SQL itself does not allow dynamic identifiers (table or column names etc.). But you can use dynamic SQL with EXECUTE in a PL/pgSQL function. Demo CREATE OR REPLACE FUNCTION trg_demo() RETURNS TRIGGER AS $func$ DECLARE test ...


7

This answer applies to modifying the PostgreSQL source code by applying a "diff" or "patch". It's not about installing minor version updates; to do that, just download and run the installer. To alter the PostgreSQL server its self or its procedural language runtimes, you will generally need to recompile PostgreSQL from scratch. On Windows that's a bit of ...


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

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

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

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

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


6

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


6

Support for array foreign keys was worked on with the goal of getting it into PostgreSQL 9.3, but it didn't make the cut for the release due to performance and reliability problems. It doesn't seem to be being worked on for 9.4. At this time, you need to stick to the usual relational approach of using a "join table" to model an m:n relationship. CREATE ...


6

First, use ST_SetSRID(ST_MakePoint(c.longitude, c.latitude),4326)::geography instead of ST_GeographyFromText('SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')') Per documentation: ST_MakePoint while not being OGC compliant is generally faster and more precise than ST_GeomFromText and ST_PointFromText. It is also easier to use if you ...


6

Update: I've posted about this to the AWS forums - please go chime in and ask for it there. At time of writing, Amazon RDS does not support physical replication outside RDS. You can GRANT users the REPLICATION right using an rds_superuser login, but you can't configure replication entries for outside IPs in pg_hba.conf. Furthermore, when you create a DB ...


6

During its operation, PostgreSQL records changes to transaction log files, but it doesn't immediately flush them to the actual database tables. It usually just keeps the changes in memory, and returns them from memory when they are requested, unless RAM starts getting full and it has to write them out. This means that if it crashes, the on disk tables won't ...



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