Questions tagged [postgresql]

29508 questions
1

votes
1

answer
29

Views

Select object that has no pointer

I have 2 entities named ContentState and HashedFile. Each record of ContentState has a HashedFile linked to it but the relationship is not unique and so there is a many to one relation regarding ContentState with unique="false" and not-null="true" (thus, 2 or more ContentState records can point to t...
little ali
1

votes
2

answer
881

Views

How to optimize/cache Rails query

I've Rails application where query takes way too long time. It uses postgresql DB and query consists of one table with thousands of records. statistics_controller.rb all_data = Usagedata.select([:start_time, :end_time, :node_count, :processors, :id, :wall_duration, :local_user_id]) .where(Usagedata...
Raccoon
1

votes
1

answer
6.3k

Views

Postgres windows function with aggregate group by

I want to get a list of email domains and the top user within each domain. My approach is to sum the questions per email grouped by the domain and then get the top user with a window function. However this does not work: SELECT domain, sum(questions_per_email) as questions_per_domain, first_value(e...
Kristoffer
0

votes
0

answer
22

Views

How to escape variable in a postgresql insert query into a python script?

I have an issue with a Python script and i cannot find answers into the web, or i cannot implement them or they doesn't work for me :/ I have a Python script and into it i have an INSERT query for a Postgresql database. This is the query : print('Insert Site') with conn.cursor() as cur: cur.execute...
Thomas MOREAU
1

votes
1

answer
12

Views

Does order of columns in foreign key definition matter?

Given the following primary key definition (note the order of the columns) on table1: ALTER TABLE "table1" ADD PRIMARY KEY ("f1", "f4", "f2", "f3"); Further given the following foreign key definition (note the order of the columns) on table2: ALTER TABLE "table2" ADD CONSTRAINT "const1" FOREIGN KEY...
user3193317
0

votes
0

answer
5

Views

Spring JPA Criteria API search in list of names by json list

I am trying to create JPA Specification that search in list by json list @Override public Predicate toPredicate(Root root, CriteriaQuery criteriaQuery, CriteriaBuilder builder) { Expression unitsNames = builder.function("json_array_elements", String.class, root.get("unitsNames")); return builder....
Mohamed Adel
0

votes
0

answer
3

Views

Equivalent for DBMS_STATS.GATHER_SCHEMA_STATS(ownname ,options => ); in postgres

Is there any equivalent for DBMS_STATS.GATHER_SCHEMA_STATS(ownname ,options ); in postgres ?
Sanjum
1

votes
2

answer
2.2k

Views

What causes Postgresql connections to get stuck on BIND with server at 100% CPU?

One of our Postgresql 9.2.4 database servers has got "stuck". The are 20 connections to it showing up in htop as stuck on BIND and the server is at 100% CPU and load avg of 20 or so: postgres: chicken accounts x.x.x.x (46317) BIND Update: It turns out that we had a bunch of simple selects with long...
David Tinker
1

votes
1

answer
796

Views

Migrating Spring application from Postgres to Oracle: defining table's schema

FINAL EDIT The reason why I didn't see any logs is that I was using an old version of log4j. Hibernate hbm2ddl uses slf4j for logging and so all the logs were ignored by log4j 1. Now I upgraded to log4j2 that has a slf4j bridge and I see all the errors in the log. Godd new is that from here I can pi...
malavock
1

votes
1

answer
127

Views

Ruby app pushed to Heroku displaying issues

I am running a Ruby on Rails application with a PostgreSQL database. This is the first time I pushed an app with its database to heroku, so I had no idea what to expect. What I got was my main page working as intended; but the Users page (page created by Rails scaffold command wich should display da...
Nuno Roma
1

votes
1

answer
351

Views

Scope of transactions and prepared statements

I wonder how transactions and prepared statements are scoped. With scoped, I mean if they are bound to a specific connection or environment in wich they were created. More specifically: Can a prepared statement be reused on a connection other the one it was created on? Can a prepared statement be us...
Erik Aigner
1

votes
1

answer
1.4k

Views

link to libpq failed with unresolved symbol _PQconnectdb

I use Qt5, msvc2010, windows7 64bit. I want to test if i can link libpq.lib. http://www.postgresql.org/docs/9.2/static/libpq-example.html I build the above example in QtCreator. And get error LNK2019: unresolved external symbol _PQconnectdb in function "_main" I check my libpq.lib use dumpbin libp...
Aylwyn Lake
1

votes
2

answer
2.1k

Views

what is the default value for “Timestamp with zone” in pgsql?

I am using following code to have default value if column has null value. PostgreSQL version : 9.3 COALESCE(LastLoginTimestamp,'') Error: invalid input syntax for type timestamp with time zone: "" What I need to do in order to have default/empty value if LastLoginTimestamp is null.
AnandSonake
1

votes
2

answer
1.4k

Views

Create PostgreSQL Database with case-sensitive name using npgsql

I'm trying to create a PostgreSQL database using the npgsql library, but the database name always ends up in lower case. Is it possible to preserve the case or should I just lower case everything? I want the database name to be MyDatabase, but it always ends up as mydatabase. string server = "localh...
Christopher Tso
1

votes
2

answer
4k

Views

Why can't you start postgres in docker using “service postgres start”?

All the tutorials point out to running postgres in the format of docker run -d -p 5432 \ -t /postgresql \ /bin/su postgres -c '/usr/lib/postgresql/9.2/bin/postgres \ -D /var/lib/postgresql/9.2/main \ -c config_file=/etc/postgresql/9.2/main/postgresql.conf' Why can't we in our Docker file have: ENTR...
Reza S
1

votes
2

answer
575

Views

Where in the system catalog is the function body stored?

I am trying to build a documentation system for my database and I would like to include the source of my functions and triggers. I managed to find all of the metadata on functions and triggers, but where is the actual body of these functions and triggers stored?
Patrick
0

votes
0

answer
16

Views

PostgreSQL Database Log Transaction

I'm a last year college student and I'm doing my thesis right now. My title is "Index Suggestion based on Log Analysis". This project will analyze the PostgreSQL transaction log to give index recommendation to the database that will be tested. This research will develop an index recommender tool by...
wdwilhelmina
0

votes
0

answer
4

Views

What does tojson filter do in jinja2 template

var data = JSON.parse('{{data | tojson}}'); is present in home.html I simply want to know what does tojson do my data contain list of list something like this: [['1','rahul','123'],['2','shiv','789'],['3','shivang','145']] my flask file render the template by return render_template('home.html',data...
Shashank Tripathi
0

votes
1

answer
14

Views

Multiple columns on left side of IN condition

This is a valid statement: SELECT foo FROM table1 WHERE column1 IN (SELECT bar FROM table2) AND column2 IN (SELECT bar FROM table2) But is there any way we can avoid repeating the SELECT bar FROM table2 statement to make it simpler and more economical? I mean something like this: SELECT foo FROM tab...
tom
1

votes
3

answer
176

Views

Alternating true/false flag for current time given step

I have a query being executed every X milliseconds. As a part of result I would like to have alternating true/false flag. This flag should change whenever the query is executed again. Example Sample query: select 1, ; 1st execution returns: 1 | true 2nd execution returns: 1 | false 3rd execution r...
Tomas Greif
1

votes
3

answer
616

Views

Log Data Analytics : Choice of Database [closed]

I am getting Log Data from various web applications in the following format: Session Timestamp Event Parameters 1 1 Started Session 1 2 Logged In Username:"user1" 2 3 Started Session 1 3...
Peeyush
1

votes
1

answer
103

Views

Symfony2 - How can I limit the number of records an user can store?

I'm building a SaaS project using Symfony2, Doctrine2 and Postgresql. I have three different subscription plans, and for each plan there is a limit of members each user can store. How can I limit a user to not store more members that the allowed for his subscription plan? Should I use a pre-persist...
marc casas
1

votes
1

answer
137

Views

Convert Nepali date to english date using postgresql

I have Nepali date in employment table of kep database and i want to convert this date in to English date using postgresql.Guide me please. Here is my table id date 1 2071/1/4 2 2071/1/29 3 2069/4/24
Ash
1

votes
1

answer
2.5k

Views

Execute function returning data in remote PostgreSQL database from local PostgreSQL database

Postgres version: 9.3.4 I have the need to execute a function which resides in a remote database. The function returns a table of statistic data based on the parameters given. I am in effect only mirroring the function in my local database to lock down access to this function using my database roles...
atom.gregg
1

votes
1

answer
255

Views

different sizes of postgres databases. move db from server1 to server2

I'm trying to move a database from server1 to server2. I read docummentation of postgres, and I think everything is right except that after I dumped db from server1 moved it and restored on server2 the sizes are different. Server1 SELECT pg_size_pretty(pg_database_size('db_name'));...
radubogdan
1

votes
1

answer
14

Views

How to access remote database with Rails+Nginx+Postgresql+DigitalOcean?

I have an app where I was using a database which was located in the same server (DigitalOcean Droplet) as an app. So in my Rails app it was: config/database.yml: production:
Motash
0

votes
0

answer
2

Views

Prisma how to look into the database

I have followed https://www.prisma.io/docs/get-started/01-setting-up-prisma-new-database-JAVASCRIPT-a002 and gotten it to work. I can see the following: docker ps tells me that two docker containers are being run running node index.js each time shows a list of all users that have been created. hence...
Amarsh
1

votes
1

answer
10

Views

Update a table with values from itself, but selecting on criteria from a different table

I have a junction table that only consists of foreign keys like so: JUNCTION +-----------+---------+---------+ | module_id | comp_id | ... | +-----------+---------+---------+ | 1 | 2 | ... | | 2 | 4 | ... | | 3 | 4 | ... | | 4 | 1...
BrDaHa
1

votes
1

answer
573

Views

Add a serial column based on a sorted column

I have a table that has one column with unordered value. I want to order this column descending and add a column to record its order. My SQL code is: select * into newtable from oldtable order by column_name desc; alter table newtable add column id serial; Would this implement my goal? I know that r...
Ben
1

votes
1

answer
1.5k

Views

Reduce bothering notices in plpgsql

I have a function which uses temporary table, that must be dropped if exists. drop table if exists t_xy; create temp table t_xy on commit drop as select ...; Subsequently I use this function in a view. The function is called many times while select is in progress. I like to use "raise notice" comma...
Hink
1

votes
1

answer
379

Views

Caching an advanced search query in rails

I have this method in my model that powers the search function in my app: def self.get_products(cat_ids, term, min_price, max_price, sort_column, sort_direction, current_store = nil) products = Product.where(category_id: cat_ids).joins('LEFT JOIN stores ON stores.id = products.store_id') products =...
Skyalchemist
1

votes
3

answer
3.2k

Views

update two tables at the same time postgresql

I have a problem in POSTGRESQL. I want to update two tables at the same time. Both update can be applied since they have the same condition. Here's the individual update: UPDATE standards.standards SET description = 'The student will demonstrate positive self esteem.' WHERE description LIKE 'The stu...
asdasdas
1

votes
1

answer
2k

Views

Postgresql cast seconds to time without time zone

I have some table (1) with a column of type bigint that has the time in seconds. I also have another table (2) that has the time as "time without timezone". When I do INSERT INTO 2 SELECT time FROM 1 it gives me an error: column "time" is of type time without time zone but expression is of type big...
DannyPhantom
1

votes
2

answer
1.1k

Views

PostgreSQL: union of several tables using dynamic names

I have a set of tables (about 100) in schema named qgep and which names start with vl_. They have all the same columns (colA, colB, colC). What I'd like to do is to get one big table which is the union of all my vl_* tables, with also a column with the name of the original table. I could get the lis...
Denis Rouzaud
1

votes
1

answer
3.4k

Views

How to change the privileges of a table in postgresql?

I try to grant specific privileges to my table "MEMBERS" in postgresql but nothing changes. More specifically I do this (through pgadmin console): CREATE DATABASE login; CREATE USER loginUser WITH PASSWORD 'xxxxxxxxxxxxx'; CREATE TABLE members ( id serial NOT NULL, username varchar(30) NOT NULL PRIM...
user1919
1

votes
1

answer
875

Views

TypeError: 'psycopg2._psycopg.Binary' object does not support indexing

I need your help, I'm trying to insert files (.txt) to postgres using psycopg2 with python, but sends this error and don't understand... error: TypeError: 'psycopg2._psycopg.Binary' object does not support indexing I have: archivo=open("coordenada.out",'rb').read() cur.execute("insert into fhi(coor...
Schiffer
1

votes
1

answer
2.4k

Views

PostgreSQL use case when result in where clause

I use complex CASE WHEN for selecting values. I would like to use this result in WHERE clause, but Postgres says column 'd' does not exists. SELECT id, name, case when complex_with_subqueries_and_multiple_when END AS d FROM table t WHERE d IS NOT NULL LIMIT 100, OFFSET 100; Then I thought I can use...
Makla
1

votes
1

answer
4.6k

Views

pgAgent fails to connect database :'fe_sendauth: no password supplied' (in Windows7)

Help me please.. I have some questions about pgAgent. [Environment] DB : PostgreSQL 9.3 OS : Window 7 OS login ID : [email protected] pgAdmin login ID : postgres pgAgent user : postgres pgAgent schema belongs to: postgres DB DB that I'll treat : testDB (In Step configuration, I specified 'testDB' as...
arayo
1

votes
3

answer
118

Views

How does postgres order the results when ORDER BY is not provided

Let's say I have two tables: user (user_name varchar(50), project_name varchar(50)) project (project_name varchar(50), project_cost(integer)) I have a query that's returns me results which are "de-facto desired" : select u.user_name, p.project_name from user u, project p where u.project_name = p...
Mecon
1

votes
2

answer
245

Views

Conditional sum based on boolean column

I'm using chartkick with groupdate to build charts: line_chart @product.stocks.group_by_date(:date).sum(:quantity) This is working fine but i'm getting wrong data because in the stocks table the quantity is depending on another column stock_in which is boolean. The quantity can't be stored as negati...
DevMarwen

View additional questions