Hot answers tagged

26

There is nothing special about it. As described in PostgreSQL documentation, Creating a Database, you need to be connected to a database, in order to create another one: Since you need to be connected to the database server in order to execute the CREATE DATABASE command, the question remains how the first database at any given site can be created. The ...


6

If we slice your query, the first cte (rt2) returns this: id hid parent_hid hierarchy 11 2 1 2 22 3 2 3 44 5 3 5 77 8 5 8 170 11 8 11 190 13 11 13 240 18 15 18 255 20 18 20 The first SELECT ...


3

The error is that the INTO clause is not part of the SQL command. It's part of the plpgsql command EXECUTE. And dynamic field names are currently not possible, neither in SQL nor PL/pgSQL. But there are ways around this limitation: Proof of concept Assign to NEW by key in a Postgres trigger How to set value of composite variable field using dynamic SQL ...


2

The bad plan is probably a culmination of many problems. Which means that there are many ways of tackling it. My guess is that the culmination of problems causes two plans to look falsely close to each other in cost, and then the differences in memory setting (probably effective_cache_size) between master and slave is the straw that broke the camel's back ...


2

I do not agree with the approach you are thinking of using, but if you are dead set on using it, then I would probably structure the query like this: SELECT path, markdown FROM public."Comment" where path = '/other/relative/path/' AND (TRUE OR markdown = 'here I am'); Essentially adding a 3rd parameter to your query .. one use case would use the 3rd ...


2

Use this query instead: SELECT path, markdown FROM public."Comment" WHERE path = $1 AND ($2 IS NULL OR markdown = $2); To ignore the second parameter, pass NULL for it. Recent related question with more info: Conditional WHERE in plpgsql function


2

You can do it with: SELECT u.id, u.given_name, u.family_name, u.email, COUNT(1) as num FROM users u JOIN tasks t ON (u.id=t.user_id) WHERE t.created_at BETWEEN %s and %s GROUP BY u.id, u.given_name, u.family_name, u.email, (DAYS_AGO_7_CONFIG,YESTERDAY)) To address the question in the comments, you can add a HAVING clause after GROUP BY ...


2

AIUI, your wish is to shorten the RETURNS clause of the function. Not sure if you want to establish a dependency on the row type of the table at the same time, but that would make sense here, too. The form RETURNS SETOFrettype relies on the used type to be stored in the system catalogs. The manual: The return type can be a base, composite, or domain ...


2

You can use an exclusion constraint instead of a unique constraint: alter table protein_seq add exclude using hash (sequence with =) An exclusion constraint is much more general than a unique constraint, and allows you to exclude on things like overlapping ranges. When you exclude "with =", it reduces to basically the same thing as a unique constraint, ...


2

First of all: it would be more efficient to store 4 bits of information as separate boolean columns - in every respect: easier to handle, easier to understand and change, easier to index, even smaller storage size! Closely related answer: Does it make sense to store a couple of Boolean values as array? To answer your question asked: you can use a partial ...


1

Based on the replies you gave to my comments you must figure out how to resolve the duplicate keys issue. Also I do not think that master-slave replication is appropriate in this case. I would, after making sure that there are no duplicate keys, use pg_dump to transfer your data into AmazonRDS one by one. Perform checks after every database that you do. If ...


1

pg_dumpall --clean will not issue DROP DATABASE statements for databases like template1 or postgres that have been created by initdb at cluster initialization time. However it will dump the contents of these databases if you created any objects in it (by default they're empty). So when restoring the result of pg_dumpall --clean into the same instance, it's ...


1

If you have access to the OS account under which postgres runs, you can identify the PID of your process (using top, or ps, or pg_stat_activity), and then do a kill -SIGSTOP <pid>. Later you would then do a kill -SIGCONT <pid> to get it to run again. This is not a risk-free thing to do. If you stop the process while it is holding a spinlock (...


1

What language are you calling the query from? I've used query builders to add additional criteria to a base query. In this case, the query builder would add the check on markdown only if there was a value to compare it too. The AND markdown = ALL conditional as you describe it is no different that if you had no markdown comparison in the WHERE clause.


1

The two-column btree index will help with the like 'foo%' query, but probably not dramatically so. It helps because it can be executed as an index-only scan, and so it can compute the LIKE portion within in the index without ever having to visit the table. The index scan will jump to the first entry > '01/01/2012', and then traverse from there to the ...


1

This looks like a good fit for Postgres' JSON functions: select to_json(x) from ( select c.*, json_agg(ct.thing_id) as "thingIds" from container_thing ct join container c on ct.container_id = c.id group by c.id ) x SQLFiddle example: http://sqlfiddle.com/#!15/cd9992/1


1

Join + string_agg() select '{"id": "' || cast (c.id as varchar(36)) ||', "thingIds": ["' || string_agg(cast(ct.thing_id as varchar(36)),'","') || '"]}' from container c join container_thing ct on c.id=ct.container_id group by c.id;


1

A join (on container_id) will work very well in this situation. Yes, you will get multiple rows with duplicated container data. If your coding language supports an ORM (e.g. MyBatis or Hibernate for Java), the redundant duplication of container fields will be handled for you. The ORM will return a list of containers with each container having a list of IDs. ...


1

I would index geoid10 on both tables. If you can commit partial results, commit every 1000 to 10,000 updates. You should be able to do the updates in a stored procedure. This may reduce the overhead for lock contention.


1

Got an answer from Pivotal - I should have realised this, but parameterised queries are planned in ignorance of the parameter value, and partition elimination is part of the query plan, so we can't use variables for partition elimination. I am re-tooling our partition elimination to use views that are dynamically constructed with an IN list of dates. This ...



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