Tag Info

Hot answers tagged

13

No. Please see the PostgresSQL FAQ: How does PostgreSQL use CPU resources? The PostgreSQL server is process-based (not threaded), and uses one operating system process per database session. A single database session (connection) cannot utilize more than one CPU. Of course, multiple sessions are automatically spread across all available CPUs by ...


12

You didn't specify the LOGIN right: ALTER ROLE portal WITH LOGIN; If you use CREATE USER instead of CREATE ROLE the LOGIN right is granted automatically; otherwise you must specify it in the WITH clause of the CREATE statement.


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


10

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


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


10

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


9

Proper solution The core of the problem is the data model. In a normalized schema, you wouldn't store name and email redundantly. Could look like this: CREATE TABLE name ( name_id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, verified BOOLEAN NOT NULL DEFAULT FALSE, UNIQUE (name, email) ); ...


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

It looks like this is just a typo. Your subquery is returning fk_fc_id but your join is referencing fc_fk_id. It seems like you just need to alter the query: select fulllist.fk_fc_id from ( select distinct fk_fc_id from data_item ) as fulllist left outer join ( select temp.fk_fc_id, count(*) as number from ( select * ...


8

You can use the function json_array_elements() to unnest the JSON array and string_agg() to build a text array from it. You need columns to uniquely identify each row. The primary key would be your natural choice. If you should not have one, the ctid can be your surrogate primary key for the purpose of this query. Text array with double-quoted values: ...


8

Use an array to represent the series of values: pg_prepare($con, "prep", "select * from test where tid=ANY($1::int[])"); $strpar = "{3,4,6,8,10}"; pg_execute($con, "prep", array($strpars)); The cast to int[] in the query might even be superfluous if the planner is able to infer the type by itself.


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

As said in "40.5.3. Executing a Query with a Single-row Result" (emphasis mine): The result of a SQL command yielding a single row (possibly of multiple columns) can be assigned to a record variable, row-type variable, or list of scalar variables. This is done by writing the base SQL command and adding an INTO clause. So this should work: SELECT ...


7

No automatic predicate pushdown for CTEs PostgreSQL 9.3 doesn't do predicate pushdown for CTEs. An optimizer that does predicate pushdown can move where clauses into inner queries. The goal is to filter out irrelevant data as early as possible. As long as the new query is logically equivalent, the engine still fetches all the relevant data, so produces the ...


7

Try this instead: CREATE INDEX user_reputation_idx ON users(cast("user"->>'reputation' AS int)) For some reason (unknown to me), the Postgres syntax shortcut :: for casts is not allowed in an index definition. It should work with the standard SQL form cast(expression AS type), though. This is completely independent of the json type, btw. You can ...


7

It's highly likely that the best approach will be a side-table of sometable(main_id, value) where you have a composite index on (main_id, value). This allows very fast lookups to see "for this mainid, does this value exist". This will let you enforce foreign key relationships. Unless you have a good reason, use this conventional relational approach. Failing ...


7

Usually PostgreSQL 9.3 is generally faster then 8.3 - but hard to say what is wrong. Possible sources: problems with IO, wrong PostgreSQL configuration - max_connections = 1000 is probably terribly wrong value, default work_mem is usually too small, hitting hw limits (9.1 and higher should to better use more CPU), wrong testing ... Other problems can be ...


7

There's a chicken-and-egg problem there. PostgreSQL reads postgresql.conf to decide where to log, and how. So it cannot really log errors in postgresql.conf to the PostgreSQL logs, unless it uses some kind of fallback/default log. Instead, it uses the Windows Event Log, which is what Windows applications are supposed to do anyway. (You can have PostgreSQL ...


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



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