Tag Info

Hot answers tagged

13

Two queries - two replies: a) Placing business logic to database has strong defenders and strong opponents. Lot of arguments for/against are volatile and valid only for some configurations and environment. Some databases has not good capabilities for stored procedural programming, some companies has not good personal resources for programming in relative ...


11

It depends. With SQL functions (LANGUAGE sql), the answer is generally yes. Passed parameters are treated as values and SQL-injection is not possible - as long as you don't call unsafe functions from within and pass parameters. With PL/pgSQL functions (LANGUAGE plpgsql), the answer is normally yes. However, PL/pgSQL allows for dynamic SQL where passed ...


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

It very much depends on the details of your requirements. If you have sufficient free space (at least 110% of pg_size_pretty((pg_total_relation_size(tbl))) on disk and can afford a share lock for some time and an exclusive lock for a very short time: Create the new table including the uuid column with a column default. CREATE TABLE tbl_new ( tbl_uuid ...


10

No. Please see http://wiki.postgresql.org/wiki/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 ...


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

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


8

It very much depends on the circumstances and exact requirements. Consider my comment to the question. Simple solution With DISTINCT ON in Postgres: SELECT DISTINCT ON (i.good, i.the_date) i.the_date, p.the_date AS pricing_date, i.good, p.price FROM inventory i LEFT JOIN price p ON i.good = p.good AND i.the_date >= p.the_date ORDER BY ...


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

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


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

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

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.


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

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


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


6

That's because the SQL standard doesn't require PostgreSQL to return rows in any particular order without an ORDER BY clause. It can return them however it feels like. See the manual for SELECT. In particular: If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce. and When using LIMIT, it is a good ...


6

The databases are different. In general, the answer depends very much on your specific queries. As far as a general use case, I would expect PostgreSQL to perform better for a few reasons, but there are cases where I would expect MySQL to perform better. In PostgreSQL all tables are heap tables. In MySQL all innodb tables are btree indexes with the tuple ...


6

This is a dump file produced by pg_dump -Fc. To create a text file containing SQL commands from it, use pg_restore. The basic syntax is pg_restore file.dump > file.sql, that is, you don't specify a target database (notice the missing-d option).


6

Your rule does not have a WHERE clause and any update is trying to modify the whole users table. You can/should add WHERE id = old.id: CREATE OR REPLACE RULE students_data_update AS ON UPDATE TO students_data DO INSTEAD UPDATE users SET f_name = new.f_name, l_name = new.l_name, --- ... WHERE id = old.id ; --- added Note: I ...


6

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



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