Tag Info

Hot answers tagged

6

select id, name, v[1] as major_version, v[2] as minor_version, v[3] as patch_level from ( select id, name, string_to_array(version, '.') as v from versions ) t order by v[1]::int desc, v[2]::int desc, v[3]::int desc; SQLFiddle: http://sqlfiddle.com/#!15/c9acb/1 If you expect more elements in the ...


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


5

A UNION by default will filter out duplicate rows (essentially adding an implicit DISTINCT) which means comparing the rows output to filter them. If you have duplicate rows that you need filtering out then you may need to cast the JSON type into something else (can you cast it to a string type? - I'm not familiar with postgres and JSON support). If you do ...


5

Running an EXPLAIN ANALYSE VERBOSE massively underestimates the time taken to return the results There's a misunderstanding here, because EXPLAIN ANALYZE does not estimate, it runs the query for real and reports the actual time taken by each steps, as opposed to EXPLAIN without ANALYZE that just reports the estimates without running the query. ...


5

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


4

If your pairs are unique, as in the example you gave, you want the values of b that appear only once, and this would be a solution: SELECT a,b FROM tbl1 WHERE b in ( SELECT b FROM tbl1 GROUP BY b HAVING (count(b)) = 1 ) You can see it in this fiddle http://sqlfiddle.com/#!15/c82d3/3


4

A unique constraint creates a unique index to implement the constraint. The only index type in PostgreSQL that supports unique indexes is the default b-tree index type; you can't make a unique GIN or GiST index, e.g.: regress=> create unique index indexname on test USING GiST(id) ; ERROR: access method "gist" does not support unique indexes Data types ...


4

OK, so you: Dropped a bunch of constraints Did some work Attempted to add a completely unrelated random constraint and that failed You only showed two of the constraints in the output (why?) but the two you showed were CHECK constraints, not UNIQUE constraints. So it makes absolutely no sense to attempt to replace them with a UNIQUE constraint. You ...


4

The IN() operator is equivalent to a series of OR, which doesn't help you at all here. Instead, I would build the query in a manner like this. SELECT posts.id FROM posts WHERE posts.id IN (SELECT post_id FROM post_categories WHERE category_id=1) AND posts.id IN (SELECT post_id FROM post_categories WHERE category_id=2) AND posts.id IN (SELECT ...


4

Whenever you need to examine the structure of your database via code, always think "I should look at information_schema or pg_catalog". information_schema contains a standardized schema (66 views), whereas pg_catalog is PostgreSQL-specific, but contains more info (97 tables or views). select * from pg_catalog.pg_indexes where indexdef ~* ...


4

You could try simplifying and using LATERAL for joining the laps table: SELECT races.*, tmptimers.last_start_time, tmplaps. last_updated_at FROM races LEFT JOIN timers AS tmptimers ON tmptimers.user_id = 1 AND tmptimers.race_id = races.id LEFT JOIN LATERAL ( SELECT updated_at AS last_updated_at FROM laps WHERE ...


4

but using the "limit" keyword cant prevent the search over the whole set of data(is it correct?) Correct; it'll just limit how many results are returned. For some kinds of query it can also limit how many are scanned in the first place, but you can't rely on that in the general case. does PostgreSQL database have to create this 1M search result ...


3

Going out on a limb here (basic information is missing), partial indexes will probably be your best bet. Much easier to handle than partitioning the whole table, it offers similar performance for the split case and allows much better performance for queries on the whole table: CREATE INDEX tbl_nodelay_idx ON tbl (tbl_id, ??) WHERE delay <= 0; CREATE ...


3

Most importantly, this is a special case of relational division. Once you know the name of the beast you'll find plenty of query techniques. Like the arsenal we assembled on SO: How to filter SQL results in a has-many-through relation Building on this test case (which you should have provided): CREATE TABLE post ( post_id serial PRIMARY KEY , post ...


3

The CTE is not needed here and poses as optimization barrier. A plain subquery generally performs better: SELECT * FROM ( SELECT id ,rank() OVER w AS global_rank ,lag(slug) OVER w AS previous_slug ,lead(slug) OVER w AS next_slug FROM entries WHERE competition_id = 'bdd94eee-25a4-481f-b7b5-37aaed953c6b' ...


3

select a.host, b.host, b.volume from hosts as a join hosts as b on b.volume like '%'||a.volume||'%' and a.host <> b.host; Instead of LIKE you can also use position which might be easier to read: select a.host, b.host, b.volume from hosts as a join hosts as b on a.host <> b.host and position(a.volume in b.volume) > 0 ...


3

To produce your desired output, you can simply: SELECT id, version FROM versions ORDER BY string_to_array(version, '.')::int[]; One can cast a whole text array to an integer array (to sort 9 before 10). One can ORDER BY array types. This is the same as ordering by each of the elements. SQL Fiddle (reusing @a_horse's fiddle, thanks!)


3

As Craig explained very well, you cannot create a unique index on the type raster without all the necessary operators. Your second best bet to enforce uniqueness is to create a functional index on the text representation: CREATE UNIQUE INDEX us_tmin_enforce_scalex_rast ON chp05.us_tmin (cast(rast as text)) Note that this cannot be implemented as ...


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

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


3

Incorrect SQL was the problem. The ALTER COLUMN must be part of an ALTER TABLE. This SQL does indeed alter the data type of the existing column from VARCHAR to the newly-created domain. ALTER TABLE unit_ ALTER COLUMN fuel_ SET DATA TYPE fuel_domain_;


3

A few things you can do: Use enums or lookups keyed by integer values, or a simple "char" field, instead of varchar sort keys where possible. I'd use an enum because you can control the sort order easily. The only serious downside with an enum is that you can't currently drop values from an enum type. You can add them (including inserting them in the ...


2

The issue is covered in this detailed blog. In short, the master and the standby servers must all have archive_mode = on and an archive_command, until 9.3 which removes this requirement.


2

This is of course not an exact answer to your question, but if you don't need to access the history table, you can as well generate an SQL dump: pg_dump -h host -p port -w -U user db > dump.sql Then one could use a tool like git to calculate the difference and store this efficiently. git add dump.sql git commit -m "temp dump" git gc --aggressive ...


2

I always use CITEXT for email, because an email address is (in practice) case insensitive, i.e. John@Example.com is same as john@example.com. It is also easier to setup an unique index to prevent duplicates, as compared to text: -- citext CREATE TABLE address ( id serial primary key, email citext UNIQUE, other_stuff json ); -- text CREATE TABLE ...


2

create extension semver; select id, version from SoftwareReleases order by version::semver; http://www.pgxn.org/dist/semver/doc/semver.html


2

Guess I don't need to tell you that this is a seriously awkward data model. Anyway, I think this query would do what you're looking for: SELECT subq2.sales_time, subq2.num_sold, subq2.effective_price, subq2.effective_price * subq2.num_sold AS total_sale_price FROM ( SELECT subq1.sales_time, subq1.num_sold, subq1.max_price_ts, (SELECT price FROM ...


2

If I interpret your question correctly: "Pick all rows where a has one of two given values, and b only exists in combination with one of them." You can use any of the standard techniques laid out in the referenced answer. Help with this SELECT in the same table Just restrict your base table to the two given a. For instance SELECT * FROM tbl t1 WHERE ...


2

pg_settings is a view, not a table. And every session has its own version of it. Per documentation: The change only affects the value used by the current session. The attempt to access it from a different connection (session) is futile.


2

IN is like writing = ANY, e.g. regress=> SELECT 1 = ANY (ARRAY[1, 2, 3]); ?column? ---------- t (1 row) A simplistic interpretation of your question would be to say that you're asking for = ALL, but that rarely makes sense: regress=> SELECT 1 = ALL (ARRAY[1, 2, 3]); ?column? ---------- f (1 row) What you really want is to find all posts ...



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