Tag Info

Hot answers tagged

4

Your query would fail, because the standard name of an integer is "integer", not "int". You can avoid this kind of error by comparing the internal regtype OID instead of a text representation. Many basic data types have several alias names, they all resolve to the same internal registered type. That aside, you can largely simplify and improve: SELECT ...


4

There might be hardware issues, too - how should we know? But there are certainly issues with the query. First of all, remove DISTINCT from your VIEW definition. It's doing nothing at all (but complicating and slowing things down). Related answer on SO with explanation: PostgreSQL - Slow query joining on a VIEW Arriving at this (cleaned up) query: ...


4

Ask and you shall receive. Try here for a basic intro to PostgreSQL and JSON. Also, if all else fails, try the docco here. Check out the pretty_bool option. [EDIT in response to OP's comment] Your original question was "Is there a way to export postgres table data as JSON?". Fairly clear question to which I believe that I provided a clear answer. From my ...


3

Unfortunately = ANY (array) only works with an array literal on the right hand side, not a sub-select. You need to "normalize" your de-normalized model, using unnest(): SELECT latitude, longitude FROM userloc WHERE id IN (SELECT unnest(interested) FROM donedeals WHERE deals_id = 64); If deals_id is unique in the donedeals ...


3

Apparently, you want to see constraint exclusion at doing its work. As in similar setups the parent table has no constraint (and, just to mention it, usually no rows), it will be always visited by queries on the parent table. Using your example schema, see the output: test=# EXPLAIN SELECT count(1) FROM p; QUERY PLAN ...


3

if in a row col1 is greater then or equal two col1 in another row, then the same relation is valid between the two corresponding col2 entries In which case you can reformulate your query to look like: SELECT * FROM table WHERE col2 >= val1 AND col2 <= val2; because you can find the lower bound for col2 from the lower bound for col1, like this: ...


2

The simplest solution without PostGIS would be to store lat/long as two number columns. numeric for exact precision. double precision or even just real if you don't need the precision. I see no reason why the data type point shouldn't work as well. Per documentation: Points are the fundamental two-dimensional building block for geometric types. Values ...


2

The alter table was probably waiting to get an exclusive lock on the table. If you have transactions touching the table, the ALTER TABLE will wait until all those transactions are committed You can check if your statement is waiting for such a lock by looking at pg_stat_activity and pg_locks. If you have sessions that are shown as idle in transaction ...


2

I think the best option is to use nexval on the sequence of the new table. with x as ( select nextval('new_table_id_seq') as new_id, A.x, B.y from A JOIN B ON A.w = B.z ), y as ( insert into new_table (id, x,y) select new_id, x,y from x ) select new_id, x,y from x;


2

I'm not sure I fully understand what you expect to happen with the SQL you pasted .. (add up the numbers from 1-99 ignoring all even values?) But this is a SQL to get around the syntax error you have: WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n+1 not in (t.n) and n < 100 ) SELECT sum(n) FROM t; I would like to ...


2

The problem is similar to this one: How to have a one-to-many relationship with a privileged child? The "at most one per group" part of the constraint can be solved with a partial index: CREATE UNIQUE INDEX is_FavoriteChild ON x (yid) WHERE is_principal ; Another way to solve the problem is by removing the is_principal column and add a 3rd table. ...


2

This is only for Postgres: If the tablespaces reside all on the same harddisk, then there is no performance difference at all. If the tablespaces are created on different harddisks then yes, this can make a difference. In that case the necessary I/O is spread over multiple disks, thus improving the overall throughput (assuming all harddisks have the same ...


2

Typically, this whould better be rewritten as JOIN: SELECT u.latitude, u.longitude FROM userloc u JOIN donedeals d ON u.id = ANY (d.interested) WHERE d.deals_id = 67; I also considered the "is contained by" operator: <@, that @a_horse already mentioned. It can use a GIN index on interested. But on a second look, that's irrelevant here. This ...


2

I think you need something using ROW_NUMBER() and (integer) division by 2, along the lines of this: WITH p AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY supplier_id ORDER BY id)-1 AS rn FROM products ) SELECT * FROM p ORDER BY rn/2, supplier_id, rn ; Tested at SQLFiddle.


2

The error is harmless but to get rid of it, I think you need to break this restore into two commands, as in: dropdb -U postgres mydb && \ pg_restore --create --dbname=postgres --username=postgres pg_backup.dump The --clear option in pg_restore doesn't look like much but actually raises non-trivial problems. For versions up to 9.1 The ...


1

I have 3 solutions for you: Direct reference of sequence and using concat One possible solution is to reference the seqence in insert statement directly and prepend your node-id. A similar question including answer you can find here: http://stackoverflow.com/a/17925601/4206293 Using a UUID Another possible solution is, if you don't need you node-id in ...


1

The task as I understand it Pick rows from a table where the period overlaps with a given time frame. Determine distinct ranges of overlapping periods within that set and return the greatest sum(quantity) from any range. Requires Postgres 9.2+, since there are no range types in older versions. Assumptions All values in period have inclusive bounds ([]). ...


1

varchar keys are okay, but they do have some issues: varchars use more space than ints you are more likely to have to update a varchar value than an int value, causing cascading updates might not be appropriate in internationalized applications (i.e. different values for different languages) To answer your particular question, no there won't be a pointer ...


1

Although the representation of the data differs between the old and new schema the data itself should be the same. This means a series of reconciliation queries will provide certainty that the migration was without error. Say you have an Orders table. The total number of Orders per year, month or day will be the same in the old and new databases. ...


1

I'm guessing your << is not utilizing an index, because your primary key would create a btree index, and what you need to make that particular operation indexable is a gist index. (use explain analyze to confirm). if that is the case that index not being used, Try doing something like CREATE INDEX idx_network_info_network_gist ON ...


1

Perhaps there's a better alternative but I can only think of joining back to the 2 tables. This assumes that new_table has a unique constraint on (x,y) and that these columns are not nullable: with ins (id, x, y) as ( insert into new_table (x, y) select A.x, B.y from A join B on A.w = B.z returning id, x, y ) -- insert into another_table (id, z) ...


1

Explanation My question is: why does this not use the index amplifier_saturation_start? Even with 30,000,000 rows, only 3,500 in the date range it can be faster to read tuples from the top of the index amplifier_saturation_lddate on lddate. The first row that passes the filter on start can be returned as is. No sort step needed. With a perfectly ...


1

Have you tried using ranges? Maythe the indexing methods for them (gist) can give you desired performance (for large datasets). Though gist indexes come with some tradeoffs (size, build-time, index-time for simple queries). Some testing code: create table t(id serial primary key, some_col text, foo numrange); insert into t(some_col, foo) select 'foobar', ...



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