Tag Info

New answers tagged

0

You can always prefix the query with the postgres comment string: --select foo from bar; and the query will not run but will be available in the psql deadline history buffer for editing.


0

After some back and forth, I managed to figure what was actually going on and how to fix it. the function that is reading from the database expects to get a timestamp in a given format To me this means that the given function expect these to be text parameters, not timestamps. It would otherwise be content with the timestamp... – dezso Jan 27 at 20:22 ...


2

Be careful. Your two designs are not the same. Case 1 implements a unique constraint on the name of the book but includes case, so "The Lord of the Flies" and "the lord of the flies" would be different. Case 1 then creates a second index to support efficient searching of queries of the form select * from book where lower(name) like 'the lord%'; Case 2 ...


0

As a horse_with_no_name suggested, I would also go with hstore if you are using PostgreSQL becuase I think its the easiest to morph back to regular table structure if you decide you need that. There are a couple of features that make hstore pretty nice for your problem: 1) Indexing is pretty fast 2) You can query hstore column with something like SELECT ...


0

Your question looks like an instance of a very widespread pattern. This pattern goes by the name "generalization and specialization" in Extended ER modeling. It also goes by the name "classes and subclasses" (or "types and subtypes") in object modeling. When designing an object oriented data structure, the concept of inheritance plays a key role in the ...


1

This counts ranges of at least 3 days with minutes_asleep = 0. A continuous range of 5 days still counts as 1. A continuous range of 6 days counts as 2. Etc. Ignoring all other entries where minutes_asleep is different. SELECT sum(ct) AS total_count FROM ( SELECT (count(*)/3)::int AS ct -- integer division truncates as desired + see below FROM ( ...


0

You need to create a pseudo date range and join to it... Like such: select generate_series('2014-01-01'::timestamp, '2014-10-01'::timestamp, '1 month'::interval)::date; Once you have your series, you can join to it and use COALESCE to fill in 0's for where there is no data.. WITH days AS ( select generate_series('2014-01-01'::timestamp, ...


0

For a table created like this: CREATE TABLE public.delete_key_bigserial (id bigserial PRIMARY KEY NOT NULL); ... both my queries (as well as pgAdmin, psql or any other decent client) would find the PK constraint. If it's not there, you removed it somehow. Note that my first query in the previous answer only returns the column it is the PK and a serial ...


0

If you are running away from MySQL and hoping that PostgreSQL is better, then first consider what type of UUIDs you have. If they are the 'sequential variant' (Type 1), and if you have some clustering by time, then MySQL (or any database) can take advantage of it. Here is a discussion of such: http://mysql.rjweb.org/doc.php/uuid Although MySQL does not ...


1

MySQL While the MySQL Documentation literally says Typically, the clustered index is synonymous with the primary key, they are not one and the same. Please keep in mind that the clustered index (called gen_clust_index) was created in such a way that the index pages for the PRIMARY KEY and the table's row data coexist in the same pages. Having wide PRIMARY ...


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


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


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.


0

Run the following command to get the database credentials from Heroku: heroku pg:credentials DATABASE_URL Then you can use a GUI tool like PG Commander or PGAdmin to connect to the db.


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


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


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 ([]). ...


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


0

For anyone curious, here is the updated query plan incorporating the changes suggested by Erwin Limit (cost=131.47..131.48 rows=15 width=648) (actual time=2.816..2.880 rows=100 loops=1) -> Sort (cost=131.47..131.48 rows=15 width=648) (actual time=2.815..2.835 rows=100 loops=1) Sort Key: messages.updated_at Sort Method: ...


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


0

Online SQL beautifier1 AND beautifier2


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


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


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


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


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


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


0

What you ask for would work like this: UPDATE "User" u SET ( user_key, user_id, user_status) = (x.user_key, x.user_id, x.user_status) FROM "User" x WHERE u.user_id = '003' -- string type? AND x.user_id = '004'; Much doesn't seem right here. Prominently: The task itself doesn't seem to make any sense. I also assume you don't want to ...


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


0

One option is to simply cast udt_name to a regtype: SELECT column_name, data_type, udt_name::regtype FROM information_schema.columns WHERE table_schema = 'public' AND table_name='table_name'; Another option is to use the function format_type(), but that needs the oid of the data type: select c.attname, pg_catalog.format_type(c.atttypid, NULL) as ...


0

It seems to me that you need to set the Timestamp column to CURRENT_TIMESTAMP(3). This would set the column to return the time to the three digit precision you are looking for.


0

Step 1. your_path_to_postgres-x.x.x/bin/initdb -D ~/data Step 2. /home/your_username/data -l logfile start Step 3. psql postgres Hope it helps! Danish Khakwani


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;


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


0

You could GROUP BY before you do any joining... Which means, reverse your query. This is your OLD query SELECT person.name, person.id, license.expiry_date, COUNT(car) FROM person JOIN license ON license.person_id = person.id JOIN car ON car.owner_id = person.id WHERE person.name = 'Charles Bannerman' GROUP BY person.id; This is your NEW query WITH ...


0

SELECT person.name, person.id, license.expiry_date, COUNT(car) OVER (Partition by Person.Id) FROM person JOIN license ON license.person_id = person.id JOIN car ON car.owner_id = person.id WHERE person.name = 'Charles Bannerman'; You can use OVER (partion by <grouping column(s)>). This will aggregate the data with out having to ...


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


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


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


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


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


1

seharusnya seperti ini(supposed to be like this): SELECT person.name, person.id, license.expiry_date, COUNT(car) FROM person JOIN license ON license.person_id = person.id JOIN car ON car.owner_id = person.id WHERE person.name = 'Charles Bannerman' GROUP BY person.name, person.id, license.expiry_date, car.car;


1

From docs (http://www.postgresql.org/docs/9.4/static/datatype-json.html) try to use expression index: CREATE INDEX idx_jsonthings_names ON jsonthings USING gin ((d -> 'name')); SELECT d FROM jsonthings WHERE d @> '{"name": ["First", "Second"]}';


0

I believe the difference is likely to be due to a difference in the clocks on each server.


0

You could create a table to hold the key/value pairs like such: CREATE TABLE KEY_VALUE ( ID BIGINT, -- THIS COULD BE A FKEY TO YOUR '300K' RECORD TABLE KEY VARCHAR, VALUE VARCHAR ); As long as this table is indexed properly (an index on id/key, I imagine), getting any key/value pairs you are interested in should still be very quick, even if it's ...


0

If you build an index like: CREATE INDEX idx_indexname ON yourtable (LOWER(columnname) ASC NULLS LAST); Lower will use index. ILIKE will use index, but only in some case. I suggest starting here: http://www.postgresql.org/docs/9.2/static/indexes-types.html


0

Measure it and see, using explain analyze on test data. I would expect a significant difference between an expression index on lower(col) with lower(col) = lower('constant') vs use of col ilike 'constant', with the expression index on lower(col) the faster. That's because Pg can do a bitmap index scan with the expression index, but for the ilike it'll have ...


1

DELETE FROM fps WHERE fps.id NOT IN (SELECT DISTINCT fps_id FROM fmmp) The sub query should find a list of fps ids that are being referred to, so you can delete any fps entries that are not in that list.



Top 50 recent answers are included