Tag Info

Hot answers tagged

16

Update: Tested all 5 queries in SQLfiddle with 100K rows (and 2 separate cases, one with few (25) distinct values and another with lots (around 25K values). 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 It would be efficient with a separate index on each of the ...


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

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


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

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

To be clear, I'd use union as ypercube suggests, but it is also possible with arrays: select distinct unnest( array_agg(distinct a)|| array_agg(distinct b)|| array_agg(distinct c)|| array_agg(distinct d) ) from t order by 1; SQLFiddle here


9

You could use LATERAL, like in this query: SELECT DISTINCT x.n FROM atable CROSS JOIN LATERAL ( VALUES (a), (b), (c), (d) ) AS x (n) ; The LATERAL keyword allows the right side of the join to reference objects from the left side. In this case, the right side is a VALUES constructor that builds a single-column subset out of the column values ...


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


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

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


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

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


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


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

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

There are a couple of misunderstandings here: The null bitmap is not part of the heap tuple header. Per documentation: There is a fixed-size header (occupying 23 bytes on most machines), followed by an optional null bitmap ... Your 32 nullable columns are unsuspicious for two reasons: The null bitmap is added per row, and only if there is at least ...


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

If you just want to select from some values, rather than just creating a table and inserting into it, you can do something like: WITH temp (k,v) AS (VALUES (0,-9999), (1, 100)) SELECT * FROM temp; To actually create a temporary table in a similar fashion, use: WITH vals (k,v) AS (VALUES (0,-9999), (1, 100)) SELECT * INTO temporary table temp FROM ...


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



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