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

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

The basic misunderstanding is that you assume variable substitution for an SQL utility command like ALTER TABLE, which is not implemented. Equally not possible to pass values for utility commands to EXECUTE with the USING clause in plpgsql. Detailed explanation for both in this related answer on SO: Error when setting n_distinct using a plpgsql variable ...


1

There are some. The most reliable I've seen so far (at least when the development process is also have to be considered) is having diffs. Basically, all DB objects (tables, types, functions, etc.) are defined one by one in files, which are then under version control. When you want to change something, you write a diff (ALTER TABLE, for example) and change ...


1

If the other database has established a connection to the database to which you are connected and has issued a LISTEN, then you send a NOTIFY to it the same way you would send one to anything else using the NOTIFY/LISTEN interface. If the other database has not volunteered to LISTEN, then NOTIFY is not the right tool for the job. You could use dblink or ...


1

No other clean up should be required. If you are really worried, you can watch your database size with: select pg_database_size('your_database'); To make sure it shrinks by about the amount you expected. It could take until the end of the next checkpoint to fully clean up. If you were dropping tens of thousands of small tables is where you have you to ...


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

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


1

I accidentally deleted my passwd file under /etc directory and after that postgresql service could start, when I run command systemctl status postgresql.myversion. the error was no passwd entry found for user postgres I decided to reinstall postgresql. Because I used enterpricedb installer sudo apt-get --purge remove postgresql* did not work for me. ...



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