    postgres=# UPDATE pg_database SET datistemplate=&#39;false&#39; WHERE datname=&#39;template_postgis&#39;;
    UPDATE 1
    postgres=# DROP DATABASE template_postgis;
    DROP DATABASE
    postgres=# 

You can use the alter database command. Much simpler and safer than upsetting metadata.

    postgres=# create database tempDB is_template true;
    CREATE DATABASE
    postgres=# drop database tempDB;
    ERROR:  cannot drop a template database
    postgres=# alter database tempDB is_template false;
    ALTER DATABASE
    postgres=# drop database tempDB;
    DROP DATABASE
    postgres=# 


[Documentation][1]


  [1]: https://www.postgresql.org/docs/current/static/sql-alterdatabase.html

```
update pg_database set datistemplate=false where datname=&#39;template0&#39;;
update pg_database set datistemplate=false where datname=&#39;template1&#39;;

....

Creating script to analyze new cluster                      ok
Creating script to delete old cluster                       ok
Checking for hash indexes                                   ok

Upgrade Complete
----------------
```

 I am using amazon ec2 ubuntu 11.04 server

     sudo pip install python-snappy 

also I tried to downloaded package and entered &quot;sudo python setup.py install&quot;

I got the error:

      running build
      running build_ext
      building &#39;snappy&#39; extension
      gcc -pthread -fno-strict-aliasing -DNDEBUG -g -fwrapv -O2 -Wall -Wstrict-prototypes -fPIC -I/usr/include/python2.7 -c snappymodule.cc -o build/temp.linux-x86_64-2.7/snappymodule.o
      cc1plus: warning: command line option ‘-Wstrict-prototypes’ is valid for Ada/C/ObjC but not for C++ [enabled by default]
      snappymodule.cc:31:22: fatal error: snappy-c.h: No such file or directory
      compilation terminated.
      error: command &#39;gcc&#39; failed with exit status 1


How i could get rid of this error?

