Tag Info

Hot answers tagged

5

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


5

You can use either n UPDATE statements, one for each column: UPDATE table SET Col1 = NULL WHERE Col1 = ''; UPDATE table SET Col2 = NULL WHERE Col2 = ''; --- UPDATE table SET ColN = NULL WHERE ColN = ''; or one with CASE: UPDATE table SET Col1 = CASE WHEN Col1 = '' THEN NULL ELSE Col1 END, Col2 = CASE WHEN Col2 = '' THEN NULL ELSE Col2 END, --- ...


4

If possible, just store the first value It doesn't look like you need to store two numbers at all. If these are like tax years, i.e. "2014/2015 financial year", you only need to consistently store one part. If you store 2014, your app knows it's the academic year beginning in 2014. So it can display "2014/2015 Academic Year" with no ambiguity. If you ...


4

About SSDs, the main advice is to lower 'random_page_cost' to 1 (equals to 'seq_page_cost') in postgresql.conf, in addition to others usuals settings.


4

You can do something like this: SELECT ((generate_series-1)/16384)::int+1 as series, (random()*200000)::int as rand from generate_series(1,10*16384); This does the sampling with replacement. If it needs to be done without replacement, that is quite a bit hard.


4

[Is it] possible to setup PostgreSQL to use directly a block device (like /dev/sda) on for POSIX systems for data storage? Nope. PostgreSQL would have to implement its own file system to support this. It lets the operating system do that, on the theory that the OS will probably do it better. The project tries to avoid duplicating functionality the ...


3

