For your schema (_note the period after the schema name_):

    \dt my_schema.

Or:

    SET search_path TO my_schema, public;
    \dt

For all schemas:

    \dt *.



I have a REST Json API that returns a list &quot;logbooks&quot;. There are many types of logbooks that implement different but similar behavior. The server side implementation of this on the Database layer is a sort of Single Table Inheritance, so each JSON representation of a logbook contains its &quot;type&quot; :

    [
      {&quot;type&quot;: &quot;ULM&quot;, &quot;name&quot;: &quot;My uml logbook&quot;, ... , specific_uml_logbook_attr: ...},
      {&quot;type&quot;: &quot;Plane&quot;, &quot;name&quot;: &quot;My plane logbook&quot;, ... , specific_plane_logbook_attr: ...}
    ]


I would like to replicate this server model on the client side, so I have a base `Logbook` class and multiple logbook sub classes :

    class Logbook extends Backbone.Model

    class UmlLogbook extends Logbook

    class PlaneLogbook extends Logbook

    ...

My `Backbone.Collection` is a set of `Logbook` models that i use to query the JSON API :

    class LogbookCollection extends Backbone.Collection
      model: Logbook
      url: &quot;/api/logbooks&quot;

When I fetch the logbook collection, is there a way to cast each `Logbook` to its corresponding sub class (based on the JSON &quot;type&quot; attribute) ?


A Backbone.js Collection of multiple Model subclasses

I have a 200MB file that I want to give to a user via download. However, since we want the user to only download this file once, we are doing this:

    echo file_get_contents(&#39;http://some.secret.location.com/secretfolder/the_file.tar.gz&#39;);

to force a download. However, this means that the whole file has to be loaded in memory, which usually doesn&#39;t work. How can we stream this file to them, at some kb per chunk?

Streaming a large file using PHP

I have created a new schema in my PostgreSQl database with psql:

    CREATE SCHEMA my_schema;

But when I issue the `\dt` command, I see only the tables that are in the `public` schema. However, I can access all the tables in `my_schema` with `my_schema.table_name`.

How can I see all the tables from all the schemas in psql?

Why does PostgreSQL&#39;s \dt show only public schema tables?

I have created a new schema in my PostgreSQl database with psql:
<pre><code class="hljs language-ini">CREATE SCHEMA my_schema;
</code></pre>
But when I issue the <code>\dt</code> command, I see only the tables that are in the <code>public</code> schema. However, I can access all the tables in <code>my_schema</code> with <code>my_schema.table_name</code>.
How can I see all the tables from all the schemas in psql?

I installed PostgreSQL using Homebrew on Lion. It starts okay but wouldn&#39;t shutdown. I tried:

    $ #started with
    $ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start
    $ #tried stoping with
    $ pg_ctl -D /usr/local/var/postgres stop -m immediate
    waiting for server to shut down................................... failed
    pg_ctl: server does not shut down

---

I fixed this issue by deleting the Launch Agent:

    launchctl unload -w ~/Library/LaunchAgents/org.postgresql.postgres.plist
    rm ~/Library/LaunchAgents/org.postgresql.postgres.plist



PostgreSQL server wouldn&#39;t shutdown on Lion (Mac OS 10.7)

Is there any way to write case-insensitive queries in PostgreSQL, E.g. I want that following 3 queries return same result.

    SELECT id FROM groups where name=&#39;administrator&#39;

    SELECT id FROM groups where name=&#39;ADMINISTRATOR&#39;

    SELECT id FROM groups where name=&#39;Administrator&#39;


How to make &quot;case-insensitive&quot; query in Postgresql?

I need to create, manage and drop schemas on the fly. If I go to create a schema that already exists, I want to (conditionally, via external means) drop and recreate it as specified. How can I check for the existence of said schema on my Postgres 9 server?

Currently, I&#39;m doing this:

    select exists (select * from pg_catalog.pg_namespace where nspname = &#39;schemaname&#39;);

but I feel like there&#39;s probably another way... is this the &quot;proper&quot; way to query Postgres for the existence of a particular schema?

Postgresql: Check if Schema Exists?

I&#39;m writing a Django-ORM enchancement that attempts to cache models and postpone model saving until the end of the transaction. It&#39;s all almost done, however I came across an unexpected difficulty in SQL syntax.

I&#39;m not much of a DBA, but from what I understand, databases don&#39;t really work efficiently for many small queries. Few bigger queries are much better. For example it&#39;s better to use large batch inserts (say 100 rows at once) instead of 100 one-liners.

Now, from what I can see, SQL doesn&#39;t really supply any statement to perform a batch update on a table. The term seems to be _confusing_ so, I&#39;ll explain what I mean by that. I have an array of arbitrary data, each entry describing a single row in a table. I&#39;d like to update certain rows in the table, each using data from its corresponding entry in the array. The idea is very similar to a batch insert. 

For example: My table could have two columns `&quot;id&quot;` and `&quot;some_col&quot;`. Now the array describing the data for a batch update consists of three entries `(1, &#39;first updated&#39;)`, `(2, &#39;second updated&#39;)`, and `(3, &#39;third updated&#39;)`. Before the update the table contains rows: `(1, &#39;first&#39;)`, `(2, &#39;second&#39;)`, `(3, &#39;third&#39;)`.

I came accross this post:

https://stackoverflow.com/questions/1006969/why-are-batch-inserts-updates-faster-how-do-batch-updates-work/1007006#1007006

which seems to do what I want, however I can&#39;t really figure out the syntax at the end.

I could also delete all the rows that require updating and reinsert them using a batch insert, however I find it hard to believe that this would actually perform any better.

I work with PostgreSQL 8.4, so some stored procedures are also possible here. However as I plan to open source the project eventually, any more portable ideas or ways to do the same thing on a different RDBMS are most welcome.

**Follow up question:** How to do a batch &quot;insert-or-update&quot;/&quot;upsert&quot; statement?

**Test results**

I&#39;ve performed 100x times 10 insert operations spread over 4 different tables (so 1000 inserts in total). I tested on Django 1.3 with a PostgreSQL 8.4 backend.

These are the results:

  * All operations done through Django ORM - each pass **~2.45 seconds**,
  * The same operations, but done without Django ORM - each pass **~1.48 seconds**,
  * Only insert operations, without querying the database for sequence values **~0.72 seconds**,
  * Only insert operations, executed in blocks of 10 (100 blocks in total) **~0.19 seconds**,
  * Only insert operations, one big execution block **~0.13 seconds**.
  * Only insert operations, about 250 statements per block, **~0.12 seconds**.

Conclusion: execute as many operations as possible in a single connection.execute(). Django itself introduces a substantial overhead.

Disclaimer: I didn&#39;t introduce any indices apart from default primary key indices, so insert operations could possibly run faster because of that.

Bulk/batch update/upsert in PostgreSQL

I&#39;m trying to drop my database and create a new one through the command line.

I log in using `psql -U username` and then do a `\connect template1`, followed by a `DROP DATABASE databasename;`.

I get the error
&gt;database databasename is being accessed by other users

I shut down Apache and tried this again but I&#39;m still getting this error. Am I doing something wrong?


Content Type	Original Author	Original Content on Stackoverflow
Question	skanatek	View Question on Stackoverflow
Solution 1 - Postgresql	Alex Howansky	View Answer on Stackoverflow

Why does PostgreSQL's \dt show only public schema tables?

Postgresql Problem Overview

Postgresql Solutions

Solution 1 - Postgresql

Streaming a large file using PHP

A Backbone.js Collection of multiple Model subclasses

Attributions