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

How to change schema of multiple PostgreSQL tables in one operation?

I have a PostgreSQL 9.1 database with 100 or so tables that were loaded into the 'public' schema. I would like to move those tables (but not all of the functions in 'public') to a 'data' schema. I know that I can use the following to move 1 table…
RyanKDalton
  • 1,271
  • 3
  • 14
  • 30
34
votes
2 answers

now() default values are all showing same timestamp

I have created my tables with a column (type: timestamp with timezone) and set its default value to now() (current_timestamp()). I run a series of inserts in separate statements in a single function and I noticed all the timestamps are equal down…
Ryan Fisch
  • 2,614
  • 5
  • 36
  • 57
32
votes
2 answers

Moving average in postgresql

I have the following table in my Postgresql 9.1 database: select * from ro; date | shop_id | amount -----------+----------+-------- 2013-02-07 | 1001 | 3 2013-01-31 | 1001 | 2 2013-01-24 | 1001 | 1 2013-01-17 | …
Glicious
  • 421
  • 1
  • 5
  • 13
32
votes
6 answers

How do I merge two tables in postgresql?

I have two tables table 1: name| count xxx | 1 yyyy | 2 zzzz | 3 table 2: name |count xxx | 1 aaa | 5 I want the resulting table to be like the following table: name | count xxx | 1 yyyy | 2 zzzz | 3 aaa | 5 Does anyone know how to do this?
user1897937
  • 379
  • 1
  • 4
  • 9
32
votes
1 answer

PostgreSQL 9.1: How to concatenate rows in array without duplicates, JOIN another table

I am using PostgreSQL 9.1 and need help with concatenating multiple rows in one. I need to do that in 2 tables. When I use two times array_agg() functions I get duplicated values in result. Tables: CREATE TABLE rnp (id int, grp_id int, cabinets…
lara80
  • 393
  • 2
  • 5
  • 12
32
votes
1 answer

PostgreSQL multiple authentication methods

How can I set up multiple authentication methods for the same host/database/user rule? I want to be able to log in to my postgres user using both sudo -u postgres psql -U postgres (without having to enter a PostgreSQL password) and psql -U postgres…
Waz
  • 653
  • 6
  • 12
31
votes
1 answer

storing year in database

What is the best PostgreSQL data type for year data, e.g., 2006 or 1847. TEXT, SMALLINT, DATE? Ideally, I'd like to be able to query that column with a second year-month-day column (in DATE format).
metasequoia
  • 7,014
  • 5
  • 41
  • 54
30
votes
5 answers

How to allow only one row for a table?

I have one table in which I would like only one entry. So if someone is trying to insert another row it shouldn't be allowed, only after someone deleted the previously existing row. How do I set a rule for a table like this?
X-jo
  • 491
  • 1
  • 5
  • 13
30
votes
3 answers

How to set up Postgres database for local Rails project?

I recently got a new machine and would now like to work on my projects from Github. I'm curious as to how to properly set up the Postgres database on my local machine. I have postgresql, pgadmin3 and libpq-dev installed on Ubuntu (12.04). I pull…
Connor Leech
  • 18,052
  • 30
  • 105
  • 150
30
votes
4 answers

to_char(number) function in postgres

i want to display/convert a number to character (of it's same length) using to_char() function . In oracle i can write like SELECT to_char(1234) FROM DUAL But in postgres SELECT to_char(1234) is not working.
jobi88
  • 3,865
  • 8
  • 21
  • 15
30
votes
4 answers

Postgres analogue to CROSS APPLY in SQL Server

I need to migrate SQL queries written for MS SQL Server 2005 to Postgres 9.1. What is the best way to substitute for CROSS APPLY in this query? SELECT * FROM V_CitizenVersions CROSS APPLY dbo.GetCitizenRecModified(Citizen,…
user1178399
  • 1,028
  • 8
  • 17
  • 32
29
votes
2 answers

If PostgreSQL count(*) is always slow how to paginate complex queries?

If PostgreSQL's count(*) is always slow how to paginate complex queries? Making triggers doesn't seem to be a good solution as long as in this case we have a lot of pages (for example different categories, filters, etc). What to do if VACUUM/VACUUM…
Daniil Ryzhkov
  • 7,416
  • 2
  • 41
  • 58
28
votes
1 answer

Fixing error "function array_length(bigint[]) does not exist"

I have a recursive query, encapsulated within a function, that returns a hierarchical survey "structure": CREATE OR REPLACE FUNCTION get_survey_results(IN v_survey_id integer DEFAULT 1, IN v_survey_session_id integer DEFAULT NULL) RETURNS…
Jeromy French
  • 11,812
  • 19
  • 76
  • 129
28
votes
6 answers

Alphanumeric case in-sensitive sorting in postgres

I am new to postrges and want to sort varchar type columns. want to explain the problem with with below example: table name: testsorting order name 1 b 2 B 3 a 4 a1 5 …
akhi
  • 664
  • 2
  • 10
  • 23
28
votes
7 answers

How to restore a single table from a .sql postgresql backup?

A table's rows were mistakenly deleted from the database. We have a db backup which results in a sql file that can restored like so: psql -h localhost -d proddump -f /Users/U/Desktop/prod_db_backup/PostgreSQL/site_prod.sql This ends up doing a full…
AnApprentice
  • 108,152
  • 195
  • 629
  • 1,012
1 2 3
…
99 100