psql – Page 6 – Tarik Billa

PG::UndefinedTable: ERROR: missing FROM-clause entry for table when using joins and where

March 26, 2023 by Tarik

Hmm it looks like you’re trying to include current_orders and include order. Are these the same tables with different conditions? This might be confuse active record. Also, I’m pretty sure it’s wise to include the references method when referencing a joined table. Perhaps, try something like this: active_couriers = Courier.includes(:orders) .available_courier_status .where(:service_region_id => @service_region.id) .where(“orders.created_at … Read more

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

March 23, 2023 by Tarik

Solved it myself, meh. Needed to declare arow record. do $$ declare arow record; foo varchar(50); begin for arow in select a from (values(‘foo’), (‘bar’), (‘fooBar’)) s(a) loop foo := arow.a; RAISE NOTICE ‘Calling cs_create_job(%)’, foo; end loop; end; $$;

Postgres JSON data type Rails query

March 22, 2023 by Tarik

For any who stumbles upon this. I have come up with a list of queries using ActiveRecord and Postgres’ JSON data type. Feel free to edit this to make it more clear. Documentation to the JSON operators used below: https://www.postgresql.org/docs/current/functions-json.html. # Sort based on the Hstore data: Post.order(“data->’hello’ DESC”) => #<ActiveRecord::Relation [ #<Post id: 4, … Read more

How to Add, Delete new Columns in Sequelize CLI

February 23, 2023 by Tarik

If you are using sequelize-cli you need to create the migration first. This is just a file that tells the engine how to update the database and how to roll back the changes in case something goes wrong. You should always commit this file to your repository $ sequelize migration:create –name name_of_your_migration The migration file … Read more

“psql: could not connect to server: Connection refused” Error when connecting to remote database

January 4, 2023 by Tarik

cd /etc/postgresql/9.x/main/ open file named postgresql.conf sudo vi postgresql.conf add this line to that file listen_addresses=”*” then open file named pg_hba.conf sudo vi pg_hba.conf and add this line to that file host all all 0.0.0.0/0 md5 It allows access to all databases for all users with an encrypted password restart your server sudo /etc/init.d/postgresql restart

PostgreSQL: Remotely connecting to Postgres instance using psql command

January 1, 2023 by Tarik

I resolved this issue using below options: Whitelist your DB host from your network team to make sure you have access to remote host Install postgreSQL version 4 or above Run below command: psql -h <REMOTE HOST> -p <REMOTE PORT> -U <DB_USER> <DB_NAME>

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

December 22, 2022 by Tarik

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 | … Read more

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

December 12, 2022 by Tarik

I think the solution to add following to .psqlrc is far from perfection \set ON_ERROR_STOP on there exists much more simple and convenient way – use psql with parameter: psql -v ON_ERROR_STOP=1 better to use also -X parameter turning off .psqlrc file usage. Works perfectly for me p.s. the solution found in great post from … Read more

psql: command not found Mac

December 12, 2022 by Tarik

You have got the PATH slightly wrong. You need the PATH to “the containing directory”, not the actual executable itself. Your PATH should be set like this: export PATH=/Library/PostgreSQL/9.5/bin:$PATH without the extra sql part in it. Also, you must remove the spaces around the equals sign. Keywords: Postgresql, PATH, macOS, OSX, psql

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

December 2, 2022 by Tarik

createdb is a command line utility which you can run from bash and not from psql. To create a database from psql, use the create database statement like so: create database [databasename]; Note: be sure to always end your SQL statements with ;