In psql that would be

    \dx

See the [manual of psql](http://www.postgresql.org/docs/current/static/app-psql.html) for details.

Doing it in plain SQL it would be a select on [`pg_extension`](http://www.postgresql.org/docs/current/static/catalog-pg-extension.html): 

    SELECT * 
    FROM pg_extension;

Additionally if you want to know which extensions are available on your server: `SELECT * FROM pg_available_extensions`.

See [`pg_available_extensions`](https://www.postgresql.org/docs/current/view-pg-available-extensions.html) and [`pg_available_extension_versions`](https://www.postgresql.org/docs/current/view-pg-available-extension-versions.html).

This SQL query gives output similar to `\dx`:

    SELECT e.extname AS &quot;Name&quot;, e.extversion AS &quot;Version&quot;, n.nspname AS &quot;Schema&quot;, c.description AS &quot;Description&quot; 
    FROM pg_catalog.pg_extension e 
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace 
    LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid AND c.classoid = &#39;pg_catalog.pg_extension&#39;::pg_catalog.regclass 
    ORDER BY 1;

Thanks to https://blog.dbi-services.com/listing-the-extensions-available-in-postgresql/

Just a comment that whether you run as suggested above, in psql either

`\dx`

or

    select extname from pg_extension ;

Keep in mind that 

 1. Make sure you are connected to the right database. As your extensions are loaded database specific.
 2. Any extensions added to template1 database will by definition, appear on all databases.

I want to use AutoLayout to size and layout a view in a manner that is reminiscent of UIImageView&#39;s aspect-fit content mode.

I have a subview inside a container view in Interface Builder. The subview has some inherent aspect ratio which I wish to respect. The container view&#39;s size is unknown until runtime.

If the container view&#39;s aspect ratio is wider than the subview, then I want the subview&#39;s height to equal the parent view&#39;s height.

If the container view&#39;s aspect ratio is taller than the subview, then I want the subview&#39;s width to equal the parent view&#39;s width.

In either case I wish the subview to be centered horizontally and vertically within the container view.

Is there a way to achieve this using AutoLayout constraints in Xcode 6 or in previous version? Ideally using Interface Builder, but if not perhaps it is possible to define such constraints programmatically.

Emulating aspect-fit behaviour using AutoLayout constraints in Xcode 6

Just opened a file with Sublime Text (with Sublime Linter) and noticed a PEP8 formatting error that I&#39;d never seen before. Here&#39;s the text:

    urlpatterns = patterns(&#39;&#39;,
        url(r&#39;^$&#39;, listing, name=&#39;investment-listing&#39;),
    )

It&#39;s flagging the second argument, the line that starts `url(...)`

I was about to disable this check in ST2 but ***I&#39;d like to know what I&#39;m doing wrong*** before I ignore it. You never know, if it seems important I might even change my ways :)

What is PEP8&#39;s E128: continuation line under-indented for visual indent?

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](https://dba.stackexchange.com/questions/166283/where-can-i-find-a-list-of-the-extensions-for-postgresql)

Using psql how do I list extensions installed in a database?

<p>How do I list all extensions that are already installed in a database or schema from psql?</p>
<p>See also</p>
<ul>
<li><a href="https://dba.stackexchange.com/questions/166283/where-can-i-find-a-list-of-the-extensions-for-postgresql" target="_blank" rel="noopener noreferrer">Finding a list of available extensions that PostgreSQL ships with</a></li>
</ul>


I&#39;m working on the design for a RoR project for my company, and our development team has already run into a bit of a debate about the design, specifically the database.

We have a model called `Message` that needs to be persisted. It&#39;s a very, very small model with only three db columns other than the id, however there will likely be A LOT of these models when we go to production. We&#39;re looking at as much as 1,000,000 insertions per day. The models will only ever be searched by two foreign keys on them which can be indexed. As well, the models never have to be deleted, but we also don&#39;t have to keep them once they&#39;re about three months old.

So, what we&#39;re wondering is if implementing this table in Postgres will present a significant performance issue? Does anyone have experience with very large SQL databases to tell us whether or not this will be a problem? And if so, what alternative should we go with?

How big is too big for a PostgreSQL table?

It seems PostgreSQL does not allow to create a database table named &#39;user&#39;. But MySQL will allow to create such a table.

Is that because it is a key word? But Hibernate cannot identify any issue (even if we set the PostgreSQLDialect).

Cannot create a database table named &#39;user&#39; in PostgreSQL

Am trying to setup a postgresql table that has two foreign keys that point to the same primary key in another table. 

When I run the script I get the error 

&gt; sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Company.stakeholder - there are multiple foreign key paths linking the tables.  Specify the &#39;foreign_keys&#39; argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

That is the exact error in the [SQLAlchemy Documentation][1] yet when I replicate what they have offered as a solution the error doesn&#39;t go away. What could I be doing wrong?

    #The business case here is that a company can be a stakeholder in another company.
    class Company(Base):
        __tablename__ = &#39;company&#39;
        id = Column(Integer, primary_key=True)
        name = Column(String(50), nullable=False)
    	
    class Stakeholder(Base):
        __tablename__ = &#39;stakeholder&#39;
        id = Column(Integer, primary_key=True)
        company_id = Column(Integer, ForeignKey(&#39;company.id&#39;), nullable=False)
        stakeholder_id = Column(Integer, ForeignKey(&#39;company.id&#39;), nullable=False)
        company = relationship(&quot;Company&quot;, foreign_keys=&#39;company_id&#39;)
        stakeholder = relationship(&quot;Company&quot;, foreign_keys=&#39;stakeholder_id&#39;)

I have seen similar questions here but [some of the answers][2] recommend one uses a `primaryjoin` yet in the documentation it states that you don&#39;t need the `primaryjoin` in this situation.


  [1]: http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#handling-multiple-join-paths
  [2]: https://stackoverflow.com/a/9222924/1082673

SQLAlchemy multiple foreign keys in one mapped class to the same primary key

I&#39;m trying to make the fastest COALESCE() that accepts two or more arguments, and returns the first non-null AND non-empty (&quot;&quot;) value.

I&#39;m using this:

    CREATE OR REPLACE FUNCTION coalescenonempty(VARIADIC in_ordered_actual varchar[])
    RETURNS varchar AS $$
      SELECT i
      FROM (SELECT unnest($1) AS i) t
      WHERE i IS NOT NULL AND i &lt;&gt; &#39;&#39;
      LIMIT 1;
    $$ LANGUAGE sql;

It&#39;s pretty fast, but still nowhere as fast as COALESCE or CASE WHEN statements.

What do you think?

How to COALESCE for empty strings and NULL values?

In MySQL, you can terminate a `select` query with `\G` (as opposed to `\g`) to display the results vertically:

    select * from foo \G

    ***************
     id: 1
    bar: Hello
    ***************
     id: 2
    bar: World

How can one do the same thing for PostgreSQL using psql?

Display select results vertically in psql, as is done by MySQL&#39;s \G

I am using Postgres&#39; json data type but want to do a query/ordering with data that is nested within the json.

I want to order or query with .where on the json data type. For example, I want to query for users that have a follower count &gt; 500 or I want to order by follower or following count.

Thanks! 

Example:

    model User

    data: {
         &quot;photos&quot;=&gt;[
           {&quot;type&quot;=&gt;&quot;facebook&quot;, &quot;type_id&quot;=&gt;&quot;facebook&quot;, &quot;type_name&quot;=&gt;&quot;Facebook&quot;, &quot;url&quot;=&gt;&quot;facebook.com&quot;}
          ], 
         &quot;social_profiles&quot;=&gt;[
             {&quot;type&quot;=&gt;&quot;vimeo&quot;, &quot;type_id&quot;=&gt;&quot;vimeo&quot;, &quot;type_name&quot;=&gt;&quot;Vimeo&quot;, &quot;url&quot;=&gt;&quot;http://vimeo.com/&quot;, &quot;username&quot;=&gt;&quot;v&quot;, &quot;id&quot;=&gt;&quot;1&quot;},
             {&quot;bio&quot;=&gt;&quot;I am not a person, but a series of plants&quot;, &quot;followers&quot;=&gt;1500, &quot;following&quot;=&gt;240, &quot;type&quot;=&gt;&quot;twitter&quot;, &quot;type_id&quot;=&gt;&quot;twitter&quot;, &quot;type_name&quot;=&gt;&quot;Twitter&quot;, &quot;url&quot;=&gt;&quot;http://www.twitter.com/&quot;, &quot;username&quot;=&gt;&quot;123&quot;, &quot;id&quot;=&gt;&quot;123&quot;}
         ]
    }

Postgres JSON data type Rails query

On PostgreSQL 9.3.4, I have a JSON type column called &quot;person&quot; and the data stored in it is in the format `{dogs: [{breed: &lt;&gt;, name: &lt;&gt;}, {breed: &lt;&gt;, name: &lt;&gt;}]}`.  I want to retrieve the breed of dog at index 0.  Here are the two queries I ran:

**Doesn&#39;t work**

    db=&gt; select person-&gt;&#39;dogs&#39;-&gt;&gt;0-&gt;&#39;breed&#39; from people where id = 77;
    ERROR:  operator does not exist: text -&gt; unknown
    LINE 1: select person-&gt;&#39;dogs&#39;-&gt;&gt;0-&gt;&#39;bree...
                                     ^
    HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

**Works**

    select (person-&gt;&#39;dogs&#39;-&gt;&gt;0)::json-&gt;&#39;breed&#39; from es_config_app_solutiondraft where id = 77;
     ?column?
    -----------
     &quot;westie&quot;
    (1 row)

Why is the type casting necessary?  Isn&#39;t it inefficient?  Am I doing something wrong or is this necessary for postgres JSON support?

PostgreSQL Nested JSON Querying

I have two models, `Courier` and `Order`. 

I have the following query below:

    active_couriers = Courier.
      available_courier_status.
      where(:service_region_id =&gt; @service_region.id).
      includes(:orders)

This query works, however, it pulls in all orders. I want to limit the orders to only orders for the day. So I added the following query `where(&quot;orders.created_at &gt;= ?&quot;, Time.zone.now.beginning_of_day)`.

    active_couriers = Courier.
      available_courier_status.
      where(:service_region_id =&gt; @service_region.id).
      includes(:current_orders).
      includes(:orders).
      where(&quot;orders.created_at &gt;= ?&quot;, Time.zone.now.beginning_of_day)

This give me the error:

    PG::UndefinedTable: ERROR:  missing FROM-clause entry for table &quot;orders&quot;

What am I doing incorrectly here?


PG::UndefinedTable: ERROR: missing FROM-clause entry for table when using joins and where

Is there a way to show all the content inside a table by using psql command line interface?

I can use `\list` to show all the databases, `\d` to show all the tables, but how can I show all the data in a table?

Content Type	Original Author	Original Content on Stackoverflow
Question	ARV	View Question on Stackoverflow
Solution 1 - Postgresql	a_horse_with_no_name	View Answer on Stackoverflow
Solution 2 - Postgresql	Dave Glassborow	View Answer on Stackoverflow
Solution 3 - Postgresql	hit3k	View Answer on Stackoverflow
Solution 4 - Postgresql	Sumit S	View Answer on Stackoverflow

Using psql how do I list extensions installed in a database?

Postgresql Problem Overview

Postgresql Solutions

Solution 1 - Postgresql

Solution 2 - Postgresql

Solution 3 - Postgresql

Solution 4 - Postgresql

What is PEP8's E128: continuation line under-indented for visual indent?

Emulating aspect-fit behaviour using AutoLayout constraints in Xcode 6

Attributions