Stack Overflow
Stack Overflow

Questions tagged [postgresql-9.1]

for PostgreSQL questions specific to version 9.1.

PostgreSQL 9.1 is the version of PostgreSQL released in September of 2011, with expected end-of-life in September of 2016.

While the generic tag should probably be used with all PostgreSQL-related questions, this tag should also be used if the question pertains to features added in version 9.1, or if the question is about a problem seen while running version 9.1.

1821 questions
72
votes
6 answers

How to use SQL LIKE condition with multiple values in PostgreSQL?

Is there any shorter way to look for multiple matches: SELECT * from table WHERE column LIKE "AAA%" OR column LIKE "BBB%" OR column LIKE "CCC%" This questions applies to PostgreSQL 9.1, but if there is a generic solution it would be…
sorin
  • 161,544
  • 178
  • 535
  • 806
71
votes
4 answers

Rename enum item in PostgreSQL

I would like to change the name of an item in an enum type in PostgreSQL 9.1.5. Here is the type's create stmt: CREATE TYPE import_action AS ENUM ('Ignored', 'Inserted', 'Updated', 'Task created'); I just want to change 'Task created' to…
David S
  • 12,967
  • 12
  • 55
  • 93
70
votes
2 answers

PL/pgSQL checking if a row exists

I'm writing a function in PL/pgSQL, and I'm looking for the simplest way to check if a row exists. Right now I'm SELECTing an integer into a boolean, which doesn't really work. I'm not experienced with PL/pgSQL enough yet to know the best way of…
nnyby
  • 4,748
  • 10
  • 49
  • 105
68
votes
2 answers

Postgresql insert trigger to set value

Assume in Postgresql, I have a table T and one of its column is C1. I want to trigger a function when a new record is adding to the table T. The function should check the value of column C1 in the new record and if it is null/empty then set its…
user1408470
  • 1,475
  • 3
  • 15
  • 21
66
votes
1 answer

Postgres SELECT ... FOR UPDATE in functions

I have two questions about using SELECT … FOR UPDATE row-level locking in a Postgres function: Does it matter which columns I select? Do they have any relation to what data I need to lock and then update? SELECT * FROM table WHERE x=y FOR…
Dan Taylor
  • 677
  • 1
  • 5
  • 6
65
votes
3 answers

How to handle special characters in the password of a Postgresql URL connection string?

Using a Postgresql URL connection string in the format of: postgresql://user:secret@localhost How do I handle special characters in that string (e.g., $) so that it will actually function when I connect to my postgres database? I've tried simply…
wgpubs
  • 8,131
  • 15
  • 62
  • 109
64
votes
7 answers

Easy way to view postgresql dump files?

I have a ton of postgresql dump files I need to peruse through for data. Do I have to install Postgresql and "recover" each one of them into new databases one by one? Or I'm hoping there's a postgresql client that can simply open them up and I can…
at.
  • 50,922
  • 104
  • 292
  • 461
62
votes
2 answers

How to create a new database with the hstore extension already installed?

Recently I went into trouble trying to use hstore with Django. I installed hstore this way: $ sudo -u postgres psql postgres=# CREATE EXTENSION hstore; WARNING: => is deprecated as an operator name DETAIL: This name may be disallowed altogether in…
Maxime R.
  • 9,621
  • 7
  • 53
  • 59
60
votes
4 answers

PostgreSQL: Show all the privileges for a concrete user

How to make a query to the Postgres data dictionary to find out all the privileges that a particular user has. I've been looking for a solution and I can not find anything. Thanks and good day
Python241820
  • 1,017
  • 1
  • 12
  • 16
55
votes
1 answer

Grant "create schema" ON database to a user?

I'm using PostgreSQL 9.1 and have to to grant "create schema" ON database to a user. How can I do that?
matheusvmbruno
  • 2,140
  • 3
  • 15
  • 20
50
votes
3 answers

extract week number from date postgres

I would like to extract the week number as: 2015-52 from a date formatted as: 2015-12-27 How can I perform this in postgres? my weeks are calculated from monday to sunday.
chopin_is_the_best
  • 1,951
  • 2
  • 23
  • 39
50
votes
4 answers

Postgres query optimization (forcing an index scan)

Below is my query. I am trying to get it to use an index scan, but it will only seq scan. By the way the metric_data table has 130 million rows. The metrics table has about 2000 rows. metric_data table columns: metric_id integer , t timestamp , d…
Jeff
  • 873
  • 2
  • 10
  • 16
48
votes
8 answers

PostgreSQL next value of the sequences?

I am using PostgreSQL for my Codeigniter website. I am using grocery crud for add, edit and delete operations. While doing an edit or add, I want to rename an uploaded file dynamically based on the id of the content. I am able to do this using…
i_nomad
  • 795
  • 2
  • 7
  • 9
47
votes
2 answers

Update with result from CTE

I want to update job date if any records is an earlier date. Trying to use CTE to achieve this: CREATE TABLE job (jobid int4, jobdate date); INSERT INTO job (jobid, jobdate) VALUES (1, '2016-02-01'), (2, '2016-02-01'), (3,…
sibert
  • 1,968
  • 8
  • 33
  • 57
46
votes
3 answers

Printing to the screen in a .sql file in PostgreSQL

I have a .sql file I am building for an upgrade to my application that alters tables, inserts/updates, etc. I want to write to the screen after every command finishes. So, for instance if I have something like: insert into X... I want to see…
El Guapo
  • 5,581
  • 7
  • 54
  • 82
1 2
3
…
99 100