    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?

I would like to change the name of an item in an enum type in PostgreSQL 9.1.5.  

Here is the type&#39;s create stmt:

    CREATE TYPE import_action AS ENUM
    (&#39;Ignored&#39;,
    &#39;Inserted&#39;,
    &#39;Updated&#39;,
    &#39;Task created&#39;);

I just want to change &#39;Task created&#39; to &#39;Aborted&#39;.  It seems like from the [documentation][1], that the following should work:

    ALTER TYPE import_action
    RENAME ATTRIBUTE &quot;Task created&quot; TO &quot;Aborted&quot;; 

However, I get a msg:

    ********** Error **********
    
    ERROR: relation &quot;import_action&quot; does not exist
    SQL state: 42P01

But, it clearly does exist.

The type is currently being used by more than one table. 

I&#39;m being to think that there must not be a way to do this.  I&#39;ve tried the dialog for the type in pgAdminIII, but there is no way that I can see to rename the it there.  (So, either a strong hint that I can&#39;t do it, or - I&#39;m hoping - a small oversight be the developer that created that dialog)

If I can&#39;t do this in one statment?  Then what do I need to do?  Will I have to write a script to add the item, update all of the records to new value, then drop the old item?  Will that even work?

It&#39;s seems like this should be a simple thing.  As I understand it, the records are just storing a reference to the type and item.  I don&#39;t think they are actually store the text value that I have given it.  But, maybe I&#39;m wrong here as well.


  [1]: http://www.postgresql.org/docs/9.1/static/sql-altertype.html

Rename enum item in PostgreSQL

Is there any shorter way to look for multiple matches:

     SELECT * 
     from table 
     WHERE column LIKE &quot;AAA%&quot; 
        OR column LIKE &quot;BBB%&quot; 
        OR column LIKE &quot;CCC%&quot;

This questions applies to PostgreSQL 9.1, but if there is a generic solution it would be even better.

How to use SQL LIKE condition with multiple values in PostgreSQL?

I have a ton of postgresql dump files I need to peruse through for data. Do I have to install Postgresql and &quot;recover&quot; each one of them into new databases one by one? Or I&#39;m hoping there&#39;s a postgresql client that can simply open them up and I can peek at the data, maybe even run a simple SQL query?

The dump files are all from a Postgresql v9.1.9 server.

Or maybe there&#39;s a tool that can easily make a database &quot;connection&quot; to the dump files?

***UPDATE***: These are **not text files**. They are **binary**. They come from Heroku&#39;s backup mechanism, this is [what Heroku says about how they create their backups][1]:

&gt; PG Backups uses the native pg_dump PostgreSQL tool to create its
&gt; backup files, making it trivial to export to other PostgreSQL
&gt; installations.


  [1]: https://devcenter.heroku.com/articles/heroku-postgres-import-export

Easy way to view postgresql dump files?

I want to export my database as a .sql file. 
Can someone help me? The solutions I have found don&#39;t work.
A detailed description please.
On Windows 7.

Postgresql Database export to .sql file

I have two questions about using SELECT … FOR UPDATE row-level locking in a Postgres function:

* Does it matter which columns I select? Do they have any relation to what data I need to lock and then update?

        SELECT * FROM table WHERE x=y FOR UPDATE;

   vs

        SELECT 1 FROM table WHERE x=y FOR UPDATE;

* I can&#39;t do a select in a function without saving the data somewhere, so I save to a dummy variable. This seems hacky; is it the right way to do things?

Here is my function:

&lt;!-- language: lang-sql --&gt;

    CREATE OR REPLACE FUNCTION update_message(v_1 INTEGER, v_timestamp INTEGER, v_version INTEGER)
    RETURNS void AS $$
    DECLARE
    	v_timestamp_conv TIMESTAMP;
    	dummy INTEGER;
    BEGIN
    	SELECT timestamp &#39;epoch&#39; + v_timestamp * interval &#39;1 second&#39; INTO v_timestamp_conv;
    	SELECT 1 INTO dummy FROM my_table WHERE userid=v_1 LIMIT 1 FOR UPDATE;
    	UPDATE my_table SET (timestamp) = (v_timestamp_conv) WHERE userid=v_1 AND version &lt; v_version;
    END;
    $$  LANGUAGE plpgsql;

Postgres SELECT ... FOR UPDATE in functions

I either forgot or mistyped (during the installation) the password to the default user of Postgres. I can&#39;t seem to be able to run it and I get the following error:

    psql: FATAL:  password authentication failed for user &quot;hisham&quot;
    hisham-agil: hisham$ psql 

Is there anyway to reset the password or how do I create a new user with superuser privileges? 

I am new to Postgres and just installed it for the first time. I am trying to use it with Rails and I am running Mac OS X Lion.


I forgot the password I entered during postgres installation

I&#39;m getting the following error when running a query on a PostgreSQL db in standby mode. The query that causes the error works fine for 1 month but when you query for more than 1 month an error results.

    ERROR: canceling statement due to conflict with recovery
    Detail: User query might have needed to see row versions that must be removed

Any suggestions on how to resolve? Thanks

PostgreSQL ERROR: canceling statement due to conflict with recovery

I have a small table and a certain field contains the type &quot;**character varying**&quot;. I&#39;m trying to change it to &quot;**Integer**&quot; but it gives an error that casting is not possible.

Is there a way around this or should I just create another table and bring the records into it using a query.

The field contains only integer values.

Change type of varchar field to integer: &quot;cannot be cast automatically to type integer&quot;

Question is simple. How to add column `x` to table `y`, but only when `x` column doesn&#39;t exist  ? I found only solution [here][1] how to check if column exists.

    SELECT column_name 
    FROM information_schema.columns 
    WHERE table_name=&#39;x&#39; and column_name=&#39;y&#39;;

  [1]: https://stackoverflow.com/questions/9991043/how-can-i-test-if-a-column-exists-in-a-table-using-an-sql-statement

How to add column if not exists on PostgreSQL?

How do I convert an integer to string as part of a PostgreSQL query?

So, for example, I need:

    SELECT * FROM table WHERE &lt;some integer&gt; = &#39;string of numbers&#39;

where `&lt;some integer&gt;` can be anywhere from 1 to 15 digits long.

How do I convert an integer to string as part of a PostgreSQL query?

Does anyone know if it&#39;s even possible (and how, if yes) to query a database server setting in PostgreSQL (9.1)?

I need to check the `max_connections` (maximum number of open db connections) setting.

Query a parameter (postgresql.conf setting) like &quot;max_connections&quot;

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?


Store query result in a variable using in PL/pgSQL

If I use `array_agg` to collect names, I get my names separated by commas, but in case there is a `null` value, that null is also taken as a name in the aggregate. For example :

    SELECT g.id,
           array_agg(CASE WHEN g.canonical = &#39;Y&#39; THEN g.users ELSE NULL END) canonical_users,
           array_agg(CASE WHEN g.canonical = &#39;N&#39; THEN g.users ELSE NULL END) non_canonical_users
    FROM groups g
    GROUP BY g.id;

it returns `,Larry,Phil` instead of just `Larry,Phil` (in my 9.1.2, it shows `NULL,Larry,Phil`).

Instead, if I use `string_agg()`, it shows me only the names (without empty commas or nulls).

The problem is that I have `Postgres 8.4` installed on the server, and `string_agg()` doesn&#39;t work there. **Is there any way to make array_agg work similar to string_agg() ?**


how to exclude null values in array_agg like in string_agg using postgres?

I have a table with not null column, How to set a null value in this column as default?

I mean, I want to do something like this:

    postgres=# ALTER TABLE person ALTER COLUMN phone SET NULL;

but it shows:

    postgres=# ALTER TABLE person ALTER COLUMN phone SET NULL;
    ERROR:  syntax error at or near &quot;NULL&quot;
    LINE 1: ALTER TABLE person ALTER COLUMN phone SET NULL;



ALTER TABLE, set null in not null column, PostgreSQL 9.1

I&#39;m using PostgreSQL 9.1. I have the column name of a table. Is it possible to find the table(s) that has/have this column? If so, how?

How to find a table having a specific column in postgresql

I have a query like this that nicely generates a series of dates between 2 given dates:

    select date &#39;2004-03-07&#39; + j - i as AllDate 
    from generate_series(0, extract(doy from date &#39;2004-03-07&#39;)::int - 1) as i,
         generate_series(0, extract(doy from date &#39;2004-08-16&#39;)::int - 1) as j

It generates 162 dates between `2004-03-07` and `2004-08-16` and this what I want. The problem with this code is that it wouldn&#39;t give the right answer when the two dates are from different years, for example when I try `2007-02-01` and `2008-04-01`.

Is there a better solution?


Generating time series between two dates in PostgreSQL

When I try to run bundle (bundle install), I all the time get


    Installing pg (0.13.2) with native extensions 
    Gem::Installer::ExtensionBuildError: ERROR: Failed to build gem native extension.
    
            /Users/ryan/.rvm/rubies/ruby-1.9.2-p290/bin/ruby extconf.rb 
    checking for pg_config... no
    No pg_config... trying anyway. If building fails, please try again with
     --with-pg-config=/path/to/pg_config
    checking for libpq-fe.h... no
    Can&#39;t find the &#39;libpq-fe.h header
    *** extconf.rb failed ***
    Could not create Makefile due to some reason, probably lack of
    necessary libraries and/or headers.  Check the mkmf.log file for more
    details.  You may need configuration options.
    
    Provided configuration options:
    	--with-opt-dir
    	--without-opt-dir
    	--with-opt-include
    	--without-opt-include=${opt-dir}/include
    	--with-opt-lib
    	--without-opt-lib=${opt-dir}/lib
    	--with-make-prog
    	--without-make-prog
    	--srcdir=.
    	--curdir
    	--ruby=/Users/ryan/.rvm/rubies/ruby-1.9.2-p290/bin/ruby
    	--with-pg
    	--without-pg
    	--with-pg-dir
    	--without-pg-dir
    	--with-pg-include
    	--without-pg-include=${pg-dir}/include
    	--with-pg-lib
    	--without-pg-lib=${pg-dir}/lib
    	--with-pg-config
    	--without-pg-config
    	--with-pg_config
    	--without-pg_config
    
    
    Gem files will remain installed in /Users/ryan/.rvm/gems/ruby-1.9.2-p290/gems/pg-0.13.2 for inspection.
    Results logged to /Users/ryan/.rvm/gems/ruby-1.9.2-p290/gems/pg-0.13.2/ext/gem_make.out
    An error occured while installing pg (0.13.2), and Bundler cannot continue.
    Make sure that `gem install pg -v &#39;0.13.2&#39;` succeeds before bundling.

I use Mac OS X 10.6, the version of installed PostgreSQL is 9.1. I found the problem is in the **libpq-dev**, how could I install/fix that?

Rails 3 - can&#39;t install pg gem

I have been migrating a MySQL db to Pg (9.1), and have been emulating MySQL ENUM data types by creating a new data type in Pg, and then using that as the column definition. My question -- could I, and would it be better to, use a CHECK CONSTRAINT instead? The MySQL ENUM types are implemented to enforce specific values entries in the rows. Could that be done with a CHECK CONSTRAINT? and, if yes, would it be better (or worse)?



Postgres ENUM data type or CHECK CONSTRAINT?

I&#39;m using PostgreSql version : 

    postgres=# select version();
                               version
    -------------------------------------------------------------
     PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 64-bit
    (1 row)


i had connected to a database from `postgres=#` to `newdb=#`....
Now i&#39;m in `newdb=#` Database i want to disconnect it and go back to `postgres=#` database ....

How to do this ?


I have tried with `disconnect newdb;`

but its giving erroe as::

    postgres=# create database newdb;
    CREATE DATABASE
    postgres=# \c newdb;
    WARNING: Console code page (437) differs from Windows code page (1252)
             8-bit characters might not work correctly. See psql reference
             page &quot;Notes for Windows users&quot; for details.
    You are now connected to database &quot;newdb&quot; as user &quot;postgres&quot;.
    newdb=# disconnect newdb;
    ERROR:  syntax error at or near &quot;disconnect&quot;
    LINE 1: disconnect newdb;
            ^
    newdb=#


it isnt working is there any other way to do this or am i wrong in anything!!

How to Disconnect from a database and go back to the default database in PostgreSQL?

Postgres 9.1 database contains tables yksus1 .. ykssu9 in public schema. pgAdmin shows those definitions as in code below.
How to move  those tables to firma1 schema ?

Other tables in firma1 schema have foreign key references to those table primay keys. Foreign key references to those tables are only from tables in firma1 schema.

Some of those tables contain data.
If tables is moved to firma1 schema, foreign key references shouuld also be updated to firma1.yksusn tables.
Table structures cannot changed.

It looks like primary key sequences are already in firma1 schema so those should not moved.
Version string	PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit

    CREATE TABLE yksus1
    (
      yksus character(10) NOT NULL DEFAULT ((nextval(&#39;firma1.yksus1_yksus_seq&#39;::regclass))::text || &#39;_&#39;::text),
      veebis ebool,
      nimetus character(70),
      &quot;timestamp&quot; character(14) DEFAULT to_char(now(), &#39;YYYYMMDDHH24MISS&#39;::text),
      username character(10) DEFAULT &quot;current_user&quot;(),
      klient character(40),
      superinden character(20),
      telefon character(10),
      aadress character(50),
      tlnr character(15),
      rus character(60),
      CONSTRAINT yksus1_pkey PRIMARY KEY (yksus)
    );
    ALTER TABLE yksus1
      OWNER TO mydb_owner;
        
    CREATE TRIGGER yksus1_trig
      BEFORE INSERT OR UPDATE OR DELETE
      ON yksus1
      FOR EACH STATEMENT
      EXECUTE PROCEDURE setlastchange();


other tables are similar:


    CREATE TABLE yksus2
    (
      yksus character(10) NOT NULL DEFAULT ((nextval(&#39;firma1.yksus2_yksus_seq&#39;::regclass))::text || &#39;_&#39;::text),
      nimetus character(70),
      &quot;timestamp&quot; character(14) DEFAULT to_char(now(), &#39;YYYYMMDDHH24MISS&#39;::text),
      osakond character(10),
      username character(10) DEFAULT &quot;current_user&quot;(),
      klient character(40),
      superinden character(20),
      telefon character(10),
      aadress character(50),
      tlnr character(15),
      rus character(60),
      CONSTRAINT yksus2_pkey PRIMARY KEY (yksus),
      CONSTRAINT yksus2_osakond_fkey FOREIGN KEY (osakond)
          REFERENCES yksus2 (yksus) MATCH SIMPLE
          ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE
    );
    ALTER TABLE yksus2
      OWNER TO mydb_owner;
    
    CREATE TRIGGER yksus2_trig
      BEFORE INSERT OR UPDATE OR DELETE
      ON yksus2
      FOR EACH STATEMENT
      EXECUTE PROCEDURE setlastchange();

how to move tables from public to other schema in Postgres

I have read other posts, when searching, an answer to this question.

I am using PostgreSQL 9.1, and created extension &#39;citext&#39; using `CREATE EXTENSION citext`, but when I try to create any columns of type &#39;citext&#39;, it throws this error

&gt; ERROR:  type &quot;citext&quot; does not exist 

I researched but did not find any concrete answers? Any idea why?

Postgresql: ERROR: type &quot;citext&quot; does not exist

What command or short key can I use to exit the PostgreSQL command line utility `psql`?


How to exit from PostgreSQL command line utility: psql

When I do a `\dt` in psql I only get a listing of tables in the current schema (`public` by default).

How can I get a list of all tables in all schemas or a particular schema?

List tables in a PostgreSQL schema

**Firstly, if you&#39;re not using 9.1+, please [refer to this question](https://stackoverflow.com/q/1564056/124486).**

How do I install an extension to PostgreSQL 9.1?

How do I import modules or install extensions in PostgreSQL 9.1+?

In one of my select statements I&#39;ve got the following error:

    ERROR:  failed to find conversion function from unknown to text
    ********** Error **********
    ERROR: failed to find conversion function from unknown to text
    SQL state: XX000

This was easy to fix using `cast`, but I do not fully understand why it happened. I will ilustrate my confusion with two simple statements.

This one is OK:

    select &#39;text&#39;
    union all
    select &#39;text&#39;;

This will return error:

    with t as (select &#39;text&#39;)    
    select * from t
    union all
    select &#39;text&#39;

I know I can fix it easily:

    with t as (select &#39;text&#39;::text)    
    select * from t
    union all
    select &#39;text&#39;

Why does the conversion fail in the second example? Is there some logic I do not understand or this will be fixed in future version of PostgreSQL?

PostgreSQL 9.1.9

The same behavior on PostgreSQL 9.2.4 ([SQL Fiddle][1])


  [1]: http://sqlfiddle.com/#!12/d41d8/1262

Failed to find conversion function from unknown to text

I have a `Postgres db 9.1` running on `AWS EC2`, with `ubuntu 12.04`.

I messed a lot with the instance (i.e installed all kinds of postgres X.X before i settled on 9.1).

Now after a month working on that db, I discovered that if I restart my instance `postgres` doesn&#39;t load correctly, its status says &quot;Running clusters&quot;. this will last forever until I


    sudo service postgresql restart

from terminal, and then it works again.

How do I add this line, to ubuntu startup so that each time it loads, it will restart this service, and hopefully solve my problem?

Also any other solution which might solve this.

postgres, ubuntu how to restart service on startup? get stuck on clustering after instance reboot

I have a Postgres schema which looks like:

![enter image description here][1]

The problem is that whenever I save text longer than 500 characters in the description column I get the error:

    value too long for type character varying(500)

In the documentation for Postgres it says type text can have unlimited characters.

I&#39;m using postgresql-9.1.

This table has been generated using Django 1.4 and the field type in the model is TextField, if that helps explain the problem further.

Any ideas as why this is happening and what I can do to fix it?

  [1]: http://i.stack.imgur.com/wQ4l0.png

Strange PostgreSQL &quot;value too long for type character varying(500)&quot;

I have a table that looks like this caller &#39;makerar&#39;

     cname  | wmname |          avg           
    --------+-------------+------------------------
     canada | zoro   |     2.0000000000000000
     spain  | luffy  | 1.00000000000000000000
     spain  | usopp  |     5.0000000000000000

And I want to select the maximum avg for each cname. 

    SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname;

but I will get an error, 

    ERROR:  column &quot;makerar.wmname&quot; must appear in the GROUP BY clause or be used in an   aggregate function 
    LINE 1: SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname;

so i do this

    SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname, wmname;

however this will not give the intented results, and the incorrect output below is shown.

     cname  | wmname |          max           
    --------+--------+------------------------
     canada | zoro   |     2.0000000000000000
     spain  | luffy  | 1.00000000000000000000
     spain  | usopp  |     5.0000000000000000

Actual Results should be

     cname  | wmname |          max           
    --------+--------+------------------------
     canada | zoro   |     2.0000000000000000
     spain  | usopp  |     5.0000000000000000

How can I go about fixing this issue?

Note: This table is a VIEW created from  a previous operation.


must appear in the GROUP BY clause or be used in an aggregate function

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 trying to run unit tests in Django, and it creates a new database. The database has postgis extensions and when I regularly create the database, I use &quot;CREATE ExTENSION postgis&quot;.

However, when I run tests, it gives me the following error:

    $ ./manage.py test
    Creating test database for alias &#39;default&#39;...
    Got an error creating the test database: database &quot;test_project&quot; already exists

    Type &#39;yes&#39; if you would like to try deleting the test database &#39;test_project&#39;, or &#39;no&#39; to cancel: yes
    Destroying old test database &#39;default&#39;...
    DatabaseError: permission denied to create extension &quot;postgis&quot;
    HINT:  Must be superuser to create this extension.

The user has the Create DB privilege already, I&#39;m using PostgreSQL 9.1 on Ubuntu 12.04 with Postgis 2.0.

cannot create extension without superuser role

I am very new to databases, I haven&#39;t worked lot on it. Now I want to understand the term database clusters. I googled a lot and found many useful links but I am not able to understand them - maybe because I have very little basic knowledge about databases and also they were in very techy language.

I need advice on these points:

1. What are database clusters in PostgreSQL?
2. How to create clusters in PostgreSQL?

What&#39;s a PostgreSQL &quot;Cluster&quot; and how do I create one?

I&#39;m having an issue selecting dates properly from Postgres - they are being stored in UTC, but 
not converting with the Date() function properly.

Converting the timestamp to a date gives me the wrong date if it&#39;s past 4pm PST.

`2012-06-21` should be `2012-06-20` in this case.


The `starts_at` column datatype is `timestamp without time zone`. Here are my queries: 

Without converting to PST timezone: 

    Select starts_at from schedules where id = 40;

          starts_at      
    ---------------------
     2012-06-21 01:00:00

Converting gives this:

    Select (starts_at at time zone &#39;pst&#39;) from schedules where id = 40;
            timezone        
    ------------------------
     2012-06-21 02:00:00-07

But neither convert to the correct date in the timezone.

PostgreSQL date() with timezone

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?

In my database table I am having one boolean column. which have some transaction with will False, True and Null.

These are the cases i have tried:

**Case:1**

    select * from table_name where
    boolean_column is null;

works well. Give the result with all transaction having null value for that column.

**Case:2**

    select *from table_name where boolean_column = False;

Works well. Gives result with all the transaction having False value for that column.

Case:3
This is requirement which does not works. I want all the transaction having value False and Null.

I have tried these.

i)  `select *from table_name where boolean_column is False or Null;`

*Which only gives the result for False it does not shows null records.*


ii) `select *from table_name where boolean_column is Null or False;`

*Which only gives the result for null it does not shows records with False value. *

iii) `select *from table_name where boolean_column is Null or boolean_column = False;`

*This is simply display all the transaction does not applied any condition at all.*


How to resolve this issue.
Any guidance appreciated.

Thanks in Advance.

Rgds,
Anil.




Postgresql SQL: How check boolean field with null and True,False Value?

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

Assume in Postgresql, I have a table `T` and one of its column is `C1`. 

I want to trigger a function when a new record is adding to the table `T`. The function should check the value of column `C1` in the new record and if it is null/empty then set its value to `&#39;X&#39;`. 

Is this possible? 

Postgresql insert trigger to set value

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

All Postgresql 9.1 Solutions on Gang of Coders

Total of 38 Postgresql 9.1 Solutions