Tag Info

Hot answers tagged

9

This query: SELECT DISTINCT ON(recipient) * FROM messages LEFT JOIN identities ON messages.recipient = identities.name WHERE messages.timestamp BETWEEN timeA AND timeB ORDER BY recipient, timestamp DESC; says: For all messages between timeA and timeB, find the recipients and for every recipient, find one message (the latest in between timeA and ...


8

Aaron, In my recent work, I've been looking into some similar questions with PostgreSQL. PostgreSQL is almost always pretty good at generating the right query plan, but it isn't always perfect. Some simple suggestions would be to make sure to run an ANALYZE on your progresses table to make sure that you have updated statistics, but this isn't guaranteed ...


8

PostgreSQL certainly can use an index for IS NOT NULL. I don't see any query planner assumptions about that condition, either. If the null fraction for the column (pg_statistic.stanullfrac) is low enough to suggest that the index is usefully selective for the query, PostgreSQL will use an index. I can't figure out what you're trying to say with: If ...


8

Would this be a good idea? No. would numeric(6,0) take less bytes? No. test=> SELECT pg_column_size(INT4 '999999'), pg_column_size(NUMERIC(6,0) '999999'); pg_column_size | pg_column_size ----------------+---------------- 4 | 10 (1 row) how about the performance (this table is being queried a lot)? Slower. ...


7

TL;DR: Normalize, normalize, normalize. Joins good, unions bad. Narrow tables with lots of rows good, wide tables with few rows bad. Fewer more general tables good, lots of very specific tables bad. Denormalized structure You're trying to model relations as objects. This can work, but it gets clumsy fast. It looks like a pretty direct mapping of your C++ ...


7

First off, gaps in a sequence are to be expected. Ask yourself if you really need to remove them. Your life gets simpler if you just live with it. To get gap-less numbers, the (often better) alternative is to use a VIEW with row_number(). Example in this related answer: Gap-less sequence where multiple transactions with multiple tables are involved Here ...


6

You don't need triggers or PL/pgSQL at all. You don't even need DEFERRABLE constraints. And you don't need to store any information redundantly. Include the ID of the active email in the users table, resulting in mutual references. One might think we need a DEFERRABLE constraint to solve the "chicken/egg" problem of inserting a user and his active email, ...


5

Normally we'd expect that when postgres was restarted, the crash recovery process would have removed files related to a rollback'ed index from the data directory. Let's assume that it didn't work, or at least that it has to be checked manually. The list of files that should be in the datadir can be established with a query like this: select ...


5

Right from the manual: The key word PUBLIC indicates that the privileges are to be granted to all roles, including those that might be created later. PUBLIC can be thought of as an implicitly defined group that always includes all roles. Any particular role will have the sum of privileges granted directly to it, privileges granted to any role it is ...


5

I have created the following sample DDL structure in order to work the necessary queries: CREATE TABLE my_data ( my_data_id SERIAL, theme_id INT NOT NULL, priority_id INT NOT NULL, my_value INT NOT NULL, CONSTRAINT pk_my_data PRIMARY KEY (my_data_id) ); CREATE TABLE theme ( theme_id SERIAL, name CHAR(6) NOT ...


5

Short version: no. There's no practical way (in PostgreSQL, at least) to index a pattern column so it can be matched against plaintext inputs in a way that will speed up "does this plaintext match any of these patterns" queries. PostgreSQL would need a special custom index type that "understood" pattern matches. I'm not sure how practical it'd be to ...


5

The documentation on psql explains: Whenever the pattern parameter is omitted completely, the \d commands display all objects that are visible in the current schema search path — this is equivalent to using * as the pattern. (An object is said to be visible if its containing schema is in the search path and no object of the same kind and name ...


5

PostgreSQL relies on the operating system's disk cache for most caching. This cache is usually reported as "free" RAM by most tools, because modern operating systems use all but a little bit of the currently-free RAM for disk cache. This is normal. To confirm, use a better tool that shows buffers/cache separately from truly free memory. On Linux, free -m ...


5

Since 9.3, you can do this using \gset in psqlrc: select split_part(version(),' ',2) pmt \gset \set PROMPT1 '%:pmt:' Or, as 'the value of the selected prompt variable is printed literally, except where a percent sign (%) is encountered': select split_part(version(),' ',2) "PROMPT1" \gset If your version is <9.3, it's still possible, but much uglier, ...


5

If you can add a column to the table, the following scheme would almost1 work: CREATE TABLE emails ( UserID integer NOT NULL, EmailAddress varchar(254) NOT NULL, IsActive boolean NOT NULL, -- New column ActiveAddress varchar(254) NOT NULL, -- Obvious PK CONSTRAINT PK_emails_UserID_EmailAddress PRIMARY KEY (UserID, ...


4

You can create a function like this; CREATE OR REPLACE FUNCTION public.get_messages_by_timestamp ( time_a timestamp, time_b timestamp ) RETURNS TABLE ( recipient varchar, "timestamp" timestamp ) AS $$ BEGIN RETURN QUERY SELECT DISTINCT ON (m.recipient) m.recipient, m."timestamp" FROM messages m LEFT JOIN ...


4

Unfortunately, a ROW() expression does not preserve column names. Use a subselect instead. And json_agg() is simpler and faster for the task: SELECT u.id, u.name , json_agg((SELECT x FROM (SELECT s.name, s.created) x)) AS sites FROM remodel.users u JOIN remodel.user_sites us ON us.user = u.id JOIN remodel.sites s ON s.site = us.id ...


4

So, is there a way to preserve current_user, without giving the dbuser group role direct access to the relations in schema private? You may be able to use a rule, rather than an INSTEAD OF trigger, to provide write access through the view. Views always act with the security rights of the view creator rather than the querying user, but I don't think ...


4

"date" Don't call your timestamp column "date", that's very misleading. Better yet, don't use the basic type name "date" as identifier at all, that's error-prone, leads to confusing error messages and it's a reserved word in standard SQL. Should be something like: CREATE TABLE test ( id serial PRIMARY KEY , ts timestamp NOT NULL -- also adding NOT NULL ...


4

Your existing index on DATE is obviously useless for the query. The first obvious step for your query: SELECT * FROM tbl WHERE column_a = 'value1' AND column_b = 'value2'; is an index for column_a or column_b (which ever is more selective) or possibly a multicolumn index on (column_a, column_b), like: CREATE INDEX tbl_a_b_idx ON tbl(column_a, column_b); ...


4

SQLState: 55P03 In Postgres 9.4.x I tested this by performing an unresolved SELECT FOR UPDATE in pgAdmin and then doing a SELECT FOR UPDATE NOWAIT in my Java app. I used the JDBC driver JDBC41 Postgresql Driver, Version 9.4-1201. The result was the following PostgreSQL Error Code. Message:ERROR: could not obtain lock on row in relation "my_table_" ...


4

Masi, The PostgreSQL B-Tree index is very strongly based on the implementation by Lehman and Yao, which includes a lot of work oriented around multi-version concurrency control, but there's still great info in this paper. Of course, PostgreSQL doesn't make a 100% accurate replica of the method in the paper, and to find the differences, there will be almost ...


4

psycopg2 is a wrapper around libpq, written in C, to expose a Python DB-API compatible API to Python programs. It implements Python objects in C that call libpq functions. It has a thin Python module wrapper around it to load it and provide some of the interface functionality that's easier to write in pure Python. Anything that implements the DB-API to ...


4

The only way to do either of these without schema changes is with a PL/PgSQL trigger. For the "exactly one" case, you can make the references mutual, with one being DEFERRABLE INITIALLY DEFERRED. So A.b_id (FK) references B.b_id (PK) and B.a_id (FK) references A.a_id (PK). Many ORMs etc cannot cope with deferrable constraints though. So in this case you'd ...


4

To give you a flavour of JOINs and SQL, I created two tables - Customer and Cust_Order as shown. I then loaded these tables with data (see end of post for DML). These examples use both PostgreSQL and MySQL. A note on table names. I use singular names - you can, of course, use plural (as many do) - but decide and stick to one! A word of advice (and see ...


3

You could probably achieve #1 with "\i" directives to import snippets. I wouldn't advise it, though, I'd use a Makefile to concatenate the desired chunks. It's as simple as something like: myext--1.0.sql: myext-types.sql myext-tables.sql myext-views.sql cat $< $@ Note that the indent must be a tab, not spaces. There's lots of good info out there ...


3

If you are looking for significant performance improvements to dnoeth's answer, consider using a native C-function and creating the appropriate operator. Here is an example for int4 arrays. (A generic array variant and the corresponding SQL script). Datum _int_sequence_contained(PG_FUNCTION_ARGS) { return DirectFunctionCall2(_int_contains_sequence, ...


3

In this case, it's doing an Index Scan. As best as I know, the difference between an Index scan and a Bitmap Index/Heap Scan is that the former will read pages in the order defined by the index, while the latter will create a bitmap of pages to read (possibly from multiple indexes), order the results, and read them in [heap] order. Correct. There are ...


3

A simple OR condition will do this: select * from dtsc_search_data where c1 = 2 or (c1 <> 2 and c2 = 2) or (c1 <> 2 and c2 <> 2 and c3 = 2); SQLFiddle example: http://sqlfiddle.com/#!15/d1ca1/1


3

I know the reason now. I need to vacuum the tables before using index-only-scan, otherwise, if a sufficiently high number of heap pages where modified since the last vacuum, the planner will not choose to use index-only scan. When only a small amount of pages have been changed, an index-only scan may happen, which then involves heap fetches. If I force it ...



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