May 2, 2016 · 106 I came across a query in postgres here which uses the @> operator on earth objects. I've searched everywhere, but have come up empty on the meaning of this operator (and likely others like it, eg: @<, etc...). Is this documented somewhere in the postgres docs? I'm even more curious to understand what the operator does on earth objects.

How can I do such query in Postgres? IF (select count(*) from orders) &gt; 0 THEN DELETE from orders ELSE INSERT INTO orders values (1,2,3);

Nov 1, 2010 · In Postgres version 9.5 or higher you can use ON CONFLICT to avoid errors of contraints like @Arie mentioned above. To know more options related to this INSERT query refer to Postgres Docs.

Nov 30, 2010 · postgres=# REASSIGN OWNED BY old_name TO new_name; This is a global equivalent of ALTER DATABASE command provided in Frank's answer, but instead of updating a particular DB, it change ownership of all DBs owned by 'old_name'.

Nov 12, 2018 · I have PostgreSQL 11 and PGadmin 4 installed on windows. Currently I'm connected to a AWS server which hosts all of my data. I want to create a local server (localhost) as a testing environment wh...

May 4, 2018 · How do I list all extensions that are already installed in a database or schema from psql? See also Finding a list of available extensions that PostgreSQL ships with

Apr 8, 2010 · 277 I'm coming to Postgres from Oracle and looking for a way to find the table and index size in terms of bytes/MB/GB/etc, or even better the size for all tables. In Oracle I had a nasty long query that looked at user_lobs and user_segments to give back an answer.

One thing non-intuitive thing is that for postgres, position is 1 based, not 0 based. For example select position ('a' in 'abc') returns 1. And if there is no match it returns 0.

May 3, 2013 · I have been facing a strange scenario when comparing dates in postgresql (version 9.2.4 in windows). I have a column in my table say update_date with type timestamp without timezone. Client can sea...

Mar 11, 2011 · Note that when the postgres command line tool, PSQL is used for executing this query, the total number of connections is the result of this query - 1 since the psql connection made is also included as a connection