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
10
votes
1 answer

Postgres 9.2 PL/pgSQL simple update in loop

I have a following table: +----+----------+----------+ | id | trail_id | position | +----+----------+----------+ | 11 | 16 | NULL | | 12 | 121 | NULL | | 15 | 121 | NULL | | 19 | 42 | NULL | | 20 | 42 |…
ellmo
  • 906
  • 7
  • 18
9
votes
4 answers

How do you view new sequence ownership information in Postgres after using ALTER SEQUENCE?

I'm using "ALTER SEQUENCE sequence OWNED BY table.column" to change sequence association in Postgres. Is there a way to view this new information using \ds or something similar? \ds still shows the table owner as the sequence owner.
mpso
  • 1,129
  • 2
  • 13
  • 26
9
votes
2 answers

NOT NULL constraint over a set of columns

I have a table in Postgres which currently has a NOT NULL constraint on it's email column. This table also has a phone column which is optional. I would like the system to accept some records without email but only if these have phone as NOT NULL.…
rhetonik
  • 1,818
  • 1
  • 15
  • 21
9
votes
2 answers

getting multiple queries with “show transaction isolation level” in pg_activity

i am using postgres db server for my production use. when i fire a query select * from pg_stat_activity on my postgresql server, so i getting 98% of queries like "SHOW TRANSACTION ISOLATION LEVEL" and my postgresql server accepts only 100…
Yogesh Prajapati
  • 4,770
  • 2
  • 36
  • 77
9
votes
1 answer

Easy way to have return type be SETOF table plus additional fields?

I'm writing a PL/pgSQL stored procedure that will return a set of records; each record contains all the fields of an existing table (call it Retailer, which has two fields: retailer_key and retailer_name). This, of course, works: CREATE FUNCTION…
MichaelF
  • 149
  • 1
  • 8
9
votes
1 answer

Get all procedural , user defined functions

How to get list of all user defined functions via SQL query ? I find this code here SELECT p.proname, p.pronargs, t.typname FROM pg_proc p, pg_language l, pg_type t WHERE p.prolang = l.oid and p.prorettype = t.oid and l.lanname = 'c' ORDER BY…
Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236
9
votes
1 answer

Use cases for hstore vs json datatypes in postgresql

In Postgresql, the hstore and json datatypes seem to have very similar use cases. When would you choose to use one vs. the other? Initial thoughts: You can nest with json; you can't with hstore Functions for parsing json won't be available until…
alan
  • 4,247
  • 7
  • 37
  • 49
9
votes
2 answers

String concatenation within an exception

In my trigger procedures I use RAISE EXCEPTION for messages. I have no problem with simple messages, but if I want to give the user some more complex feedback, I face a problem: the concatenation operator doesn't work within RAISE EXCEPTION…
Pavel V.
  • 2,653
  • 10
  • 43
  • 74
9
votes
2 answers

Using postgresql xml data type with sqlalchemy

SqlAlchemy supports most database specific data types via dialects, but I could not find anything to work with the postgresql xml column type. Does somebody know a working solution. Idealy it should not require a custom column type implementation by…
Achim
  • 15,415
  • 15
  • 80
  • 144
9
votes
2 answers

psql - how to flush database content without dropping table

I have a table in my db called 'mytable'. I'd like to clear it so that I can continue to collect and analyze 'fresh data' from it. Something like conn = psycopg2.connect(database = mydb_name, host = mydb_server, user = mydb_uname, password =…
jml
  • 1,745
  • 6
  • 29
  • 55
9
votes
1 answer

NULL vs. `infinity` in PostgreSQL range types

What is the meaning of 'infinity' in PostgreSQL range types? Is there any difference between specifying infinity or -infinity as a bound, or NULL? I.e. is infinity an explicit form of specifying that the range bound is infinite, whereas NULL would…
Cochise Ruhulessin
  • 1,001
  • 1
  • 11
  • 15
9
votes
2 answers

Prefetch column sequence SQLAlchemy

My model is quite complex and I'm trying to take logic from existing stored procedures and convert them to SQLAlchemy (for portability reasons). I'm struggling however with uncommitted data. I have user table: 1d, name I have status table: id,…
Trent
  • 2,909
  • 1
  • 31
  • 46
9
votes
1 answer

Entity Framework 5.0 PostgreSQL (Npgsql) default connection factory

I'm trying to get EF 5.0 code first working with PostgreSQL (Npgsql provider). I have Npgsql 2.0.12.1 installed via NuGet (referenced assembly is 2.0.12.0 though). I have Npgsql declared in app.config (both default connection factory and provider…
Jaded
  • 1,802
  • 6
  • 25
  • 38
9
votes
2 answers

PostgreSQL data file location

I am using PostgreSQL on Mac, I have created several databases using command line. I am looking into /Library/PostgreSQL/9.2/data and I can not see any .dat file. Am I looking into the wrong place for data files? I have used the following command:…
sheidaei
  • 9,842
  • 20
  • 63
  • 86
8
votes
2 answers

Airflow psycopg2.OperationalError: FATAL: sorry, too many clients already

I have a four node clustered Airflow environment that's been working fine for me for a few months now. ec2-instances Server 1: Webserver, Scheduler, Redis Queue, PostgreSQL Database Server 2: Webserver Server 3: Worker Server 4: Worker Recently…
Kyle Bridenstine
  • 6,055
  • 11
  • 62
  • 100