I’m pretty sure you can’t simply add on delete cascade to an existing foreign key constraint. You have to drop the constraint first, then add the correct version. In standard SQL, I believe the easiest way to do this is to start a transaction, drop the foreign key, add a foreign key with on delete … Read more
(Moving my answer from Using in-memory PostgreSQL and generalizing it): You can’t run Pg in-process, in-memory I can’t figure out how to run in-memory Postgres database for testing. Is it possible? No, it is not possible. PostgreSQL is implemented in C and compiled to platform code. Unlike H2 or Derby you can’t just load the … Read more
This article explains that your sequence might be out of sync and that you have to manually bring it back in sync. An excerpt from the article in case the URL changes: If you get this message when trying to insert data into a PostgreSQL database: ERROR: duplicate key violates unique constraint That likely means … Read more
Here is an example I used to connect node.js to my Postgres database. The interface in node.js that I used can be found here https://github.com/brianc/node-postgres var pg = require(‘pg’); var conString = “postgres://YourUserName:YourPassword@localhost:5432/YourDatabase”; var client = new pg.Client(conString); client.connect(); //queries are queued and executed one after another once the connection becomes available var x = … Read more
It sounds like you have a connection leak in your application because it fails to close pooled connections. You aren’t having issues just with <idle> in transaction sessions, but with too many connections overall. Killing connections is not the right answer for that, but it’s an OK-ish temporary workaround. Rather than re-starting PostgreSQL to boot … Read more
To see where the data directory is, use this query. show data_directory; To see all the run-time parameters, use show all; You can create tablespaces to store database objects in other parts of the filesystem. To see tablespaces, which might not be in that data directory, use this query. SELECT *, pg_tablespace_location(oid) FROM pg_tablespace;
What you ask for is impossible. SQL is a strictly typed language. PostgreSQL functions need to declare a return type (RETURNS ..) at the time of creation. A limited way around this is with polymorphic functions. If you can provide the return type at the time of the function call. But that’s not evident from … Read more
Adapted this from a previous answer. This kind of stuff happens often when applications want a tabbing-order for variables (read: records in an EAV model) , which could also be (part of) an alternate key. the priority field needs to be kept consecutive. [this is the tabbing-order] on INSERT: all records with priority >= the … Read more
CHECK constraints cannot currently reference other tables. The manual: Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row. One way is to use a trigger like demonstrated by @Wolph. A clean solution without triggers: add redundant columns and include them in FOREIGN KEY constraints, which are the … Read more