You can do this by enabling _Expanded display_.

Toggle this setting via `\x`. For example:

    # \x
    Expanded display is on.
    # \x
    Expanded display is off.

When on, results are shown in tabular (vertical) form:

    -[ RECORD 1 ]
    id  | 1
    bar | Hello
    -[ RECORD 2 ]
    id  | 2
    bar | World

You can run this for a single command by using the `\x\g\x` suffix to toggle expanded display on, run the query, then toggle it off again.

    select * from foo \x\g\x

Or via psql param as shared [here][1]

    psql db -xc &#39;select * from table&#39;

  [1]: https://stackoverflow.com/questions/22407860/display-select-results-vertically-in-psql-as-is-done-by-mysqls-g#comment86901835_22407861

Same can be achieved in dbeaver for postgres using &quot;record&quot; option in grid results

I am writing a python MapReduce word count program. Problem is that there are many non-alphabet chars strewn about in the data, I have found this post https://stackoverflow.com/questions/1276764/stripping-everything-but-alphanumeric-chars-from-a-string-in-python which shows a nice solution using regex, but I am not sure how to implement it

    def mapfn(k, v):
        print v
        import re, string 
        pattern = re.compile(&#39;[\W_]+&#39;)
        v = pattern.match(v)
        print v
        for w in v.split():
            yield w, 1

I&#39;m afraid I am not sure how to use the library `re` or even regex for that matter. I am not sure how to apply the regex pattern to the incoming string (line of a book) `v` properly to retrieve the new line without any non-alphanumeric chars.

Suggestions?

Python, remove all non-alphabet chars from string

I am a Laravel newbie. I want to seed my database. When I run the seed command I get an exception

      [Illuminate\Database\Eloquent\MassAssignmentException]
      username
    
    
    
    db:seed [--class[=&quot;...&quot;]] [--database[=&quot;...&quot;]]

What am I doing wrong. The command I use is:

    php artisan db:seed --class=&quot;UsersTableSeeder&quot;

My seed class is as follows:

    class UsersTableSeeder extends Seeder {
    	public function run()
    	{
                User::truncate();
                User::create([
                    &#39;username&#39; =&gt; &#39;PaulSheer&#39;,
                    &#39;email&#39; =&gt; &#39;psheer@rute.co.za&#39;,
                    &#39;password&#39; =&gt; &#39;45678&#39;
                ]);
                
                User::create([
                    &#39;username&#39; =&gt; &#39;Stevo&#39;,
                    &#39;email&#39; =&gt; &#39;steve@rute.co.za&#39;,
                    &#39;password&#39; =&gt; &#39;45678&#39;
                ]);
    	}
    }

MassAssignmentException in Laravel

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

In MySQL, you can terminate a <code>select</code> query with <code>\G</code> (as opposed to <code>\g</code>) to display the results vertically:
<pre><code class="hljs language-markdown">select * from foo \G

***************
 id: 1
bar: Hello
***************
 id: 2
bar: World
</code></pre>
How can one do the same thing for PostgreSQL using psql?

At amazon ec2 RDS Postgresql:

    =&gt; SHOW rds.extensions;
    
    rds.extensions                                                                                                                                 
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     btree_gin,btree_gist,chkpass,citext,cube,dblink,dict_int,dict_xsyn,earthdistance,fuzzystrmatch,hstore,intagg,intarray,isn,ltree,pgcrypto,pgrowlocks,pg_trgm,plperl,plpgsql,pltcl,postgis,postgis_tiger_geocoder,postgis_topology,sslinfo,tablefunc,tsearch2,unaccent,uuid-ossp
    (1 row)

As you can see, `uuid-ossp` extension does exist. However, when I&#39;m calling the function for generation `uuid_v4`, it fails:

    CREATE TABLE my_table (
        id uuid DEFAULT uuid_generate_v4() NOT NULL,
        name character varying(32) NOT NULL,
        
    );

What&#39;s wrong with this?



Extension exists but uuid_generate_v4 fails

I would like to give a user all the permissions on a database without making it an admin.
The reason why I want to do that is that at the moment DEV and PROD are different DBs on the same cluster so I don&#39;t want a user to be able to change production objects but it must be able to change objects on DEV.

I tried:

    grant ALL on database MY_DB to group MY_GROUP;

but it doesn&#39;t seem to give any permission.

Then I tried:

    grant all privileges on schema MY_SCHEMA to group MY_GROUP;

and it seems to give me permission to create objects but not to query\delete objects on that schema that belong to other users

I could go on by giving USAGE permission to the user on MY_SCHEMA but then it would complain about not having permissions on the table ...

So I guess my question is: is there any easy way of giving all the permissions to a user on a DB?

I&#39;m working on PostgreSQL 8.1.23.

PostgreSQL: Give all permissions to a user on a PostgreSQL database

PostgreSQL just introduced [JSONB][1] and it&#39;s already trending [on hacker news][2]. How is it different from Hstore and JSON previously present in PostgreSQL?

What are its advantages and limitations and when should someone consider using it?

  [1]: http://www.depesz.com/2014/03/25/waiting-for-9-4-introduce-jsonb-a-structured-format-for-storing-json/
  [2]: https://news.ycombinator.com/item?id=7457197





Explanation of JSONB introduced by PostgreSQL

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

I am running PostgreSQL 9.3.1. I have `test` database and `backup` user which is used to backup the database. I have no problems with granting privileges to all current tables, but I have to grant privileges each time the new table is added to schema.

    createdb test

    psql test
    test=# create table foo();
    CREATE TABLE
    test=# grant all on all tables in schema public to backup;
    GRANT
    test=# create table bar();
    CREATE TABLE

    psql -U backup test
    test=&gt; select * from foo;
    test=&gt; select * from bar;
    ERROR:  permission denied for relation bar

Is it possible to grant access to tables which will be created in future without making user owner of the table?

Grant privileges on future tables in PostgreSQL?

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?

How to show data in a table by using psql command line interface?

I want a shortcut key to clear the screen in my (Windows 7) psql console just like CTRL-l clears the screen in my R console. I am tired of typing &#39;\! cls&#39;. Do I need to write a macro for this? I am running Postgres 9.35. &#39;\r&#39; resets the query buffer but does not clear the screen. Thanks in advance for your help, sorry if I have just missed something simple.

Content Type	Original Author	Original Content on Stackoverflow
Question	Drew Noakes	View Question on Stackoverflow
Solution 1 - Postgresql	Drew Noakes	View Answer on Stackoverflow
Solution 2 - Postgresql	itsavy	View Answer on Stackoverflow

Display select results vertically in psql, as is done by MySQL's \G

Postgresql Problem Overview

Postgresql Solutions

Solution 1 - Postgresql

Solution 2 - Postgresql

MassAssignmentException in Laravel

Python, remove all non-alphabet chars from string

Attributions