psql below 9.2 does not accept this URL-like syntax for options. The use of SSL can be driven by the sslmode=value option on the command line or the PGSSLMODE environment variable, but the default being prefer, SSL connections will be tried first automatically without specifying anything. Example with a conninfo string (updated for psql 8.4) … Read more
With postgresql-9.3 one can do this; SELECT g.id, array_remove(array_agg(CASE WHEN g.canonical=”Y” THEN g.users ELSE NULL END), NULL) canonical_users, array_remove(array_agg(CASE WHEN g.canonical=”N” THEN g.users ELSE NULL END), NULL) non_canonical_users FROM groups g GROUP BY g.id; Update: with postgresql-9.4; SELECT g.id, array_agg(g.users) FILTER (WHERE g.canonical=”Y”) canonical_users, array_agg(g.users) FILTER (WHERE g.canonical=”N”) non_canonical_users FROM groups g GROUP BY g.id; … Read more
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
uuid-ossp is a contrib module, so it isn’t loaded into the server by default. You must load it into your database to use it. For modern PostgreSQL versions (9.1 and newer) that’s easy: CREATE EXTENSION IF NOT EXISTS “uuid-ossp”; but for 9.0 and below you must instead run the SQL script to load the extension. … Read more