You need to do a left outer join on the generate_series with an ON clause that matches the date of each report. Without a schema or sample data it's a bit fiddly to cook up an example modification of your actual query. Here's a simplified example to show you how it works: CREATE TABLE sparse_dates( bakedgood text primary key, firstbaked date not ...


3

Maybe something like this: with ranked_visits as ( SELECT w.website_id, v.visitor_id, count(wv.visit_id) as visits, row_number() over (partition by w.website_id order by count(wv.visit_id) desc) as rnk FROM website_visits wv JOIN websites w ON wv.website_id = w.website_id JOIN visitors v ON wv.visitor_id = ...


3

You have corrupted your database by manually deleting files from within the data directory. Never delete files from within the data directory manually. Safely removing WAL If you want to remove WAL, either let the server do it at CHECKPOINT time, or use pg_archivecleanup. Note that the server will remove WAL is no longer needs automatically, unless: ...


3

Use a windowing function. select * from ( select person, date, priority, row_number() over(partition by person order by date desc,priority desc) as p from table ) as foo where p=1; SQL Fiddle link for you to play with.


3

so is there anything I should be afraid of? Not having backups. Like any storage device, it can die. Keep backups. If the data load is going to take ages, I'd back up the read-only db once I'd done the data load, by stopping it and copying it. That way if something went wrong it'd be easier to re-create later. Can I put the entire DB on the SSD, or ...


3

There are a few subtle differences, but nothing drastic: You can add indexes on a temp table; Temp tables exist for the life of the session (or, if ON COMMIT DROP, transaction), wheras WITH is always scoped strictly to the query; If a query invokes a function/procedure, it can see the temp table, but it can not see any WITH table-expressions; A temp table ...


3

What is wrong with the (maybe too obvious?): select * from noun n, noun_inflection ni where n.label = 'handlebar' and n.label ilike '%'||ni.label_singular order by char_length(ni.label_singular) DESC limit 1;


3

select l.* from location as l --- find all locations where not exists --- where there isn't ( select * from location_flag as lf --- a flag where lf.location_id = l.id and lf.flag_id = 1 --- with 1 and lf.value = 'YES' ...


2

You can use a window function for this: select a,b,c from ( select a,b,c, dense_rank() over (order by a,b) as rnk from dbTable ) t where rnk = 1; For the "first" rows, it doesn't matter if you use rank() or dense_rank(). When you e.g. want the "second" ones, the rank() and dense_rank() would return different results in case of ties. Because ...


2

I think this might work, assuming that by not present you mean is NULL: select * from subscription where coalesce ( subscription.renewed_at, subscription.created_at ) between '2013-10-16 00:00:00.000000' and '2013-10-16 23:59:59.999999') The coalesce() function returns its first non-NULL argument, so if renewed_at is present it will be returned, ...


2

SELECT extract(epoch from now() at time zone 'utc'); doesn't return the correct timestamp because postgres timezone conversion throws away timezone information from the result: 9.9.3. AT TIME ZONE Syntax: timestamp without time zone AT TIME ZONE zone Returns: timestamp with time zone Treat given time stamp without time zone as located in the ...


2

This can be done with DDL alone but it's not at all trivial. The following will ensure that any test will either have 0 or 5 test cases: create table test ( test_id integer not null, constraint pk_test primary key (test_id) ) ; create table test_case ( test_id integer not null, case_no smallint not null, ...


2

You can set seq_page_cost and random_page_cost per tablespace via ALTER TABLESPACE without restarting Postgres.


2

I suggest your first option, with two improvements and some simplifications. ( SELECT 1 -- irrelevant what you select here FROM client_category_price WHERE sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1 -- may be redundant ) UNION ALL -- not just UNION ... UNION ALL ( SELECT 1 FROM work_order_item WHERE sellable_id = ...


2

The formats accepted by to_char(timestamp, text) include localized patterns that make it not immutable. Example of different results with the same input: test=> BEGIN; test=> set lc_time='en_US.utf8'; test=> select to_char(now()::timestamp, 'TMDay'); to_char --------- Monday test=> set lc_time TO 'fr_FR.utf8'; test=> select to_char(now()::timestamp, ...


2

Maybe I'm missing something, but it sounds as if that is a simple case of replacing the value: update the_table set link = replace(link, '/ABC/ABC_DATA/Test/', '/CBF/CBF_DATA/Documents/Test_DATA/');


2

Unless the DB is huge, your best bet will be to clone it periodically. Here's what I'd do if you're on PostgreSQL 9.3, which supports cascading replication: On the live server, add a streaming replica that keeps an up to date copy on the dev server. You don't use this DB directly, it's just there so you can copy the DB without disrupting the master. Use ...


2

The usual solution is to authenticate the user within the web app, then issue a SET ROLE or SET SESSION AUTHORIZATION to "become" the user on a JDBC session that's already authenticated with the database using a fixed username. In both cases the DISCARD ALL command that should be run by any connection pool when returning connections to the pool will ...


2

VACUUM can only remove dead tuples which are long-dead, that is, dead to all possible uses. If you have long-lived transactions, they may prevent the recently-dead tuples from being removed. This is an example of a situation where a long-lived transaction prevented removal: INFO: "pgbench_accounts": found 0 removable, 2999042 nonremovable row versions in ...


2

One way: SELECT id FROM products WHERE details->'make' = ? OR details->'model' = ? OR details->'year' = ? ORDER BY CASE WHEN details->'make' = ? THEN 0 ELSE 1 END + CASE WHEN details->'model' = ? THEN 0 ELSE 1 END + CASE WHEN details->'year' = ? THEN 0 ELSE 1 END; It's a bit simpler to invert the logic: ...


2

If I run your example with a cold cache for both queries, then the bitmap index scan really does win out. So in this sense, the planner is getting it correct. Since you are testing under a perfectly hot cache, the correct thing to do would be to lower the random_page_cost and the seq_page_cost both to zero, and if I do that it does select the index scan ...


2

I would use unnest together with array_agg, like this: SQL Fiddle PostgreSQL 9.3.1 Schema Setup: create table t ( A double precision[5], B double precision[5]); insert into t values ('{3,2,0,3,1}', '{1,0,3,2,5}'); Query 1: with c as( select unnest(a) a, unnest(b) b from t) select array_agg(a) a, array_agg(b) b, array_agg(a + b) c from c Results: | ...


2

Using @cha's schema, here's how to do it in PostgreSQL 9.4 using support for unnest ... with ordinality: SELECT array_agg(unnest_a.unnest_a + unnest_b.unnest_b ORDER BY unnest_a.ordinality) FROM t, LATERAL unnest(a) WITH ORDINALITY AS unnest_a INNER JOIN LATERAL unnest(b) WITH ORDINALITY AS unnest_b ON (unnest_a.ordinality = ...


2

You could use two smallint columns: create table foo ( from_year smallint not null, to_year smallint not null, check (from_year = to_year - 1 and from_year >= extract(year from current_date)), unique (from_year, to_year) ); insert into foo values (2014,2015);



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