Postgres not accepting connection if I say -h localhost but it works if I say -h 127.0.0.1 My /var/lib/pgsql/data/pg_hba.conf If I add following line then Postgres service failed to start: Wwhat is wrong there? Update My /etc/hosts file: Answer In pg_hba.conf, the first match counts. The manual: The first rec…
Tag: postgresql
Columns with keywords as names in PostgreSQL
I have a couple of tables which have keywords as column names. I am not able to write a subquery using them. Even if I use non-existing column names, the query works. For example I have a few questions – when I use a non-existing column name in a subquery, it works. But if I use the same column name
How to return a value from a function if no value is found
I’m attempting to return 0.0 if the following function does not return anything: I’ve tried searching for it and found that COALESCE does not work. Does anyone have any ideas how to solve this? Table structure: Example data: Answer Here is the script I used. As you can see I run PostgreSQL 9.4.1. …
jsonb query with nested objects in an array
I’m using PostgreSQL 9.4 with a table teams containing a jsonb column named json. I am looking for a query where I can get all teams which have the Players 3, 4 and 7 in their array of players. The table contains two rows with the following json data: First row: second row: How does the query have to lo…
Generate_series in Postgres from start and end date in a table
I have been trying to generate a series of dates (YYYY-MM-DD HH) from the first until the last date in a timestamp field. I’ve got the generate_series() I need, however running into an issue when trying to grab the start and end dates from a table. I have the following to give a rough idea: Postgres 9.3…
Run a query with a LIMIT/OFFSET and also get the total number of rows
For pagination purposes, I need a run a query with the LIMIT and OFFSET clauses. But I also need a count of the number of rows that would be returned by that query without the LIMIT and OFFSET clauses. I want to run: And: At the same time. Is there a way to do that, particularly a way that lets
PostgreSQL distinct rows joined with a count of distinct values in one column
I’m using PostgreSQL 9.4, and I have a table with 13 million rows and with data roughly as follows: There are indices on md5(a), on b, and on (md5(a), b). (In reality, a may contain values longer than 4k chars.) There is also a primary key column of type SERIAL which I have omitted above. I’m tryi…
SET CONSTRAINTS ALL DEFERRED not working as expected
In a PostgreSQL 9.3 database, if I define tables a and b as follows: And then do the following: It produces the error below. Why is SET CONSTRAINTS not having the desired effect? Answer Only DEFERRABLE constraints can be deferred. Let me suggest superior alternatives first: 1. INSERT in order Reverse the sequ…
PostgreSQL Foreign Key syntax
I have 2 tables as you will see in my PosgreSQL code below. The first table students has 2 columns, one for student_name and the other student_id which is the Primary Key. In my second table called tests, this has 4 columns, one for subject_id, one for the subject_name, then one for a student with the highest…
What is the difference between LATERAL JOIN and a subquery in PostgreSQL?
Since Postgres came out with the ability to do LATERAL joins, I’ve been reading up on it, since I currently do complex data dumps for my team with lots of inefficient subqueries that make the overall …