Tag Info

New answers tagged

0

There is no clear answer to that. Both variants have pros and cons. Many here will advice to properly normalize data with a separate table. You might get best of both worlds with a MATERIALIZED VIEW: Properly normalize your data model, and offer an aggregated view on the data in the materialized view.


1

extract() the epoch from the time component after casting to time (effectively removing the "day" component): SELECT extract(epoch FROM ts::time) AS sec_of_day You get the "number of seconds", including fractional seconds if there are any. Very short and fast. Test (with timestamps in unambiguous ISO format): SELECT extract(epoch FROM ts::time) AS ...


0

The easy answer is "don't do that". You can define multiple table-spaces for PostgreSQL to use, and put them on different partitions. But once you've completely run it out of space, that is hard to do. And if you are planning ahead, it would generally be better to just make your partitions larger to start with, using RAID to make them even larger than a ...


1

You could try it yourself: WARNING: there is already a transaction in progress It starts no new (sub)transaction as nested transactions are not implemented in PostgreSQL. (You may do some magic in a pl/pgsql function, for example, that mimics that behaviour, though.)


1

You could do this using one of the following queries: select date_part('second', timestamp '2011-02-01 20:23:43'); select to_char(timestamp '2011-02-01 20:23:43', 'SS'); select extract(second from timestamp '2011-02-01 20:23:43'); Have fun.


1

