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
1960
votes
9 answers

How to exit from PostgreSQL command line utility: psql

What command or short key can I use to exit the PostgreSQL command line utility psql?
App Work
  • 21,899
  • 5
  • 25
  • 38
526
votes
6 answers

List tables in a PostgreSQL schema

When I do a \dt in psql I only get a listing of tables in the current schema (public by default). How can I get a list of all tables in all schemas or a particular schema?
Nyxynyx
  • 61,411
  • 155
  • 482
  • 830
464
votes
7 answers

must appear in the GROUP BY clause or be used in an aggregate function

I have a table that looks like this caller 'makerar' cname wmname avg canada zoro 2.0000000000000000 spain luffy 1.00000000000000000000 spain usopp 5.0000000000000000 And I want to select the maximum avg for each cname. SELECT cname,…
RandomGuy
  • 4,949
  • 4
  • 16
  • 15
288
votes
16 answers

I forgot the password I entered during PostgreSQL installation

I either forgot or mistyped (during the installation) the password to the default user of PostgreSQL. I can't seem to be able to run it, and I get the following error: psql: FATAL: password authentication failed for user "hisham" hisham-agil:…
hilarl
  • 6,570
  • 13
  • 48
  • 57
245
votes
8 answers

PostgreSQL ERROR: canceling statement due to conflict with recovery

I'm getting the following error when running a query on a PostgreSQL db in standby mode. The query that causes the error works fine for 1 month but when you query for more than 1 month an error results. ERROR: canceling statement due to conflict…
AnApprentice
  • 108,152
  • 195
  • 629
  • 1,012
237
votes
4 answers

How do I convert an integer to string as part of a PostgreSQL query?

How do I convert an integer to string as part of a PostgreSQL query? So, for example, I need: SELECT * FROM table WHERE = 'string of numbers' where can be anywhere from 1 to 15 digits long.
spyd3rr
  • 2,745
  • 2
  • 16
  • 14
222
votes
11 answers

How to add column if not exists on PostgreSQL?

Question is simple. How to add column x to table y, but only when x column doesn't exist ? I found only solution here how to check if column exists. SELECT column_name FROM information_schema.columns WHERE table_name='x' and column_name='y';
marioosh
  • 27,328
  • 49
  • 143
  • 192
219
votes
9 answers

Change type of varchar field to integer: "cannot be cast automatically to type integer"

I have a small table and a certain field contains the type "character varying". I'm trying to change it to "Integer" but it gives an error that casting is not possible. Is there a way around this or should I just create another table and bring the…
itsols
  • 5,406
  • 7
  • 51
  • 95
201
votes
7 answers

Store query result in a variable using in PL/pgSQL

How to assign the result of a query to a variable in PL/pgSQL, the procedural language of PostgreSQL? I have a function: CREATE OR REPLACE FUNCTION test(x numeric) RETURNS character varying AS $BODY$ DECLARE name character varying(255); begin …
Sathish
  • 4,403
  • 7
  • 31
  • 53
199
votes
1 answer

Query a parameter (postgresql.conf setting) like "max_connections"

Does anyone know if it's even possible (and how, if yes) to query a database server setting in PostgreSQL (9.1)? I need to check the max_connections (maximum number of open db connections) setting.
Greg Kramida
  • 4,064
  • 5
  • 30
  • 46
168
votes
4 answers

ALTER TABLE, set null in not null column, PostgreSQL 9.1

I have a table with not null column, How to set a null value in this column as default? I mean, I want to do something like this: postgres=# ALTER TABLE person ALTER COLUMN phone SET NULL; but it shows: postgres=# ALTER TABLE person ALTER COLUMN…
Brian Brown
  • 3,873
  • 16
  • 48
  • 79
167
votes
9 answers

how to exclude null values in array_agg like in string_agg using postgres?

If I use array_agg to collect names, I get my names separated by commas, but in case there is a null value, that null is also taken as a name in the aggregate. For example : SELECT g.id, array_agg(CASE WHEN g.canonical = 'Y' THEN g.users ELSE…
Daud
  • 7,429
  • 18
  • 68
  • 115
148
votes
6 answers

How to find a table having a specific column in postgresql

I'm using PostgreSQL 9.1. I have the column name of a table. Is it possible to find the table(s) that has/have this column? If so, how?
QuestionEverything
  • 4,809
  • 7
  • 42
  • 61
145
votes
4 answers

Generating time series between two dates in PostgreSQL

I have a query like this that nicely generates a series of dates between 2 given dates: select date '2004-03-07' + j - i as AllDate from generate_series(0, extract(doy from date '2004-03-07')::int - 1) as i, generate_series(0, extract(doy from…
f.ashouri
  • 5,409
  • 13
  • 44
  • 52
127
votes
6 answers

Postgres ENUM data type or CHECK CONSTRAINT?

I have been migrating a MySQL db to Pg (9.1), and have been emulating MySQL ENUM data types by creating a new data type in Pg, and then using that as the column definition. My question -- could I, and would it be better to, use a CHECK CONSTRAINT…
punkish
  • 13,598
  • 26
  • 66
  • 101
1
2 3
…
99 100