source: [https://github.com/andrix/python-snappy][1]


  [1]: https://github.com/andrix/python-snappy

Error installing python-snappy: snappy-c.h: No such file or directory

How do I go about getting the details of the certificate an apk was signed with. I have a bunch of apks signed with different certificates and I am trying to group them based on their certificate. 

I can get the certificate expiry details using the jarsigner and complete my task but I was curious if I can get any more details or extract the public key ( I believe it stored in META-INF/cert.RSA but it&#39;s not readable )

Getting certificate details from an apk

    postgres=# DROP DATABASE template_postgis;
    ERROR:  cannot drop a template database

http://www.postgresql.org/docs/9.1/static/manage-ag-templatedbs.html makes it seem like if I set `template_postgis.datistemplate = false`, I&#39;ll be able to drop it, but I don&#39;t know how to set that.

How does one drop a template database from PostgreSQL?

<pre><code class="hljs language-makefile">postgres=# DROP DATABASE template_postgis;
ERROR: cannot drop a template database
</code></pre>
<a href="http://www.postgresql.org/docs/9.1/static/manage-ag-templatedbs.html" target="_blank" rel="noopener noreferrer">http://www.postgresql.org/docs/9.1/static/manage-ag-templatedbs.html</a> makes it seem like if I set <code>template_postgis.datistemplate = false</code>, I'll be able to drop it, but I don't know how to set that.

I&#39;d like to get the list of days between the two dates (including them) in a **PostgreSQL database**. For example, if I had:

 - start date: 29 june 2012
 - end date: 3 july 2012

then the result should be:

    29 june 2012
    30 june 2012 
    1 july 2012 
    2 july 2012 
    3 july 2012

What would be the best way of doing this in PostgreSQL?

Thanks.


Getting date list in a range in PostgreSQL

I have a rather complicated query on my PostgreSQL database spanning 4 tables via a series of nested subqueries. However, despite the slightly tricky looking appearance and setup, ultimately it will return two columns (from the same table, if that helps the situation) based on that matching of two external parameters (two strings need to match with fields in different tables). I&#39;m fairly new to database design in PostgreSQL, so I know that this seemingly magical thing called Views exist, and that seems like it could help me here, but perhaps not.

Is there some way I can move my complex query inside a view and somehow just pass it the two values I need to match? That would greatly simplify my code on the front-end (by shifting the complexities to the database structure). I can create a view that wraps my static example query, and that works just fine, however that only works for one pair of string values. I need to be able to use it with a variety of different values.

Thus my question is: is it possible to pass parameters into an otherwise static View and have it become &quot;dynamic&quot;? Or perhaps a View is not the right way to approach it. If there&#39;s something else that would work better, I&#39;m all ears!

**Edit: ** As requested in comments, here&#39;s my query as it stands now:

    SELECT   param_label, param_graphics_label
      FROM   parameters
     WHERE   param_id IN 
             (SELECT param_id 
                FROM parameter_links
               WHERE region_id = 
                     (SELECT region_id
                        FROM regions
                       WHERE region_label = &#39;%PARAMETER 1%&#39; AND model_id =
                             (SELECT model_id FROM models WHERE model_label = &#39;%PARAMETER 2%&#39;)
                     )
             ) AND active = &#39;TRUE&#39;
    ORDER BY param_graphics_label;

Parameters are set off by percent symbols above.

Pass In &quot;WHERE&quot; parameters to PostgreSQL View?

    Jupitor$ bundle exec rake db:create db:migrate
    APP_development already exists
    rake aborted!
    PG::Error: ERROR:  invalid value for parameter &quot;TimeZone&quot;: &quot;UTC&quot;
    : SET time zone &#39;UTC&#39;

I keep getting this error when trying to migrate to my postgres database.
help would be much appreciated!





Postgres error &quot;invalid value for parameter &quot;TimeZone&quot;: &quot;UTC&quot;&quot;

We&#39;re using `Postgresql 9.1.4` as our db server.  I&#39;ve been trying to speed up my test suite so I&#39;ve stared profiling the db a bit to see exactly what&#39;s going on.  We are using [database_cleaner](https://github.com/bmabey/database_cleaner) to truncate tables at the end of tests.  YES I know transactions are faster, I can&#39;t use them in certain circumstances so I&#39;m not concerned with that.

What I AM concerned with, is why TRUNCATION takes so long (longer than using DELETE) and why it takes EVEN LONGER on my CI server.

Right now, locally (on a Macbook Air) a full test suite takes 28 minutes.  Tailing the logs, each time we truncate tables... ie:

    TRUNCATE TABLE table1, table2  -- ... etc

it takes over 1 second to perform the truncation.  Tailing the logs on our CI server (Ubuntu 10.04 LTS), take takes a full 8 seconds to truncate the tables and a build takes 84 minutes.

When I switched over to the `:deletion` strategy, my local build took 20 minutes and the CI server went down to 44 minutes.  This is a *significant* difference and I&#39;m really blown away as to why this might be.  I&#39;ve [tuned](http://thebuild.com/presentations/not-your-job.pdf) [the](http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server) DB on the CI server, it has 16gb system ram, 4gb shared_buffers... and an SSD.  All the good stuff.  How is it possible:

**a.** that it&#39;s SO much slower than my Macbook Air with 2gb of ram  
**b.** that TRUNCATION is so much slower than DELETE when the [postgresql docs][1] [state explicitly][2] that it should be much faster.

Any thoughts?


  [1]: http://www.postgresql.org/docs/9.1/static/sql-truncate.html
  [2]: http://www.postgresql.org/docs/9.1/static/sql-truncate.html#AEN75996

Postgresql Truncation speed

I&#39;m building a queuing mechanism of sorts. There are rows of data that need processing, and a status flag. I&#39;m using an `update .. returning` clause to manage it:

    UPDATE stuff
    SET computed = &#39;working&#39;
    WHERE id = (SELECT id from STUFF WHERE computed IS NULL LIMIT 1)
    RETURNING * 

Is the nested select part the same lock as the update, or do I have a race condition here? If so, does the inner select need to be a `select for update`?

Atomic UPDATE .. SELECT in Postgres

Recently [I went into trouble][1] trying to use hstore with Django. I installed hstore this way:

&lt;!-- language: lang-sql --&gt;

    $ sudo -u postgres psql
    postgres=# CREATE EXTENSION hstore;
    WARNING:  =&gt; is deprecated as an operator name
    DETAIL:  This name may be disallowed altogether in future versions of PostgreSQL.
    CREATE EXTENSION
    postgres=# \dx
                               List of installed extensions
      Name   | Version |   Schema   |                   Description                    
    ---------+---------+------------+--------------------------------------------------
     hstore  | 1.0     | public     | data type for storing sets of (key, value) pairs
     plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
    (2 rows)
    
And naively thought that my new databases would include hstore. That ain&#39;t the case:

&lt;!-- language: lang-sql --&gt;

    $ createdb dbtest
    $ psql -d dbtest -c &#39;\dx&#39;
                     List of installed extensions
      Name   | Version |   Schema   |         Description          
    ---------+---------+------------+------------------------------
     plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
    (1 row)

Is there a way to automatically have hstore in a newly created database ?

  [1]: https://stackoverflow.com/a/11581961/343834

How to create a new database with the hstore extension already installed?

PostgreSQL field type for unix timestamp : 

+ to store it as unix time stamp 
+ to retrieve it as a unix timestamp as well.

Have been going through [Date/Time Types &lt;sup&gt;postgreSQL V 9.1&lt;/sup&gt;](http://www.postgresql.org/docs/9.1/static/datatype-datetime.html).
____
+ Is integer the best way to go!? (this is what I had done when I was using MySQL. Had used `int(10)`)


PostgreSQL field type for unix timestamp?

How do I invoke a postgresql sequence while inserting new row into a table?

I want to do something like this:

    insert into biz_term(
      biz_term_id, 
      biz_term_name, 
      ) 
    values(SELECT nextval(idsequence)&#39;,
    &#39;temp&#39;
    );

I want to do it because when I am trying to insert new record into `biz_term` table then sequence `idsequence` is not getting invoked directly.  How to invoke it?

How to invoke sequence while inserting new record into postgresql table?

I&#39;m writing a function in PL/pgSQL, and I&#39;m looking for the simplest way to check if a row exists.  
Right now I&#39;m SELECTing an `integer` into a `boolean`, which doesn&#39;t really work. I&#39;m not experienced with PL/pgSQL enough yet to know the best way of doing this.

Here&#39;s part of my function:

    DECLARE person_exists boolean;
    BEGIN

    person_exists := FALSE;

    SELECT &quot;person_id&quot; INTO person_exists
      FROM &quot;people&quot; p
    WHERE p.person_id = my_person_id
    LIMIT 1;

    IF person_exists THEN
      -- Do something
    END IF;

    END; $$ LANGUAGE plpgsql;

*Update* - I&#39;m doing something like this for now:

    DECLARE person_exists integer;
    BEGIN

    person_exists := 0;

    SELECT count(&quot;person_id&quot;) INTO person_exists
      FROM &quot;people&quot; p
    WHERE p.person_id = my_person_id
    LIMIT 1;

    IF person_exists &lt; 1 THEN
      -- Do something
    END IF;

PL/pgSQL checking if a row exists

How to assign the result of a query to a variable in PL/pgSQL, the procedural language of PostgreSQL?

I have a function:
    
    CREATE OR REPLACE FUNCTION test(x numeric)
    RETURNS character varying AS
    $BODY$
    DECLARE
    name   character varying(255);
    begin
     name =&#39;SELECT name FROM test_table where id=&#39;||x;
      
     if(name=&#39;test&#39;)then
      --do somthing
     else
      --do the else part
     end if;
    end;
    return -- return my process result here
    $BODY$
    LANGUAGE plpgsql VOLATILE

In the above function I need to store the result of this query:

    &#39;SELECT name FROM test_table where id=&#39;||x;

to the variable `name`.

How to process this?


Content Type	Original Author	Original Content on Stackoverflow
Question	dbkaplun	View Question on Stackoverflow
Solution 1 - Postgresql	dbkaplun	View Answer on Stackoverflow
Solution 2 - Postgresql	VynlJunkie	View Answer on Stackoverflow
Solution 3 - Postgresql	dba	View Answer on Stackoverflow

How does one drop a template database from PostgreSQL?

Postgresql Problem Overview

Postgresql Solutions

Solution 1 - Postgresql

Solution 2 - Postgresql

Solution 3 - Postgresql

Getting certificate details from an apk

Error installing python-snappy: snappy-c.h: No such file or directory

Attributions