Use the extract() method: select extract(second from current_timestamp) + extract(minute from current_timestamp) * 60 + extract(hour from current_timestamp) * 60 * 60; of course this can be put into a function: create or replace function total_seconds(p_timestamp timestamp) returns int as $$ select (extract(second from p_timestamp) + ...


2

psql generally doesn't need much memory when playing large SQL files, since it doesn't buffer the whole file, only one query at a time, or it uses a COPY stream. The main situation when it may run out of memory is not when importing, but when SELECT'ing a large resultset, especially on 32 bits systems. This situation is generally solved by setting ...


0

Adding LIMIT to the query often makes Postgres choose a different query plan. If statistics or cost estimates stray too far from actual data distribution / actual costs, you may end up with a slower query, even though Postgres reckoned it would be faster. Uneven data distribution may cause the query planner to misjudge the selectivity of WHERE conditions. ...


0

Since the data is updated constantly, you will have a hard time getting the benefits from index-only scans. They are mostly effective on read-only or read-mostly data. If that is the case, adding columns to the index will likely be counterproductive. If any of the constant stream of updates were into previously-unindexed columns, those could have ...


0

I use PGCLUSTER=8.4/main pg_dump ... PGCLUSTER=9.1/main pg_dump ...


1

You are not limited to just $$ you can use anything enclosed in $, e.g. $_alex_delimiter_$ insert into foo (some_column) values ($_alex_delimiter_$this is a long string that contains a single $ and two $$ and quotes '''''$_alex_delimiter_$); Just pick something that won't be part of the text.


1

I'd suspect you have standard_conforming_strings set to OFF on the 9.1 instance (meaning it was changed explicitly, since the default is ON since 9.1), and the opposite in 9.2. Demo: test=> select version(); version ...


0

Works for me. regress=> WITH x(v) AS (VALUES ('text ACT txt'), ('ACT tests'), ('tests ACT')) regress-> SELECT v FROM x WHERE v ~* '\yACT\y'; v -------------- text ACT txt ACT tests tests ACT (3 rows) on: regress=> SELECT version(); version ...


2

So I found the answer for Debian at least. Hunspell is the successor of Myspell, and they ship with your distribution (in my case Debian). These are the dictionaries you can use. You can install these by executing apt-get install hunspell-en-us apt-get install hunspell-de-de These dictionaries are installed in /usr/share/hunspell or ...


0

Thanks guys for the suggestions, I think I found the problem. Each night, several bash scripts drop and recreate some tables, which each time get a new OID. This would explain why a different OID was appearing in each successive error message, as suggested by Josh. I believe that on some occasions, pg_dump started while some of these tables were still ...


1

You can put the database into recovery mode by forcing an unclean shutdown of any of the backend processes, other than the postmaster itself. For example, with kill -9. Yes, PostgreSQL restarts. That is why it going into recovery mode. Recovery mode is a part of starting up again after an unclean shutdown. If the database was idle when it crashed, then ...


1

If you want to condense it into a single SELECT, this would work: SELECT DISTINCT ON (coalesce(t2_id, t1_id), t2_id) t1_id, t2_id FROM t0 ORDER BY coalesce(t2_id, t1_id), t2_id, t1_id; Equivalent, except for sort order. SQL Fiddle. If you want this to be fast, I'd try a functional index: CREATE INDEX t0_func_idx ON t0 (coalesce(t2_id, t1_id), ...


1

If you want to force PostgreSQL into recovery, you can create a recovery.conf with no restore_command or streaming replication configuration set. Leave hot_standby set to off in postgresql.conf. Personally I recommend taking a base backup (pg_basebackup -X stream) and using that, rather than doing it to the original DB.


1

I now believe my question to be a duplicate of Slow ORDER BY with LIMIT. I simplified the query (taking out the complication of a join table) to what matters. Fast query explain (analyze, buffers) SELECT * FROM big_table where small_table_id = 822573 ORDER BY sort_key_1, sort_key_2, sort_key_3 Sort (cost=24642.79..24686.64 rows=17540 width=113) ...


1

For the simple case, I can only think of minor improvements to the query: ( SELECT DISTINCT ON (t2_id) t1_id, t2_id FROM t0 WHERE t2_id IS NOT NULL ORDER BY t2_id, t1_id -- to get consistent results ) UNION ALL ( SELECT DISTINCT ON (t1_id) t1_id, NULL -- cheaper FROM t0 WHERE t2_id IS NULL -- if you retrieve ...


0

For starters, I suspect your current query is incorrect, missing an additional condition AND v.user_id is null. Your query, simplified with table aliases and improved somewhat: SELECT DISTINCT ON (coalesce(e.user_id, e.browser_id), e.user_id) e.browser_id, e.user_id, v.time FROM events e JOIN ( SELECT DISTINCT ON (coalesce(user_id, browser_id), ...


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


1

It's a case-sensitivity issue. "dateType" including the quotes is a quoted identifier, which implies that it retains its case (as opposed to an unquoted identifier which is implicitly converted to lower case). Quoted identifiers allows other columns of the same table named for instance "DateType" or "datetype" or other variants that differ only by case (not ...


2

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


1

With REVOKE e.g.: REVOKE postgres FROM tester;


1

It can be done, but it's not necessarily fun, or fast. Your best bet for fuzzy matching is "Soft TFIDF" (pdf), probably using Jaro Winkler similarity. Jaro Winkler is similar to Levenshtein but weights letters more heavily at the beginning of a string. It's available in the pg_similarity extension. Here's a function I wrote a while ago, which I haven't ...


0

Even thouth the docs, states that all fields are synchronized between the parent table and its child (copied before). The optional INHERITS clause specifies a list of tables from which the new table automatically inherits all columns. Use of INHERITS creates a persistent relationship between the new child table and its parent table(s). Schema ...


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


0

This is what I came up with: /* Get the records of original table that correspond to each value of A and for values of B better or the same for the top-k records */ SELECT n3.A,n3.B,n3.C FROM dbTable n3, ( /* n2 = Get the worst value of B per A for the top-k records */ SELECT A, MAX(B) AS B FROM /* n1 = Count records per A, ordered by B */ (SELECT A, ...


0

From the docs: "LIKE....Unlike INHERITS, the new table and original table are completely decoupled after creation is complete. Changes to the original table will not be applied to the new table"


0

If you know which rows were accidentally updated, you just don't know what the previous value of those rows were, then what you can do is: Restore to a new non-production server from your base backup. Recover that server to the point just before the error. Write a query to select the values for the rows you know got mistakenly updated. Turn that into a ...


0

Instead of matching the portion of the string that I wanted to fuzzy search on I instead, concatinated all of the terms into a string, and then ran the difference function (from the fuzzystrmatch module) against it. So it looked something like this. FOR term IN terms LOOP term_string := term_string||' '||term; END LOOP; score := score + ...


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


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


0

No, you can't assign a transaction an arbitrary user-defined identifier. Transactions already have transaction IDs, but only read/write transactions get true, persistent ones. I'm asking this if transaction has identifiers then true asynchronous database call will be possible with frameworks like Netty or Vert.x Having a transaction identifier would ...


1

Disable any FKs pointing to the table Create table SAVE as SELECT * FROM A Truncate table A Begin to create a script with the following with xfer as (select cast ('a' as text) as targ_tab, cast ('b' as text) as from_tab) select xfer.*, tabs.table_name as targ_table_name, cols.table_name as from_table_name, 'alter table ' || tabs.table_name || ' add (' ...


1

Well, you are running this giant UPDATE statement of all rows in table B: update B t1 set b1 = t2.a1 from (select b1, b2 from B) t2 where t1.a2 = t2.b2; which will more-or-less double the size of the table, since the UPDATE has to keep old row versions around. You may want to read up a bit on how PostgreSQL implements MVCC and how vacuuming works, but to ...


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


1

Pretty much a WAG because you haven't shown EXPLAIN (BUFFERS, OUTPUT) data, but: select id from demo_bbb b; -- database query time: ~26ms If you're on 9.2 or newer this probably did an index-only scan on the unique index used to implement the primary key constraint. Given the latter times that's the most likely case; if it'd done a seqscan (as it would on ...


0

In my opinion the pros are: when using queries, you can select a specific row inside brand_product, by only using brand_product_id. if you want another table, connected to the brand_product table, you can specify the rows by just using the brand_product_id rather than the combination of brand_id and product_id (though, it's rare to connect a third table to ...


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

Give your example, this will do it: select distinct on (coalesce(t2_id, t1_id)) t1_id, t2_id from t0 order by coalesce(t2_id, t1_id); It essentially says "do a distinct on t2_id, but if that is null use t1_id instead".


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


1

The WHERE o.date ... condition makes the outer join behave like an inner join, cutting out any rows of peoplethat don't have at least one matching row in orders. You'll have to move the condition about o.date from the WHERE clause to the joining ON: SELECT p.id, p.name, coalesce(sum(o.price), 0.00) AS total FROM people p LEFT OUTER JOIN orders o ON ...


1

You need 2 columns in the foreign key constraint, to refer to a table with 2 columns in the primary key: -- Second higher level combining CREATE TABLE full_b_c( b_ID INT NOT NULL, c_ID INT NOT NULL, a_ID INT NOT NULL references a ON UPDATE CASCADE, PRIMARY KEY (b_ID, c_ID, a_ID), FOREIGN KEY (b_ID, c_ID) references b_c (b_ID, ...


1

I just wanted to add that in addition to to changing pg_hba.conf file from local all all peer to local all all md5 As per the accepted answer, the only way I could log in after doing the same thing the OP was doing was to pass the -h flag when trying to login. $ psql -U postgres -h localhost Hopefully, this will help someone in ...


1

You didn't tell us what the "failed attempt" means. But something like this should work: with ldist as ( select name, levenshtein(substring(name,1,200), lag(name) OVER (order by name)) as distance FROM books WHERE name <> '' ) select * from ldist order by distance; you don't want partition by name because that essentially ...


1

Try adding books.id to ORDER BY. If books_data.date is not unique, relative order of rows in resultest is not guaranteed. Say you have 5 rows with date1, 10 rows with date2, and 5 with date3; date1 < date2 < date3. Then some rows with date2 may appear on both pages, some on neither, and , if you are lucky enough, you may sometimes see expected ...


0

Microsoft's Jim Hogg has responded to this issue with the following: There are pros and cons. On the pro side, it seems like a good way to avoid some errors - having to check a (signed) int has value > 0. And I would also venture that many uses of int in fact relate to counts that should never be negative anyway. On the question of doubling max row ...



Top 50 recent answers are included