Stack Overflow
Stack Overflow

Questions tagged [postgres-9.4]

13 questions
9
votes
1 answer

Postgres: remove check constraint on varchar column

I'm coming from a MySQL world and am having a hard time doing things in PostgreSQL. I have a column which looks like this in my GUI client: I'm not sure if it is an enum column, or a varchar with a constraint (are they the same thing in…
Zaki Aziz
  • 3,592
  • 11
  • 43
  • 61
3
votes
2 answers

How can Postgres unpack a json array into a column then re-pack it, but preserve the data type?

Here is some data: create table t (id numeric, ja json, cr timestamp with time zone); insert into t values (1,'[1,2,3]','2019-01-01 00:00:00+0'), (1,'[3,4,5]','2019-02-01 00:00:00+0'), (2,'["a","b"]','2019-01-01 00:00:00+0'); Here is a query that…
Caius Jard
  • 72,509
  • 5
  • 49
  • 80
3
votes
2 answers

pg_restore: [archiver] unsupported version (1.13) in file header

C:\Program Files (x86)\pgAdmin III\1.22\pg_restore.exe --host localhost --port 5432 --username "postgres" --dbname "randd" --role "postgres" --no-password --verbose "C:\Users\ranjeet\Desktop\RandDbackup19-3final.backup" pg_restore: [archiver]…
Ranjeet Chaurasia
  • 31
  • 1
  • 1
  • 2
3
votes
2 answers

BTREE vs GIN vs GIST index

I'm using Postgres DB and I have a table called MyObjects with several varchar columns. The values will not be separated words (for example, urls, names etc..). I will be filtering according to these columns a lot. Searching rows that a certain…
Mr T.
  • 4,278
  • 9
  • 44
  • 61
2
votes
2 answers

how to query for min or max inet/cidr with postgres

Consider query: select min(d) from temp; select max(d) from temp; Either one, I get an error like: # select max(d) from temp; ERROR: function max(inet) does not exist LINE 1: select max(d) from temp; ^ HINT: No function matches the…
Greg
  • 6,571
  • 2
  • 27
  • 39
1
vote
1 answer

How can I get the host name and query that has been executed on the slave postgres database that caused the system into the memory crashed

My slave database has undergone the memory crash(Out of memory) and in recovery stage. I want to know the query that causes this issue. I have checked logs I get one query just before the system goes into the recovery mode;But I want to confirm…
shruti garg
  • 177
  • 1
  • 14
1
vote
0 answers

Database design for multiple offers - postgres 9.5

I have a booking system which currently supports a single discount for each reservation. I want to extend that and support multiple offers per reservation. The purpose is for the user to be able to select one of two types of offers on checkout: …
mallix
  • 1,399
  • 1
  • 21
  • 44
1
vote
3 answers

Postgresql doesn't use index

I have large table crumbs (about 100M+ rows, 100GB). It's just collection of json stored as text. It has index on column run_id that has about 10K unique values. So each run is small (1K - 1M rows). For simple query: explain analyze verbose select *…
Tomas Vitulskis
  • 75
  • 1
  • 5
0
votes
0 answers

Postgres picks sequential scan instead of a much faster index scan on multiple INNER JOINs

We have the following query: SELECT ... FROM Client c INNER JOIN Address a1 ON c.Address1 = a1.Id INNER JOIN Address a2 ON c.Address2 = a2.Id INNER JOIN Address a3 ON c.Address3 = a3.Id WHERE c.Status = 0 AND a1.Status = 0 AND a2.Status = 0 AND…
Jonas Sourlier
  • 13,684
  • 16
  • 77
  • 148
0
votes
1 answer

Append JSON element to array using postgres 9.4

I am attempting to append a JSON element to an already existing array in my database. I know about jsonb_set however I can't upgrade to Postgres 9.4 as this is a groovy project and the latest postgresql version on Maven is 9.4. I currently have a…
N P
  • 2,319
  • 7
  • 32
  • 54
0
votes
1 answer

Postgres max field length issue

We are trying to load some data in postgres The problem is data is huge that its exceeding character varying(10485760) limit. Is there any possible way to increase this limit? Thanks,
Jagdeep Bhardwaj
  • 1
  • 1
0
votes
1 answer

Rails add_column with a limit and default with Postgres Function

I'm trying to add a column that defaults to a generated UUID without the hyphens as a varchar(32). Currently this is what my migration has: add_column :users, :uuid, :string, limit: 32, default: "REPLACE(uuid_generate_v4(), '-', '')" But it seems…
Tom Prats
  • 7,364
  • 9
  • 47
  • 77
0
votes
1 answer

How can I delete rows from one table and insert into another using SQLAlchemy and Postgres RETURNING?

I would like to move rows from one table to another using SQLAlchemy with a Postgres database (there are other questions on Stack Overflow about moving data but they don't focus on using SQLAlchemy for this). The approach is to use DELETE with…
Simeon Visser
  • 118,920
  • 18
  • 185
  • 180