Rails 3.2.2 (Or 3.2.1) + Postgresql 9.1.3 + Ubuntu 11.10 Connection Error

rails 3.2.2 (or 3.2.1) + Postgresql 9.1.3 + Ubuntu 11.10 Connection error

try this way,

sudo -u postgres createuser -D -P your-current-ubuntu-username

and

sudo -u postgres createdb -O your-current-ubuntu-username your-database

open up this file /etc/postgresql/9.1/main/pg_hba.conf

change this line only:

local   all             all                                     peer

to this:

local   all             all                                     md5

Don't forget to restart the postgres server:

sudo service postgresql restart

Now check, with this command

psql -d your-database -U your-current-ubuntu-username -W

it should work

This solutions works for postgresql-9.1, here is the way to install

sudo apt-get install postgresql-9.1

Setting up postgres for rails production on VPS

I feel like such an idiot. The indentation in database.yaml is significant. Total hours wasted: 8.

psql version different depending on how you check it

Apparently you have two Postgres versions installed (13 and 11).

The PATH variable contains the binaries (psql, pg_config) for version 13, but version 11 is running on the default port 5432. Which also explains why you need to change pg_hba.conf for the version 11 to see a difference in the connection behaviour.

If you want to connect to the 13 instance, you need to provide the port for that. Try e.g. psql -p 5433 -U postgres - typically a second installation defaults to the next higher port number

Group Points into Linestrings based on one column but only if sequential following order by other column

You can use a difference of row numbers approach to classify the groups (run the inner query to see group assignment) and then use ST_MakeLine to get all those points on to one row.

select ST_MakeLine(the_geom order by datetime) as the_geom, max(m_status) as m_status
from (select t.*,
      row_number() over(order by datetime)
      -row_number() over(partition by m_status order by datetime) as grp
      from tbl t
     ) t
group by grp

Edit: Based on OP's edit to include row numbers in the output, get the min or max time per group previously identified and use it for ordering.

SELECT row_number() over(ORDER BY min_time) AS id,
       ST_MakeLine(the_geom ORDER BY datetime) AS the_geom,
       max(m_status) AS m_status
FROM
  (SELECT t.*,
           min(datetime) over(partition BY grp,m_status) AS min_time
   FROM
     (SELECT t.*,
             row_number() over(ORDER BY datetime) -row_number() over(partition BY m_status ORDER BY datetime) AS grp
      FROM tbl t
   ) t 
) t
GROUP BY grp,min_time

Postgresql: Using xpath to extract data from an XML column

Your XML defines a namespace, and that namespace must be used in the xpath expression.

SELECT xpath('/g:Attributes/g:bemaerkning/text()', t, array[array['g','http://www.gis34.dk']]) as comment
FROM   x

Note the third parameter that passes a two-dimensional array of namespace mappings.

The xpath() function returns an array of elements. If you know you only get a single element (or only ever want the first one) you just return the first element of the array:

SELECT (xpath('/g:Attributes/g:bemaerkning/text()', t, array[array['g','http://www.gis34.dk']])[1] as comment
FROM   x

Note the parentheses around the function call: (xpath(...))

---

Related Topics