Do you know what the maximum number of columns that can be queried in Postgresql? I need to know this  before I start my project. 

What is the maximum number of columns in a PostgreSQL select query

Whenever I try to drop database I get:

    ERROR:  database &quot;pilot&quot; is being accessed by other users
    DETAIL:  There is 1 other session using the database.

When I use:

    SELECT pg_terminate_backend(pg_stat_activity.pid)
    FROM pg_stat_activity
    WHERE pg_stat_activity.datname = &#39;TARGET_DB&#39;;

I terminated the connection from that DB, but if I try to drop database after that somehow someone automatically connects to that database and gives this error. What could be doing that? 
No one uses this database, except me.

Postgresql - unable to drop database because of some auto connections to DB

Is there any query available to list all tables in my Postgres DB.

I tried out one query like:

    SELECT table_name FROM information_schema.tables
                          WHERE table_schema=&#39;public&#39; 

But this query returns views also.

How can i get only table names only, not views?

PostgreSQL query to list all table names?

I am looking for some docs and/or examples for the new JSON functions in PostgreSQL 9.2. 

Specifically, given a series of JSON records:

    [
      {name: &quot;Toby&quot;, occupation: &quot;Software Engineer&quot;},
      {name: &quot;Zaphod&quot;, occupation: &quot;Galactic President&quot;}
    ]

How would I write the SQL to find a record by name?

In vanilla SQL:

    SELECT * from json_data WHERE &quot;name&quot; = &quot;Toby&quot;

The official dev manual is quite sparse:

 - http://www.postgresql.org/docs/devel/static/datatype-json.html
 - http://www.postgresql.org/docs/devel/static/functions-json.html

###Update I

