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
87
votes
2 answers

Size limit of JSON data type in PostgreSQL

Does anyone know what is the limit on the size of JSON data type in PostgreSQL 9.2?
ankurvsoni
  • 2,064
  • 3
  • 18
  • 22
84
votes
7 answers

PostgreSQL 9.2 - Convert TEXT json string to type json/hstore

I have a TEXT column containing valid JSON string. CREATE TABLE users(settings TEXT); INSERT INTO users VALUES ('{"language":"en","gender":"male"}'); INSERT INTO users VALUES ('{"language":"fr","gender":"female"}'); INSERT INTO users VALUES…
huy
  • 4,782
  • 6
  • 36
  • 42
84
votes
3 answers

Find and delete duplicate rows with PostgreSQL

We have a table of photos with the following columns: id, merchant_id, url this table contains duplicate values for the combination merchant_id, url. so it's possible that one row appears more several times. 234 some_merchant …
schlubbi
  • 1,623
  • 2
  • 13
  • 17
63
votes
3 answers

How to create a user for Postgres from the command line for bash automation

I am using Ubuntu 12.04 and Postgress 9.2. I need to create this user with this password e.g. postgres://admin:test101@127.0.0.1:5432 How to do that from the command line? I need to automate with a bash script. I have a fresh install.
Tampa
  • 75,446
  • 119
  • 278
  • 425
61
votes
2 answers

Dropping column in Postgres on a large dataset

So I have a table with a large dataset and this table has a three columns that I would like to drop. The question is: how will Postgres deal with it? Will it walk through every entry or will it just update mapping info without much overhead? Can I…
nikita2206
  • 1,129
  • 2
  • 10
  • 17
57
votes
2 answers

PostgreSQL - set a default cell value according to another cell value

If i have a column say column a of any given values, and i want another column column b to have a default value according to the value of column a In another words: if column a = 'peter' then column b default value = 'doctor'.
mosid
  • 1,044
  • 1
  • 9
  • 15
57
votes
3 answers

What is the maximum number of columns in a PostgreSQL select query

Do you know what the maximum number of columns that can be queried in Postgresql? I need to know this before I start my project.
Luke101
  • 63,072
  • 85
  • 231
  • 359
53
votes
1 answer

How to append a new item into the array-type column in PostgreSQL

Im using PostgreSQL for my application, The task will be like this There are users who use my app, and I need to maintain notification for each of them based on their activity so I get lots and lots of notifications for each user. so in general we…
CodeRows
  • 933
  • 1
  • 9
  • 15
50
votes
2 answers

How to change the template database collection coding

I want build new postgreSQL database by: CREATE DATABASE newdb WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'zh_CN.UTF-8' CONNECTION LIMIT = -1; and the error is: ERROR: new collation (zh_CN.UTF-8) is…
user504909
  • 9,119
  • 12
  • 60
  • 109
49
votes
2 answers

what is the maximum length of varchar(n) in postgresql 9.2 and which is best to use varchar(n) or text?

Hi I am using postgresql 9.2 and I want to use varchar(n) to store some long string but I don't know the maximum length of character which varchar(n) supports. and which one is better to use so could you please suggest me? thanks
KRISHNA
  • 611
  • 1
  • 5
  • 7
49
votes
1 answer

Difference between RESTRICT and NO ACTION

From postgresql documentation: RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the constraint is checked, an error is raised; this is the default behavior if you do not specify anything.…
Jeriho
  • 7,129
  • 9
  • 41
  • 57
44
votes
1 answer

How to set application name in a Postgresql JDBC url?

I want to set the application name of the connections of my application. So when I list the rows in pg_stat_activity I can have a non empty application_name column. I have setup the following JDBC url for connecting to my Postgresql…
Stephan
  • 41,764
  • 65
  • 238
  • 329
44
votes
3 answers

How can I run an ad-hoc script in PostgreSQL?

I'm trying to run this in PostgreSQL 9.2: RAISE NOTICE 'Hello, World!'; And the server says: Error : ERROR: syntax error at or near "RAISE" LINE 1: RAISE NOTICE 'Hello, World!' ^ Why?
yegor256
  • 102,010
  • 123
  • 446
  • 597
42
votes
3 answers

Creating partial unique index with sqlalchemy on Postgres

SQLAlchemy supports creating partial indexes in postgresql. Is it possible to create a partial unique index through SQLAlchemy? Imagine a table/model as so: class ScheduledPayment(Base): invoice_id = Column(Integer) is_canceled =…
brianz
  • 7,268
  • 4
  • 37
  • 44
41
votes
2 answers

PostgreSQL cannot begin/end transactions in PL/pgSQL

I am seeking clarification of how to ensure an atomic transaction in a plpgsql function, and where the isolation level is set for this particular change to the database. In the plpgsql function shown below, I want to make sure that BOTH the deletion…
Tim
  • 8,669
  • 31
  • 105
  • 183
1
2
3
…
75 76