In MySQL, I used `use database_name;`

What&#39;s the `psql` equivalent?



How to switch databases in psql?

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

Here is my simple anonymous code block:

    do $$
      declare foo varchar(50) := &#39;&#39;;
      begin
        for a in
          select a from (values(&#39;foo&#39;), (&#39;bar&#39;), (&#39;fooBar&#39;)) s(a)
        loop
          foo := a;
          print foo;
        end loop;
      end;
    $$;

When I run it:

    psql -f test.sql

I get this error:

    psql:test.sql:11: ERROR:  loop variable of loop over rows must be a record or row variable or list of scalar variables
    LINE 4:     for a in
                ^

psql - loop variable of loop over rows must be a record or row variable or list of scalar variables

Is the default way of calling a function `select * from my_function()`?

I ask because I have built a function that doesn&#39;t return anything, just inserts data into a table and (coming from a SQL Server background) it &quot;feels&quot; strange to call it with `select * from...`

I was expecting something like `exec my_function()`

Calling functions with exec instead of select

Why psql can&#39;t find relation name for existing table?

I exported a postgresql database from an external server, and attempted to import it into my local server but got this error:

    unrecognized configuration parameter &quot;idle_in_transaction_session_timeout&quot;

Does this kind of error mean that the two servers are using different versions of postgresql? I looked into that, and the external server is running:

    version
    PostgreSQL 9.5.4 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit	

and my server is running:

    version
    PostgreSQL 9.5.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.2) 5.4.0 20160609, 64-bit	

Pretty much the same thing. Is there a site where you can see all of the valid config parameters for each version? And is there a way to sync up two databases like this, so incompatibilities like this get patched up automatically?

Postgresql - unrecognized configuration parameter

I have installed PostgreSQL and it is working ok. However, when I went to restore a backup I got the error `-bash: psql: command not found`:

     [root@server1 ~]# su postgres
     [postgres@server1 root]$ psql -f all.sql
     bash: psql: command not found
     [postgres@server1 root]$ 

What have I done wrong?

Postgresql -bash: psql: command not found

I have a database that was exported with pg_dump, but now when I&#39;m trying to import it again with:

    psql -d databasename &lt; mydump.sql

It fails trying to grant roles to people that don&#39;t exist. (error says &#39;Role &quot;xxx&quot; does not exist&#39;) 

Is there a way to import and set all the roles automatically to my user?

Import postgres database without roles

What&#39;s the character for comments in postgres?

    SELECT * FROM my_table     # pound  sign produces a syntax error

Thank you cababunga, the following appears to work:

    SELECT * FROM my_table     -- this is my comment

But this does not work:

    \dt jvcurve_thin.jvcurve_results    --  my comment #2

\dt: extra argument &quot;--&quot; ignored



Comment character/characters in postgres / postgresql / psql?

I&#39;m trying to get `psql` to format nicely and am following the docs [here](http://www.postgresql.org/docs/9.0/static/app-psql.html). Right now, whenever I do a query on tables with lots of columns, no matter how big I make my screen each line overflows into the next line and producing a whole screen of unreadable junk.

The docs (link is above) say there&#39;s a way to align columns nicely for more readable output.

Normally, to start `psql`, I just type:

&gt; psql

and hit *Enter*. Now I&#39;m trying:

&gt; psql \pset format aligned

And getting an error:

    could not change directory to &quot;/root&quot;
    psql: warning: extra command-line argument &quot;aligned&quot; ingored
    psql: FATAL: Indent authentication failed for user &quot;format&quot;

Any ideas as to how I could get these command-line args to work for me?

PostgreSQL psql terminal command

A password is visible in my psql prompt (by pushing up arrow to view previous inputted commands). So I would like to delete that entry from the psql client. 

Other resources([1], [2]) claim that psql client history is kept in is a `~/.psql_history` file, however this file is simply not there. (it&#39;s not in `.bash_history` either)

So **there must be another file where the history of psql prompt is kept, any ideas where?**

I log in to my prompt like this 
`sudo -u postgres psql my_db`

Note: I am working on `Ubuntu 12.04`


  [1]: http://www.thegeekstuff.com/2009/04/15-practical-postgresql-database-adminstration-commands/
  [2]: https://stackoverflow.com/questions/6654121/postgresql-psql-history-to-dev-null

Where is psql client history kept? (~/.psql_history non-existent!)

Given a file with a SQL insert:

    INSERT INTO countries (id, country_code, name)
    VALUES
        (1, &#39;AF&#39;, &#39;Afghanistan&#39;),
        (2, &#39;AL&#39;, &#39;Albania&#39;);

I would like to run the file by using the `docker run` command on a container that is running postgres.

I&#39;ve tried this:

    docker run -e domain=&quot;192.168.99.100&quot; pg /bin/bash -c &quot;psql -d whiteboard_api -a -f inserts_into_countries_table.sql&quot;
    psql: could not connect to server: Connection refused
    	Is the server running locally and accepting
    	connections on Unix domain socket &quot;/var/run/postgresql/.s.PGSQL.5432&quot;?

you can see my image is pg:

    capistrano:whiteboard_v2 jzollars$ docker ps
    CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
    6b500bec9210        bbb                 &quot;/usr/bin/supervisord&quot;   4 weeks ago         Up 4 weeks          0.0.0.0:80-&gt;80/tcp       distracted_raman
    c1e88f2695f5        wh                  &quot;/usr/bin/supervisord&quot;   4 weeks ago         Up 4 weeks          0.0.0.0:3000-&gt;3000/tcp   high_einstein
    7e383e99bdc3        pg                  &quot;/usr/lib/postgresql/&quot;   4 weeks ago         Up 4 weeks          0.0.0.0:5432-&gt;5432/tcp   pg_test

How can I load this file and run it in a docker container using `docker run`?

How do I run a sql file of inserts through docker run?

When using Psql in Linux, if the result of my SQL query contains many columns or long strings of data, it will wrap the initial view and only once I scroll to the side will it stop wrapping and show each row on a separate line.

I&#39;ve tried various [`\pset` options][1] such as `format unaligned`, `format aligned`, `format wrapped`, `columns 0`, `columns 1000`, but none seemed to *fully* stop wrapping unless I generate static output to a file.

How can I set it to never wrap the output while still being scrollable and showing the result using the default ascii table format?


  [1]: https://www.postgresql.org/docs/9.2/static/app-psql.html

Disable wrapping in Psql output

[Official page][1] do not mention such case. But many users need only `psql` without a local database (I have it on AWS). Brew do not have `psql`.


  [1]: https://www.postgresql.org/download/macosx

Correct way to install psql without full Postgres on macOS?

I have a postgres database with multiple schemas. When I connect to the database from a shell with `psql` and I run `\dt` it uses the default connection schema which is *public*. Is there a flag I can specify or how can I change the schema?

How to select a schema in postgres when using psql?

I just installed posgresql with homebrew and when I go on to type the command

    psql
I get the following error:

    dyld: Library not loaded: /usr/local/opt/readline/lib/libreadline.6.2.dylib
    Referenced from: /usr/local/bin/psql
    Reason: image not found
    [1]    69711 trace trap  psql

Does anyone have any idea about what&#39;s wrong?

Library not loaded: /usr/local/opt/readline/lib/libreadline.6.2.dylib

I have PSQL running, and am trying to get a perl application connecting to the database. Is there a command to find the current port and host that the database is running on?

Find the host name and port using PSQL commands

I&#39;m using the PostgreSql app for mac (http://postgresapp.com/). I&#39;ve used it in the past on other machines but it&#39;s giving me some trouble when installing on my macbook. I&#39;ve installed the application and I ran:

    psql -h localhost

It returns:
    
    psql: FATAL:  database &quot;&lt;user&gt;&quot; does not exist

It seems I can&#39;t even run the console to create the database that it&#39;s attempting to find. The same thing happens when I just run:

    psql 

or if I launch psql from the application drop down menu:

Machine stats:

- OSX 10.8.4

- psql (PostgreSQL) 9.2.4

Any help is appreciated. 


I&#39;ve also attempted to install PostgreSql via homebrew and I&#39;m getting the same issue. I&#39;ve also read the applications documentation page that states: 

&gt; When Postgres.app first starts up, it creates the $USER database,
&gt; which is the default database for psql when none is specified. The
&gt; default user is $USER, with no password.

So it would seem the application is not creating $USER however I&#39;ve installed-&gt;uninstalled-reinstalled several times now so it must be something with my machine.

I found the answer but I&#39;m not sure exactly how it works as the user who answered on this thread -&gt; https://stackoverflow.com/questions/13515834/getting-postgresql-running-in-mac-database-postgres-does-not-exist didn&#39;t follow up. I used the following command to get psql to open: 

    psql -d template1

*I&#39;ll leave this one unanswered until someone can provide an explanation for why this works.*

psql: FATAL: database &quot;&lt;user&gt;&quot; does not exist

Entering the following command into a PostgreSQL interactive terminal results in an error:

    ALTER TABLE tbl_name ALTER COLUMN col_name varchar (11);

What is the correct command to alter the data type of a column?


How to alter a column&#39;s data type in a PostgreSQL table?

In MS SQL Server, I create my scripts to use customizable variables:

    DECLARE @somevariable int  
    SELECT @somevariable = -1

    INSERT INTO foo VALUES ( @somevariable )

I&#39;ll then change the value of `@somevariable` at runtime, depending on the value that I want in the particular situation. Since it&#39;s at the top of the script it&#39;s easy to see and remember.

How do I do the same with the PostgreSQL client `psql`?

How do you use script variables in psql?

I have installed postgresql on OSX. When I run psql, I get

    $ psql
    psql: could not connect to server: No such file or directory
    	Is the server running locally and accepting
    	connections on Unix domain socket &quot;/tmp/.s.PGSQL.5433&quot;?
However, from /etc/services

    postgresql      5432/udp    # PostgreSQL Database
    postgresql      5432/tcp    # PostgreSQL Database
    #                          Tom Lane &lt;tgl@sss.pgh.pa.us&gt;
    pyrrho          5433/tcp    # Pyrrho DBMS
    pyrrho          5433/udp    # Pyrrho DBMS

5433 is occupied by pyrrho, 5432 is assigned to pg. I can connect with

    psql -p 5432

but why does psql think it is 5433 and how do I make psql look in the right place by default?

postgresql port confusion 5433 or 5432?

I am running a script on my PostgreSQL server:

    psql db -f sql.sql

from `bash` or in a `cron` script.

It keeps trying to paginate the output with `more` or `less`. 

How do I disable result pagination in `psql`?

All I want to do is change the data, I don&#39;t care about any output.


PostgreSQL disable more output

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 have some .sql files with thousands of INSERT statements in them and need to run these inserts on my PostgreSQL database in order to add them to a table. The files are that large that it is impossible to open them and copy the INSERT statements into an editor window and run them there. I found on the Internet that you can use the following by navigating to the bin folder of your PostgreSQL install:

    psql -d myDataBase -a -f myInsertFile

In my case:

    psql -d HIGHWAYS -a -f CLUSTER_1000M.sql

I am then asked for a password for my user, but I cannot enter anything and when I hit enter I get this error:

&gt; psql: FATAL: password authentication failed for user &quot;myUsername&quot;

Why won&#39;t it let me enter a password. Is there a way round this as it is critical that I can run these scripts?

I got around this issue by adding a new entry in my pg_hba.conf file with the following structure:

    # IPv6 local connections:
    host    myDbName    myUserName ::1/128    trust

The pg_hba.conf file can usually be found in the &#39;data&#39; folder of your PostgreSQL install.


Run a PostgreSQL .sql file using command line arguments

Psql list all tables

Could you tell me how to check what indexes are created for some table in postgresql ?  

How to list indexes created for table in postgres

Postgresql not creating db with “createdb” as superuser, yet not outputting errors

Is there a way to specify that when executing a sql script it stops when encountering the first error on the script, it usually continues, regardless of previous errors.


How can I stop a Postgres script when it encounters an error?

I installed PostgreSQL via the graphical install on [http://www.postgresql.org/download/macosx/][1]

I see it in my applications and also have the psql terminal in my applications. I need psql to work in the regular terminal for another bash script I&#39;m running for an app.

For some reason, when I run

    psql

in the Mac terminal, my output is 

    -bash: psql: command not found

I ran the following in the terminal:

    locate psql | grep /bin

and the output was

    /Library/PostgreSQL/9.5/bin/psql

I then edited my ~/.bash_profile and added it to the path like so:

    export PATH = /Library/PostgreSQL/9.5/bin/psql:$PATH

The only other thing in ~/.bash_profile is SDK man and it&#39;s at the bottom of the script as it says it should be. I&#39;ve tried setting the bath to just the /Library/PostgreSQL/9.5/bin/ as well. I&#39;ve restarted my terminal also.

How can I get psql to work? 

**EDIT**
After adding to .bashrc, this output is returned when I open terminal

    -bash: export: `/Library/PostgreSQL/9.5/bin:/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin&#39;: not a valid identifier


  [1]: http://www.postgresql.org/download/macosx/

psql: command not found Mac

I typed `psql` and I get this:

    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/run/postgresql/.s.PGSQL.5432&quot;?
I used `sudo netstat -nlp | grep 5432` to see the status but nothing showed. 
And I searched online, somebody told me to modify `pg_hba.conf` but I can&#39;t `locate` this file. And I also tried this command `sudo ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432`. It can&#39;t work.

PostgreSQL: Why psql can&#39;t connect to server?

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

I am trying to automate database creation process with a shell script and one thing I&#39;ve hit a road block with passing a password to [psql][1].
Here is a bit of code from the shell script:

    psql -U $DB_USER -h localhost -c&quot;$DB_RECREATE_SQL&quot;

How do I pass a password to `psql` in a non-interactive way?

  [1]: https://en.wikipedia.org/wiki/PostgreSQL#Database_administration


How do I specify a password to &#39;psql&#39; non-interactively?

How do you perform the equivalent of Oracle&#39;s `DESCRIBE TABLE` in PostgreSQL (using the psql command)?

PostgreSQL &quot;DESCRIBE TABLE&quot;

I am trying to connect to a Postgres database installed in a remote server using the following command:

psql -h `host_ip` -U `db_username` -d `db_name`

This is the error that occurs:
&gt; psql: could not connect to server: Connection refused
	Is the server running on host &quot;&lt;host_ip&gt;&quot; and accepting
	TCP/IP connections on port 5432?

 1. Postgres installed version is 9.4.
 2. Host operating system: Ubuntu 15.04
 3. Client operating system: Centos 7

I already tried the following but the issue remains unresolved:

1. Edited `pg_hba.conf` file to include 

&gt; host    all     all     0.0.0.0/0  md5

 2. Edited &#39;postgresql.conf&#39; and changed the listen parameter to

&gt; listen_addresses=&#39;*&#39;

 3. Restarted Postgres service.
 4. Disabled firewall and iptables on host and client.
 5. I checked by running the psql command locally and it worked.
 6. I tried the second solution given in this [question][1]. Running `nmap` gave me the following output:

`Starting Nmap 6.47 ( http://nmap.org ) at 2015-09-07 18:08 IST
Nmap scan report for 10.17.250.250
Host is up (0.0000040s latency).
Not shown: 997 closed ports
PORT   STATE SERVICE
22/tcp open  ssh
25/tcp open  smtp
80/tcp open  http`


Am I missing something? Hope someone can help.

 


  [1]: https://stackoverflow.com/questions/6150738/cannot-connect-to-postgres-from-remote-host

&quot;psql: could not connect to server: Connection refused&quot; Error when connecting to remote database

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

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 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.

keystroke to clear screen in psql?

I am using PostgreSQL 8.4 on Ubuntu. I have a table with columns `c1` through `cN`. The columns are wide enough that selecting all columns causes a row of query results to wrap multiple times. Consequently, the output is hard to read.

When the query results constitute just a few rows, it would be convenient if I could view the query results such that each column of each row is on a separate line, e.g.

     c1: &lt;value of row 1&#39;s c1&gt;
     c2: &lt;value of row 1&#39;s c1&gt;
     ...
     cN: &lt;value of row 1&#39;s cN&gt;
     ---- some kind of delimiter ----
     c1: &lt;value of row 2&#39;s c1&gt;
     etc.

I am running these queries on a server where I would prefer not to install any additional software. Is there a psql setting that will let me do something like that?

Alternate output format for psql

I want to remotely connect to a Postgres instance. I know we can do this using the psql command passing the hostname

I tried the following:

    psql -U postgres -p 5432 -h hostname

I modified the /etc/postgresql/9.3/main/pg_hba.conf file on the target machine to allow remote connections by default

I added the following line to the file 

    host all all source_ip/32 trust

I restarted the cluster using 

    pg_ctlcluster 9.2 mycluster stop
    pg_ctlcluster 9.2 mycluster start


However, when I try to connect from the source_ip, I still get the error 

&gt; Is the server running on host &quot;&quot; and accepting TCP/IP connections on port 5432?

What am I doing wrong here?


PostgreSQL: Remotely connecting to Postgres instance using psql command

I&#39;m using psql&#39;s `\dt` to list all tables in a database and I need to save the results.

What is the syntax to export the results of a psql command to a file?

psql - save results of command to a file

I&#39;ve just started using Sequelize and Sequelize CLI

Since it&#39;s a development time, there are a frequent addition and deletion of columns. What the best the method to add a new column to an existing model?

For example, I want to a new column &#39;**completed**&#39; to **Todo** model. I&#39;ll add this column to models/todo.js. Whats the next step?

I tried `sequelize db:migrate`

not working: *&quot;No&#160;migrations&#160;were&#160;executed,&#160;database&#160;schema&#160;was&#160;already&#160;up&#160;to&#160;date.&quot;*


How to Add, Delete new Columns in Sequelize CLI

How can I call **psql** so that it **doesn&#39;t prompt for a password**?

This is what I have:

    psql -Umyuser &lt; myscript.sql

However, I couldn&#39;t find the argument that passes the password, and so psql always prompts for it.



Postgresql: Scripting psql execution with password

How do I list all extensions that are already installed in a database or schema from psql?

See also

* [Finding a list of available extensions that PostgreSQL ships with](https://dba.stackexchange.com/questions/166283/where-can-i-find-a-list-of-the-extensions-for-postgresql)

Using psql how do I list extensions installed in a database?

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;}
         ]
    }

All Psql Solutions on Gang of Coders

Total of 45 Psql Solutions