Stack Overflow
Stack Overflow

Questions tagged [postgresql-9.2]

for PostgreSQL questions specific to version 9.2.

On 10 September 2012, PostgreSQL 9.2.0 was released. It was End Of Life as of September 2017. Major enhancements in PostgreSQL 9.2 include:

  • Allow queries to retrieve data only from indexes, avoiding heap access (index-only scans)
  • Allow the planner to generate custom plans for specific parameter values even when using prepared statements
  • Improve the planner's ability to use nested loops with inner index scans
  • Allow streaming replication slaves to forward data to other slaves (cascading replication)
  • Allow pg_basebackup to make base backups from standby servers
  • Add a pg_receivexlog tool to archive WAL file changes as they are written
  • Add the SP-GiST (Space-Partitioned GiST) index access method
  • Add support for range data types
  • Add a JSON data type
  • Add a security_barrier option for views
  • Allow libpq connection strings to have the format of a URI
  • Add a single-row processing mode to libpq for better handling of large result sets

The official documentation for this version is available at: http://www.postgresql.org/docs/9.2/static/index.html

1126 questions
18
votes
3 answers

Updating a column with the results of a query in PostgreSQL

I have the following table in PostgreSQL 9.2 which contains time stamps: gid [PK] (bigserial), timestamp_mes (timestamp without time zone), time_diff (interval) 1, 2012-01-23 11:03:40, empty 2, 2012-01-23 11:03:42, empty 3, 2012-01-23 11:03:44,…
jatobat
  • 749
  • 3
  • 8
  • 21
17
votes
1 answer

Return SETOF rows from PostgreSQL function

I have a situation where I want to return the join between two views. and that's a lot of columns. It was pretty easy in sql server. But in PostgreSQL when I do the join. I get the error "a column definition list is required". Is there any way I can…
user433023
  • 237
  • 1
  • 4
  • 13
16
votes
2 answers

osm2pgsql: Function AddGeometryColumn doesn't exist

I want to import OSM file into my PostgreSQL database (Windows, Postgres Version 9.2) using the tool Osm2pgsql. When I run following command osm2pgsql.exe --create -d mydb artyom.xml -U myuser -W --style default.style I get the error SELECT…
Glory to Russia
  • 17,289
  • 56
  • 182
  • 325
14
votes
5 answers

How to cast varchar to boolean

I have a variable 'x' which is varchar in staging table, but it is set to boolean in target table which has 'true' and 'false' values. How can I convert varchar to boolean in postgresql?
Ramesh
  • 251
  • 2
  • 4
  • 12
14
votes
5 answers

Can't log into system user postgres

I installed postgresql on mac using brew. I wasn't asked about for the password for postgresql during the installation. Now I need to create a user and can't: Alexs-MacBook-Air:mydir alex$ su - postgres Password: su: Sorry Whatever password…
Incerteza
  • 32,326
  • 47
  • 154
  • 261
14
votes
4 answers

Where can I get pldbgapi.sql in order to install the debugger for postgresql?

I am trying to install the debugger for postgresql on Linux so I can use pgAdmin to debug my functions. I've set up my postgresql.conf file. However, I can't find pldbgapi.sql. Postgresql is installed at /usr/pgsql-9.2/. The directory…
Di Zou
  • 4,469
  • 13
  • 59
  • 88
14
votes
3 answers

Rails migration is always inserting whitespaces or changing the ordering of columns in the schema

There is a frustrating issue where my rails migrations update the schema with whitespaces and the position of the table's columns. So most times when I run bundle exec rake db:migrate it will do one of the below scenarios. When I merge this into our…
Coderama
  • 11,050
  • 12
  • 44
  • 58
14
votes
2 answers

PostgreSQL - properly change ID of table row

How to change id of some table's row? Like: UPDATE table SET id=10 WHERE id=5; But in way that it would cascade changes to every other table that references this table with that id? I want to do this, because I need to import data from another…
Andrius
  • 19,658
  • 37
  • 143
  • 243
13
votes
4 answers

Removing comments from pg_dump output

When a PostgreSQL pg_dump is done it inserts some comments for each element, as follows. -- -- Name: my_table; Type: TABLE; Schema: account; Owner: user; Tablespace: -- CREATE TABLE my_table( id integer ); -- -- Name: my_seq; Type: SEQUENCE;…
Marcio Mazzucato
  • 8,841
  • 9
  • 64
  • 79
13
votes
3 answers

Where in the DB is the Location of a postgres tablespace stored

I am using postgres 9.2 on redhat 6 this should be simple but I can't find it anywhere. I am looking for the database table and column which stores the Location for a postgres tablespace, I thought it would be in PG_TABLESPACE, but select * from…
davegreen100
  • 2,055
  • 3
  • 13
  • 24
13
votes
4 answers

"FATAL: the database system is shutting down" while creating connection to PostgreSQL

I am getting FATAL: the database system is shutting down errors while creating PostgreSQL JDBC connections to a PostgreSQL 9.2 server. The specific exception path I'm getting from JDBC is here: Caused by: org.postgresql.util.PSQLException: FATAL:…
Dan
  • 7,155
  • 2
  • 29
  • 54
13
votes
3 answers

Refer to a column by its number (index)

I want to perform a select using an index (number) of a column. I tried: select 1 from "user" select '1' from "user" but they don't do what I expect.
Incerteza
  • 32,326
  • 47
  • 154
  • 261
13
votes
2 answers

How to alter owner of a function in postgres

I am writing a script to alter all functions of postgres(changing owner of each function). I am able to list down all the function names using postgres query but not able to list parameters for each of those functions. My problem will be resolved if…
user1728310
  • 687
  • 2
  • 5
  • 14
13
votes
1 answer

Rails Postgis Upgrade Issues

I have installed Postgis and Postgresql through Homebrew on my Mac, and I wanted to upgrade my version of Postgis to 2.1.0-rc2. I unlinked my version of postgis, and then edited the postgis homebrew formula. After the new formula did not compile, I…
tomciopp
  • 2,602
  • 2
  • 31
  • 60
13
votes
1 answer

Low Postgres Cache Hit Ratio - data size or something else?

I've just upgraded my Heroku postgres database from the Kappa plan (800MB RAM, postgres 9.1) to the Ronin plan (1.7GB RAM, postgres 9.2), but performance has degraded. Following the guide here, I checked and the cache hit rate is even lower than it…
Daniel
  • 253
  • 1
  • 4
  • 10
1 2 3
…
75 76