I&#39;ve put together a [gist detailing what is currently possible with PostgreSQL 9.2][1].
Using some custom functions, it is possible to do things like:

    SELECT id, json_string(data,&#39;name&#39;) FROM things
    WHERE json_string(data,&#39;name&#39;) LIKE &#39;G%&#39;;

###Update II

I&#39;ve now moved my JSON functions into their own project:

[**PostSQL**][2] - a set of functions for transforming PostgreSQL and PL/v8 into a totally awesome JSON document store


  [1]: https://gist.github.com/2715918
  [2]: https://github.com/tobyhede/postsql

How do I query using fields inside the new PostgreSQL JSON datatype?

I am trying to dump a Postgresql database using the **pg_dump** tool. 

    $ pg_dump books &gt; books.out

How ever i am getting this error. 

    pg_dump: server version: 9.2.1; pg_dump version: 9.1.6
    pg_dump: aborting because of server version mismatch

The `--ignore-version` option is now deprecated and really would not be a a solution to my issue even if it had worked.

How can I upgrade **pg_dump** to resolve this issue?


Postgresql 9.2 pg_dump version mismatch

I have pgAdmin version `1.16.1`

So, for exporting table dumm I do:

Right click on table, then  in menu click on `backup`, then in `Format` choice `Plain` and save file as `some_name.sql` 


Then I remove table.

Ok, now I need import table backup `some_name.sql` into database.

How to do this?  I can&#39;t find how to import table&#39;s `.sql` dump into database using pgAdmin. 

Can you help me please?

Export and import table dump (.sql) using pgAdmin

I have column `arr`  which is of type `array`.

I need to get rows, where `arr` column contains value `s`

This query:

    SELECT * FROM table WHERE arr @&gt; ARRAY[&#39;s&#39;]

gives the error:

&gt; ERROR: operator does not exist: character varying[] @&gt; text[]

Why does it not work?

p.s. I know about `any()` operator, but why doesn&#39;t `@&gt;` work?

How to make a select with array contains value clause in psql

I just upgraded my MacMini Server from Lion Server to Mountain Lion using OS X Server.  I am having the same problem with PostgreSQL that I did last year when I first installed Lion Server.

When I try to do any kind of PostgreSQL terminal command I get the following notorious error message that many have gotten over the years:

    psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket &quot;/var/pgsql_socket/.s.PGSQL.5432&quot;?

I was attempting to change the password for _postgres when I got the error.  I tried several commands but got the same error.  I just rebooted my server but no luck.  I logged in as root to look at /var/pgsql_socket and the folder is empty.  Folder /var/pgsql_socket_alt is also empty.

I have checked online about this.  However just about all of the solutions I have read, including on Stack Overflow, suggest a removal and reinstall of PostgreSQL.  I do not know but this does not seem like a plausible option because several options on the Server App use PostgreSQL.  I contacted Apple Enterprise Support (no agreement) and I was told that my issue would have to be solved by the developers which would cost $695.

I have a website that is down right now because I cannot rebuild it.  I don&#39;t know where to turn for help with this at this point.  I will continue looking online to see if I can find something.  However I hope that someone can give me an answer quick so I can rebuild my database.

**Update: 12/13/2012 15:33 GMT-6**

Here is my output for ps auwx|grep postg:

    _postgres      28123   0.0  0.1  2479696   7724   ??  Ss    3:01PM   0:00.04 /Applications/Server.app/Contents/ServerRoot/usr/bin/postgres_real -D /Library/Server/PostgreSQL For Server Services/Data -c listen_addresses= -c log_connections=on -c log_directory=/Library/Logs/PostgreSQL -c log_filename=PostgreSQL_Server_Services.log -c log_line_prefix=%t  -c log_lock_waits=on -c log_statement=ddl -c logging_collector=on -c unix_socket_directory=/Library/Server/PostgreSQL For Server Services/Socket -c unix_socket_group=_postgres -c unix_socket_permissions=0770
    server1        28216   0.0  0.0  2432768    620 s000  R+    3:02PM   0:00.00 grep postg
    _postgres      28138   0.0  0.0  2439388    752   ??  Ss    3:01PM   0:00.01 postgres: stats collector process                           
    _postgres      28137   0.0  0.0  2479828   1968   ??  Ss    3:01PM   0:00.00 postgres: autovacuum launcher process                           
    _postgres      28136   0.0  0.0  2479696    544   ??  Ss    3:01PM   0:00.00 postgres: wal writer process                           
    _postgres      28135   0.0  0.0  2479696    732   ??  Ss    3:01PM   0:00.01 postgres: writer process                           
    _postgres      28134   0.0  0.0  2479696    592   ??  Ss    3:01PM   0:00.00 postgres: checkpointer process                           
    _postgres      28131   0.0  0.0  2439388    368   ??  Ss    3:01PM   0:00.00 postgres: logger process 

**Update: 12/13/2012 18:10 GMT-6**

After intense web searching this video was found.  I was able to get PostgreSQL working and remove the error.  I am able to connect using pgadmin and phppgadmin.  I was about to go back to Lion Server because of sheer frustration.  Now I will not have to.

http://www.youtube.com/watch?v=y1c7WFMMkZ4

Socket File &quot;/var/pgsql_socket/.s.PGSQL.5432&quot; Missing In Mountain Lion (OS X Server)

I am trying to open a program for the first time on Windows XP Pro that uses PostgreSQL 9. I&#39;m getting an error message that says :

&gt; A problem was encountered while trying to log into or create the
&gt; production database. Details: [Microsoft][ODBC Driver Manager] Data
&gt; source name not found and no default driver specified

In my ODBC manager, I have a list of User DSN&#39;s and System DSN&#39;s. I tried installing a postgres odbc driver to see if that would help, but it didn&#39;t. 

There is a connect.dat file in the program file with a line saying &quot;OLE DB Provider = MSDASQL&quot;. Changing this entry alters the error message I get to &quot;Provider cannot be found, it may not be properly installed&quot;. 

I don&#39;t know what provider name to insert to get this to work properly. I have done extensive research on this error to no avail. Any suggestions would be greatly appreciated.

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

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?

I have a column `added_at` of type `timestamp without time zone`. I want it&#39;s default value to be the current date-time but without time zone. The function `now()` returns a timezone as well.

How do I solve that problem?

Now() without timezone

What is the command to find the size of *all* the databases?

I am able to find the size of a *specific* database by using following command:

    select pg_database_size(&#39;databaseName&#39;);

Postgres DB Size Command

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 blank Spring MVC project, and I&#39;ve installed Hibernate and the PostgreSQL drivers using Maven.

I&#39;m running short on complete tutorials that show how to connect PostgreSQL with Hibernate.

Any help here?

Connecting PostgreSQL 9.2.1 with Hibernate

In PostgreSql 9.2.4 I have two tables: `user (id, login, password, name)` and `dealer (id, user_id)`.

And I want to insert into both tables returning id of created dealer.

Currently I&#39;m doing it with two queries:

    WITH rows AS (
        INSERT INTO &quot;user&quot;
    	    (login, password, name)
        VALUES
    	    (&#39;dealer1&#39;, &#39;jygbjybk&#39;, &#39;Dealer 1&#39;)
        RETURNING id
    )
    INSERT INTO dealer (user_id)
        SELECT id
        FROM rows;
    SELECT currval(&#39;dealer_id_seq&#39;);

But can I implement this with a single `INSERT` query using `RETURNING` statement?

PostgreSql INSERT FROM SELECT RETURNING ID

I tried to run simple SQL command:

    select * from site_adzone;

and I got this error

&gt; ERROR:  permission denied for relation site_adzone

What could be the problem here?

I tried also to do select for other tables and got same issue. I also tried to do this:

    GRANT ALL PRIVILEGES ON DATABASE jerry to tom;

but I got this response from console

&gt; WARNING:  no privileges were granted for &quot;jerry&quot;

Does anyone have any idea what can be wrong?

Permission denied for relation

We have a table of photos with the following columns: 

    id, merchant_id, url 

this table contains duplicate values for the combination `merchant_id, url`. so it&#39;s possible that one row appears more several times. 

    234 some_merchant  http://www.some-image-url.com/abscde1213
    235 some_merchant  http://www.some-image-url.com/abscde1213
    236 some_merchant  http://www.some-image-url.com/abscde1213



What is the best way to delete those duplications?
(I use PostgreSQL 9.2 and Rails 3.)


Find duplicate rows with PostgreSQL

Does anyone know what is the limit on the size of JSON data type in PostgreSQL 9.2?

Size limit of JSON data type in PostgreSQL

I have a `TEXT` column containing valid JSON string.

    CREATE TABLE users(settings TEXT);

    INSERT INTO users VALUES (&#39;{&quot;language&quot;:&quot;en&quot;,&quot;gender&quot;:&quot;male&quot;}&#39;);
    INSERT INTO users VALUES (&#39;{&quot;language&quot;:&quot;fr&quot;,&quot;gender&quot;:&quot;female&quot;}&#39;);
    INSERT INTO users VALUES (&#39;{&quot;language&quot;:&quot;es&quot;,&quot;gender&quot;:&quot;female&quot;}&#39;);
    INSERT INTO users VALUES (&#39;{&quot;language&quot;:&quot;en&quot;,&quot;gender&quot;:&quot;male&quot;}&#39;);

I want to transform some fields into a query-able format.

A `REGEXP_REPLACE` for each field would do (`language` field and `gender` field). But since it&#39;s valid JSON, is there way to:

 - Convert into JSON type
 - Convert into hstore type
 - Or any other feasible ways

SQLFiddle: http://sqlfiddle.com/#!12/54823

PostgreSQL 9.2 - Convert TEXT json string to type json/hstore

I am using Ubuntu 12.04 and Postgress 9.2.

I need to create this user with this password e.g.

    postgres://admin:test101@127.0.0.1:5432

How to do that from the command line?  I need to automate with a bash script.  I have a fresh install.

All Postgresql 9.2 Solutions on Gang of Coders

Total of 20 Postgresql 9.2 Solutions