Tag Info

Hot answers tagged

5

A partial unique index should do this: create unique index max_one_null on item (type_id) where manufactured_until is null; For bonus points, is there a reasonably complex way to guard that the intervals do not overlap for one item type Look into range types and exclusion constraints. They were specifically designed for this problem. Something ...


4

You can do much with window functions here. My first draft was half intended to be base for a materialized view, half for direct use (see version history). Now updated to present two optimized queries for each case (with / without materialized view). Also improved in multiple details, much faster, yet. Test case Building on this table: CREATE TABLE ...


3

You may use to_char to get the time fields from a single function call: check (to_char(end_time at time zone 'UTC' at time zone 'US/Eastern','HH24:MI:SS') = '23:59:59') Seconds given by SS are not rounded up so that should be OK as an equivalent to floor


3

Transactions are per-session, i.e. per-connection. PostgreSQL doesn't support suspending and resuming transactions, so psycopg2 couldn't make them per-cursor unless it implicitly created new connections behind the scenes. In practice I don't find psycopg2's cursors particularly useful. They can retain result sets if you're not using incremental fetch from ...


3

SELECT hotel, totprice FROM ( SELECT r.hotel, SUM(r.pricepp)*@spacesd_needed AS totprice FROM availability AS a JOIN availability AS r ON r.date BETWEEN a.date AND a.date + (@days_needed-1) AND a.hotel = r.hotel AND r.spaces >= @spaces_needed WHERE a.date BETWEEN '2014-01-01' ...


3

For now While stuck with your unfortunate solution: CHECK ((end_time AT TIME ZONE 'UTC' AT TIME ZONE 'US/Eastern')::time = '23:59:59'::time) That's right, AT TIME ZONE two times: The first instance transforms your timestamp without time zone into timestamp with time zone. that's assuming you are actually storing UTC times. The second instance converts ...


2

You must group by all of the columns i the select clause: GROUP BY ticket.queue_id, queue.name, article.create_time or add aggregate functions such as MAX: SELECT ticket.queue_id, MAX(queue.name), MAX(article.create_time), COUNT(article.id) FROM article JOIN ticket ON article.ticket_id=ticket.id JOIN queue ON ticket.queue_id=queue.id GROUP ...


2

Use a data-modifying CTE to chain the inserts in a single statement. WITH inskey AS ( INSERT INTO key (key_name) VALUES ($$key_1$$) , ($$key_2$$) RETURNING * -- returns row including newly generated key_id ) INSERT INTO related_key (key_id, related_key_id) SELECT i.key_id, t.related_key_id FROM inskey i JOIN ( VALUES ...


2

It's an inconsistency between rounding of numeric and float types. regress=> select pg_typeof (9 + 330.0/60); pg_typeof ----------- numeric (1 row) regress=> select pg_typeof(9 + f/60) from test; pg_typeof ------------------ double precision (1 row) regress=> select (9 + 330.0/60); ?column? --------------------- ...


2

Bucardo is the standard tool for this sort of job. It's well tested to run when the two servers are disconnected from each other for a while. You could use any master/master replication approach instead: Londiste, Slony, or the early release of BDR from 2ndQuadrant. With the network setup you've described, you will be hard pressed to avoid race ...


2

Possible with a single SELECT: SELECT name, count(*), to_char((count(*) * 100.0 / sum(count(*)) OVER ()), 'FM990.00" %"') AS percent FROM t GROUP BY 1 ORDER BY 1; count(*) is a separate form of the function and slightly faster than count(<expression>). Assuming all columns to be NOT NULL, else you may have to use the ...


2

Such a 1:1 mapping is sensible when part of the table is heavily accessed and part of it is lightly used. It's a good choice.


1

The fragment you posted so far can be simplified to: INSERT INTO table2 (id, name, date) -- why "date" if you insert a timestamp? SELECT NEW.id, t1.name, NEW.timestamp FROM table1 t1 WHERE ST_DWithin(NEW.position , ST_SetSRID(ST_MakePoint(t1.longitudedecimal, t1.latitudedecimal), 4326) , 0.01447534783) AND t1.id > ...


1

Information schema vs. system catalogs We have had discussions about this many times. The information schema serves certain purposes. If you know your way around the system catalogs, those serve most purposes better, IMO. The system catalogs are the actual source of all information. The information schema provides standardized views which help with ...


1

SELECT INTO is usually used to select specific set of data into a table, esp., during scenarios when the data in the table is the priority and not the constraints. It automatically creates a table if there is no such table already. But, INSERT INTO is used when you already have a table that has specific defined constraints and need to add data from a ...


1

First of all, depending not just on the number of rows but also on the total size of your table (based on the average row size) , it may not be excessive to create a new Foreign Key. Especially if scheduled properly. However, it may well be too heavy a change. In that case, you could consider creating an Intersection Table like: CREATE TABLE FlightPair ...


1

Another way, using the LAG() function: WITH x AS ( SELECT hotel_id, day, LAG(day, 3) OVER (PARTITION BY hotel_id ORDER BY day) AS day_start, 2 * SUM(price) OVER (PARTITION BY hotel_id ORDER BY day ROWS BETWEEN 3 PRECEDING ...


1

SELECT name,COUNT(*), ROUND(100.0*COUNT(name)/(SELECT 100.0* count(name) FROM t),3) as percentage FROM t GROUP BY name FIDDLE


1

Is the autovac "to prevent wraparound" (as reported in pg_stat_activity)? If so, PostgreSQL considers that an emergency and refuses to yield the lock. Which is rather unfortunate in this case, as once you drop the table the situation will be resolved. But it doesn't know why you want the lock.


1

Some options: -- regexp_matches SELECT string_agg(arr[1], '') AS string FROM regexp_matches('The United States of America', '\y(?!(the|of)\y)\w', 'gi') arr; -- regexp_split_to_table SELECT string_agg(left(word, 1), '') FROM regexp_split_to_table('The United States of America', '\s+') t(word) WHERE NOT (word ILIKE ANY ('{the,of}'::text[])); -- Without ...


1

This is a simple adjacency list traversal problem. You're not even trying to build paths, just find nodes from a root node or set. That's pretty much a textbook example of what a recursive CTE is for. http://sqlfiddle.com/#!12/8c406/12 WITH RECURSIVE children(product_content_id, product_master_id, content_id) AS ( SELECT pc.product_content_id, ...


1

Use the information_schema views, they're SQL-standard and contain the information you want. You can also directly access pg_class, pg_attribute, etc, but that's unportable and often fiddlier; you may need helper functions like oidvectortypes, pg_get_function_arguments, etc for some things. If you want to see how psql executes something like \dt, run psql ...


1

You can use the psql command line client. \dt will show a list of tables \dv will show a list of views \d [object_name] will describe the schema of the table or view Not sure how you would describe a query though. More info: https://manikandanmv.wordpress.com/tag/basic-psql-commands/


1

I would say the most advanced database access layer these days is Spring Data JPA. It builds fast, correct DAOs for you at startup time. I would check that out, maybe read some of the source, and gain inspiration from there. As for connections, it depends on the app. For desktop to db apps, you can keep a connection open for the entire user session. For ...



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