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
41
votes
6 answers

How to create sequence if not exists

I tried to use code from Check if sequence exists in Postgres (plpgsql). To create sequence if it does not exists. Running this code two times causes an exception: sequence ... already exists. How to create sequence only if it does not exist? If…
Andrus
  • 26,339
  • 60
  • 204
  • 378
36
votes
4 answers

ROWID equivalent in postgres 9.2

Is there any way to get rowid of a record in postgres?? In oracle i can use like SELECT MAX(BILLS.ROWID) FROM BILLS
jobi88
  • 3,865
  • 8
  • 21
  • 15
32
votes
1 answer

If I drop a PG table with an index, does the index still exist?

If I'm dropping and re-creating a Postgres table that is indexed, will the index still exist? Do I need to drop and re-index the table after re-creating it or do I just need to re-create the index on the new table and leave the cleaning up of the…
Alan Kavanagh
  • 9,425
  • 7
  • 41
  • 65
32
votes
6 answers

PostgreSQL PL/pgSQL random value from array of values

How can I declare an array like variable with two or three values and get them randomly during execution? a := [1, 2, 5] -- sample sake select random(a) -- returns random value Any suggestion where to start?
Mo J. Mughrabi
  • 6,747
  • 16
  • 85
  • 143
29
votes
1 answer

How to alter type and remove value in postgresql

I found how to add value to the TYPE. But how can I remove value from it? For example I have TYPE with enum values ('A','B','C'). How to remove 'C'?
Alex
  • 11,451
  • 6
  • 37
  • 52
28
votes
12 answers

The local psql command could not be located

I'm following the instructions found here. When I try to run $ heroku pg:psql or $ heroku pg:psql HEROKU POSTGRESQL_BROWN I recieve the following error message: ! The local psql command could not be located ! For help installing psql, see…
TheMarron
  • 315
  • 1
  • 6
  • 13
27
votes
1 answer

Call a function for each row in select - Postgres

I have a function called "getList(date)". This function returns me a items list (with several columns) from the date inputted in the parameter. If I call: SELECT * FROM getList('12/31/2014'); It works fine. It returns me a list with the date, the…
Gilbert
  • 443
  • 1
  • 4
  • 11
26
votes
3 answers

Select query in row_to_json function

For example , I use the following function to convert rows into json in PostgreSQL 9.2 select row_to_json(row(productid, product)) from gtab04; and this will returns below results row_to_json --------------- {"f1":3029,"f2":"DIBIZIDE M…
user3814846
26
votes
3 answers

PostgreSQL - change precision of numeric?

I tried to change precision like this: ALTER Table account_invoice ALTER amount_total SET NUMERIC(5); But I get syntax error, so I'm clearly doing something wrong. What is the right syntax to change precision of numeric in PostgreSQL?
Andrius
  • 19,658
  • 37
  • 143
  • 243
25
votes
3 answers

Raise notice to print a table's data

I'd like to be able to print some debug information from sql script / function. Of course, I can do this by RAISE NOTICE 'hello!' But I also need to print a whole table's data. This doesn't work: RAISE NOTICE '%' (SELECT * FROM table1) Is it…
Incerteza
  • 32,326
  • 47
  • 154
  • 261
25
votes
1 answer

Alter default privileges for a group role in PostgreSQL

I have created two group roles in Postgres 9.2: one is called admins and the other is called readers. The idea is very simple: admins create tables and readers have read access to these tables. After granting privileges to both group roles…
juliomalegria
  • 24,229
  • 14
  • 73
  • 89
25
votes
2 answers

Check for value with current_setting()

I'm trying to work with current_setting(). I came up with this: CREATE OR REPLACE FUNCTION process_audit() RETURNS TRIGGER AS $audit$ DECLARE user_id integer; BEGIN BEGIN user_id :=…
Alexander
  • 1,495
  • 2
  • 19
  • 24
25
votes
4 answers

How to insert data into table using stored procedures in postgresql

CREATE TABLE app_for_leave ( sno integer NOT NULL, eid integer, ename varchar(20), sd date, ed date, sid integer, status boolean DEFAULT false, CONSTRAINT pk_snoa PRIMARY KEY (sno) ); Basic Insertion is :: INSERT INTO…
09Q71AO534
  • 4,300
  • 13
  • 44
  • 68
25
votes
1 answer

Conversion array types

I have in table column, which type is CHARACTER VARYING[] (that is array) I need concatenate existed rows whith other array This is my code: UPDATE my_table SET col = array_cat(col, ARRAY['5','6','7']) returned error: function…
Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236
25
votes
1 answer

Changing a column from string to string array in postgresql

The following is a snippet of a table called "containers". Column | Type | Modifiers --------------------+-----------------------------+--------------------------------- id …
paddle42380
  • 6,921
  • 7
  • 32
  • 40
1 2
3
…
75 76