FAQ Database Discussion Community


Connecting C++ and Postgresql

c++,postgresql,linker-error,libpqxx
I'm trying to connect C++ to Postgres. I recently installed libpqxx with Homebrew as follows: brew install libpqxx Then I made it with: make DatabaseTest on the file DatabaseTest.cpp. I ran the following simple program and got a strange error that I'm not sure what to do with... #include <iostream>...

postgres: using previous row value when current row value is null

postgresql
In my app I need to produce a data output (to be then converted to JSON) for the data in a postgres database (which is actually temperature data gathered by sensors). The data is stored in device_history_log as follows (for data between 11am and noon): row_id;deviceid;sensor_value_raw;last_update 104401;20865735;21.56;"2015-06-10 11:00:14" 104432;493417852;23.9;"2015-06-10 11:00:58"...

Adding proximity filter to user search

ruby-on-rails,ruby,postgresql,proximity,rails-geocoder
We have a user search set up on our application, so far just based on text content queries. So here's what I have: in models/user.rb def self.search(query) where("description ilike ?", "%#{query}%") end in app/controllers/users_controller.rb class UsersController < ApplicationController before_action :authorized? def index @users = User.all if params[:search] @users = User.search(params[:search]).order("created_at...

Spring Boot - How to set the default schema for PostgreSQL?

java,spring,hibernate,postgresql
What I am currently doing in my application.properties file is: spring.datasource.url=jdbc:postgresql://localhost:5432/myDB?currentSchema=mySchema Isn't there another property for this? Since it looks hacky and according to a post (still finding the link sorry, will update later), it is only applicable to PostgreSQL 9.4....

Is there a way to count a number of distinct values? [duplicate]

sql,postgresql,select,distinct-values
This question already has an answer here: SQL to find the number of distinct values in a column 8 answers In PostgreSQL 9.4 I have a table: id p_id PK integer ----------------- 1 1 2 1 ............. 123122 2233 Is there a way to count all distinct p_id values...

why do we use vaccum command in PostgreSQL exactly?

postgresql
VACUUM reclaims storage occupied by deleted tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it's necessary to do VACUUM periodically, especially on frequently-updated tables. This is what...

Return id from postgresql insertion

sql,postgresql,sql-insert
In an external application I want to do the following: Insert an entry to table A Insert a bunch of entries to table B, with the id of my newly inserted item in A as a foreign key So the tables would look like this: A(_id, data) B(_id, other_data) _id...

Retrieve updated rows in AFTER UPDATE trigger Postgresql

postgresql,triggers,plpgsql
I'm writing a AFTER UPDATE trigger in postgresql. Actualy I need to get at least one row after update in STATEMENT LEVEL trigger, but there is no OLD or NEW variable there. In FOR EACH ROW trigger I didn't manage to find a sort of batch_last param. But to do...

PostgreSQL: Aggregate by more than one column

sql,postgresql,aggregate-functions
I have the following data format: id eventtime use A 2015-01-01 00:00:00-01 5.2 . . 2015-06-06 23:59:50-01 5.7 B . . I'm trying get an output of the form: id eventtime use A 2015-01-01 1200 . 2015-06-06 1400 . B 2015-01-01 1500 basically sum of the item use for every...

rails block a record for change for another places in the code

ruby-on-rails,ruby,postgresql,activerecord,sidekiq
I have a ruby on rails application with a lot of sidekiq workers. Some of workers can work for a while (at least few minutes). How can I block some record for changes from another places (ie controllers), to avoid data conflict when I save this record in the worker?...

what is the SQL prepared stament for lo_import in postgreSQL

c++,sql,database,postgresql,odbc
In postgreSQL we have option to import the file into database using lo_import method, which returns a OID using that we can export the file from database to the filesystem. eg: describe test table name text, file_id oid insert into test values('arul_test',lo_import('/home/arul/test.txt')); prepared statement would be insert into test values(?,?);...

How to get second row in PostgreSQL?

sql,postgresql
I have next table: COLUMN_NAME DATA_TYPE PK NULLABLE inc_id bigint YES NO dt_cr timestamp NO NO email varchar(255) NO YES email column is not unique, so I should use GROUP BY email. Question: How can I get from this table minimal date, next row after minimal date and email related...

How to install postgres with NSIS with all parameters?

postgresql,installer,package,nsis
We have a C# program which uses postgres as its database, so we want to bundle the postgres in the installer as a semi-silent install, i.e. the user will be notified about the mandatory installation, but the installation itself will be automatically performed without the need for user input. After...

group by is not working in postgreSQL

sql,postgresql,group-by
I want group my data by createdAt but it is not working I don't know why... Only group by id which is useless for me. This is working: SELECT "id", "createdAt", "updatedAt" FROM "tables" AS "Table" WHERE "Table"."createdAt" BETWEEN '2014-04-21 20:46:25.938-04' AND '2015-04-21 20:46:25.938-04' GROUP BY "id"; This is what...

How to avoid SQL Injection with Update command in Npgsql?

database,postgresql,sql-injection,npgsql
I am trying to use the Npgsql PostgreSQL client to accomplish two things: Avoid SQL injection, and Manage data containing the single quote ' I cannot see how to do either :( PostrgeSQL version 9.1 In the below code, dx.chronic is of type bool? and cdesc of table dx may...

Is it possible to count all rows with the same id with COUNT?

sql,postgresql
PostgreSQL 9.4. I have the following table: id player_id serial PK integer --------------------------- 1 1 2 3 ... ... 123123 1 I need to count all rows with player_id = 1. Is it possible to do with the COUNT aggregate? Now I do it as follows: SUM(CASE WHEN player_id =...

Django: Which database call is faster/better?

python,django,postgresql
I'm building an online internet game that people can log in to play. When people play a Game, they create a Result. In addition, they can Like Games and Results. My main question is, is there a significant difference in the way that I make calls to the database? There...

(Automatically) convert SQL strings to 'enums'?

sql,performance,postgresql
We have a PostgreSQL database with billions of entries in the form: CREATE TABLE entry ( session integer NOT NULL, time integer NOT NULL, key text NOT NULL, data text NOT NULL ) In this database key is a developer-defined string, while data is user-defined content. In other words, while...

rails db:migrate fails rake aborted ActiveRecord::NoDatabaseError

ruby-on-rails,postgresql,heroku
On cloud9 IDE I installed postgresql because that is needed to deploy on Heroku, I am learning this so I am not sure, when I try to migrate a simple database table I used this bundle exec rake db:migrate and I get a rake aborted and a ActiveRecord::NoDatabaseError: FATAL: role...

In PostgreSQL is it possible to join between table and function?

sql,postgresql
I have a table named Cars and a function named PartsPerCar there is no direct link between carid and partid. the link is though 3 tables in the middle. I do have a function called PartsPerCar which gets carid (it goves via the tables in the middle) and give back...

What happens with duplicates when inserting multiple rows?

sql,postgresql,exception,duplicates,upsert
I am running a python script that inserts a large amount of data into a Postgres database, I use a single query to perform multiple row inserts: INSERT INTO table (col1,col2) VALUES ('v1','v2'),('v3','v4') ... etc I was wondering what would happen if it hits a duplicate key for the insert....

Speed up Min/Max operation on postgres with index for IN operator query

postgresql,postgresql-9.3
I would like to optimize the following query in postgres SELECT(MIN("products"."shipping") AS minimal FROM "products" WHERE "products"."tag_id" IN (?) with an index like CREATE INDEX my_index ON products (tag_id, shipping DESC); Unfortunately this one is only used when it's just one tag. Almost alwayst it is queried for a handful...

load the data from file using multi threading

java,postgresql
I want to load the data into PostgreSQL table using java program. In the java program I want to use multi threading and I use the COPY command API CopyManager copyManager = ((PGConnection)conn).getCopyAPI(); FileReader fileReader = new FileReader(filename); copyManager.copyIn("COPY "+tblname+ " FROM STDIN DELIMITER ',' ", fileReader); I have divided...

Avoid calling COUNT twice in CASE expression (PostgreSQL)

sql,postgresql
Inside a larger query, I have to COUNT a variable, then if it is larger than 1, have the count as a string otherwise an empty string: CASE COUNT(measurement.id) > 1 THEN to_char(COUNT(measurement.id),' 999') ELSE '' I'm afraid this is slow because I use COUNT() twice. Is there a better...

PostgreSQL: How to multiply two columns and display result in same query?

sql,postgresql
I have a query that it's select statement is this: select Greatest(p.price,0) as newprice, sum(q.qty) as qty from .... it gives me: newprice qty 10 1 0 1 100 2 1 2 I want to multiply newprice with qty to get: newprice qty result 10 1 10 0 1 0...

How to get number of invoices for last 12 weeks in Postgres

sql,postgresql,asp.net-mvc-3,aggregate-functions,generate-series
Invoice database contains invoice dates: create table dok ( dokumnr serial primary key, invoicedate date not null ); Dashboard requires comma separated list containing number of invoices for last 12 weeks, e.q 4,8,0,6,7,6,0,6,0,4,5,6 List contains always 12 elements. If there are no invoices for some 7 day interval, 0 should...

JAVA postrgresql insert String[] to text[] postrgresql cell

java,postgresql
I'm trying to make item's images names storage in postgresql DB. This is my query: Statement st = conn.createStatement(); String queryString = "INSERT INTO goods (item_title, item_descr, item_email, item_phone, item_images, item_price) " + "VALUES ("+item.title.trim()+ "\',\'"+item.descr.trim()+ "\',\'"+item.email.trim()+ "\',\'"+item.phone+ "\',"+item.images+ ","+item.price+");"; int rowsUpdated = st.executeUpdate(queryString); 'item.images' defined as string array in...

unexpected end of function definition on PostgreSQL function

postgresql
What is wrong with this function? EDITED>> CREATE OR REPLACE FUNCTION on_ai_myTable() RETURNS TRIGGER AS $$ BEGIN SELECT fn_name, count(*) + 1 FROM table_ebscb_spa_log02 WHERE time_type = 'Start' GROUP BY fn_name RETURN NEW.fn_name; END $$ LANGUAGE plpgsql; Ok I added the ";" but now, when I try to create it,...

what is “DESC LIMIT 1” used for in plpgsql?

postgresql,triggers
what is "DESC LIMIT 1" used for in plpgsql??? in this example ORDER BY t.timestamp02 DESC LIMIT 1 ...

PostgreSQL order by TSRange

postgresql
I think the question is pretty self explanatory, I've been learning about Postgres TSRange column type, but I've not been able to find an example of how you can order by it... How do you order by a TSRange, or specifically the lower bound?

Import csv file and update existing table in Postgres

postgresql,csv,heroku-postgres,csv-import
I have a Postgres database on Heroku. I connect to it using pgadmin. I exported a table data to csv. Edited some of the cells and now I want to replace the same table with the corrected csv. But when I try to import the csv I get this error...

How to create a SELECT query FROM “TABLE1 AND TABLE2”

sql,postgresql,select,join
I have a PostgreSQL database, with only SELECT permissions. In this DB there are two tables with the same structure (the same columns). I need to write several query in each table and join the results. There is a way for writing a query like this one? SELECT field1, field2,...

Postgresql Update JDBC

java,postgresql,jdbc
I want to update table: id integer NOT NULL, "first" character varying(255), "last" character varying(255), age integer, CONSTRAINT registration_pkey PRIMARY KEY (id) using method: void updateTable(String tableName, String columnName, String value, String columnName2, String value2) { try { String sql = "UPDATE " + tableName + " SET " +...

Subtract two columns of different tables

sql,postgresql,sum,aggregate-functions,subtract
I have two unrelated tables: contribution(id,amount, create_at, user_id) solicitude(id, amount, create_at, status_id, type_id, user_id) I need to subtract the sum of the amount of the contribution and of the solicitude from a user, but that result can't to be negative. How can I do this? Function or query? I tried...

JPA NamedNativeQuery syntax error with Hibernate, PostgreSQL 9

java,hibernate,postgresql,jpa
I am having a rather odd problem. I have native query which runs perfectly when executed on sql server: SELECT date_time, GREATEST(sum(count_up - count_down) OVER (PARTITION BY date_trunc('day', result.date_time) ORDER BY date_time),0) AS cum_amt FROM peoplecounting.result order BY date_time; However, using this query as native query in JPA results in...

What does `@@` mean in a PostgresSQL query?

postgresql
What does the "at symbol" mean in a PostgreSQL query? For instance, I'm looking at a query where a WHERE condition is: WHERE (to_tsvector('german', nodes.name) @@ plainto_tsquery('german', 'münd')) I understand that this has something to do with ignoring the umlaut, but searching the web for @@ part of it hasn't...

How to install / use orafce package in postgresql 9.4?

postgresql,debian,orafce
I'm running the "official" docker container of postgresql in version 9.4. I went inside the running container and installed orafce docker exec -i -t my_postgres bash apt-get install postgresql-9.4-orafce afterwards I've tried to reload and restart the postgresql service, as well as just restarting the whole container, but when I...

How to select rows by time interval in PostgreSQL?

sql,postgresql
I have a table with timestamp field and usage field like this timestamp | usage '2015-06-13 13:45:58' | 240 '2015-06-13 13:45:59' | 480 '2015-06-13 13:46:00' | 240 '2015-06-13 13:46:01' | 320 ... I want to get sum of usage for a period of '1 week' with '30 minutes' interval. I...

PostgreSQL conditional statement

sql,postgresql,if-statement
I need to implement a conditional SELECT statement based on the result of another statement. I don't want to create a function for this purpose, but simply using conditional SELECT. My unsuccessful approach looks as below: DO $do$ BEGIN IF SELECT count(*) FROM table1 < 1 THEN SELECT * FROM...

Save a hex-string to PostgreSQL column character varying

postgresql,hex
I want to save hex-string ('A2-5A-47-00-10-00-00-00') into a PostgeSQL database. My column is character varying(30). How can I do this?...

cast uuid to varchar in postgres when I use sequelize

postgresql,uuid,sequelize.js
I have a table with a column named _id of which the type is uuid. I cast the type of _id from uuid to varchar, in order to select the records as follows: SELECT "_id" FROM "records" WHERE "_id"::"varchar" LIKE '%1010%'; and it works well. _id -------------------------------------- 9a7a36d0-1010-11e5-a475-33082a4698d6 (1 row)...

Translation of interval

postgresql,datetime,translation,intervals,postgresql-8.4
There is maybe a way to translate automatically interval as it's shown by postgresql (e.g. "1330 days 10:54:54.266684") to other languages? For instance using locales, or other settings or I have to use replace, e.g. regexp_replace((now()-t.another_date)::text, 'day','dzień')?

INSERT a number in a column based on other columns OLD INSERTs

postgresql,triggers,autofill
In PostgreSQL I have this table... (there is a primary key in the most left side "timestamp02" which is not shown in this image, pls dont bother, its not important for the purpose of this question) in the table above, all columns are entered via querrys, except the "time_index" which...

plv8 disadvantages or limitations?

postgresql,plpgsql,plv8
I'm playing around with PLV8 to write trigger and stored procedures for PostgreSQL. So far I don't really see disadvantages compared to PLPGSQL. Especially if working with JSON it seems even smarter then PLPGSQL. Are there known disadvantages or limitations if using PLV8? Can PLV8 be a full replacement for...

How to delete replication slot in postgres 9.4

postgresql,replication,postgresql-9.4
I have replication slot which I want to delete but when I do delete I got an error that I can't delete from view. Any ideas? postgres=# SELECT * FROM pg_replication_slots ; slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn --------------+--------------+-----------+--------+----------+--------+------+--------------+-------------...

heroku pgbackups:url command is no longer working?

ruby-on-rails,postgresql,ruby-on-rails-4,amazon-web-services,heroku
How do I download my dump directly from Amazon AWS S3 if heroku pgbackups:url b004 isn't working? Specifically, when I run this command it returns: ! Please add the pgbackups addon first via: ! heroku addons:add pgbackups And then when I run this command I get: ! No such add-on...

Do not include select columns in group by

sql,postgresql,group-by,subquery
Here are my tables(including only relevant columns) Table: carts address_id - integer Table: addresses name - varchar phone - varchar Table: orders order_number - integer (this is the foreign key for cart table) I want to fetch phone number of the customers who have ordered only once, so I constructed...

Calculations between 2 records of 2 tables need to extend to more then 1300 fields. How and which database can achieve this?

database,postgresql,database-design
For one of our projects we are using PostgreSQL and access two tables with complex SQL calculations due to performance advantages. Each table, lets call them TableA and TableB, have 1301 fields of type double precision (field number restriction by PostgreSQL!) The field names for TableA are: id, A1, A2,...

Subtract hours from the now() function

sql,postgresql,datetime,timezone,date-arithmetic
We have a machine running 24x7. Every day I report the number of pieces it produced per hour. In our case one working day means '2015-06-16 06:00:00' to '2015-06-17 06:00:00' for example. Here is my code: select date_trunc('hour', t_el_eventlog.eventtime at time zone 'CET') as hours, count (distinct t_el_eventlog.serialnumber) as count...

PostgreSQL: check same long condition on two columns

sql,postgresql
I have two columns X and Y and a where statement as following: WHERE (x=0 or x=1 or x=4 or x=6 or x=7 or x=12 or x=75) and (y=0 or y=1 or y=4 or y=6 or y=7 or y=12 or y=75) since it's the same condition on both columns of...

Django 1.8 migration unable to cast column id to integer

django,postgresql,django-migrations
I'm migrating my site from an SQLite backend to a Postgres backend. We've been running native-Django style migrations (i.e., not South) from the beginning of the project. Most of the migrations run fine, but there's a hiccup in our of our applications. We got this far in the Postgres migration....

Is there a better way to write this query involving a self select?

sql,postgresql,join,aggregate-functions
I am looking for a "better" way to perform a query in which I want to show a single player who he has played previously and the associated win-loss record for each such opponent. Here are the tables involved stripped down to essentials: create table player (player_id int, username text);...

Aggregate count by several weeks after field data in PostgreSQL

postgresql,aggregate
I have a query returns something like that: registered_at - date of user registration; action_at - date of some kind of action. | registered_at | user_id | action_at | ------------------------------------------------------- | 2015-05-01 12:00:00 | 1 | 2015-05-04 12:00:00 | | 2015-05-01 12:00:00 | 1 | 2015-05-10 12:00:00 | | 2015-05-01...

Postgres SQL constraint a character type

postgresql,constraints
I have a table definition in Postgres. I would like to add a constraint to a column that is of Character datatype to have only 3 allowed values: CREATE TABLE my_table ( id character varying(255) NOT NULL, uid character varying(255) NOT NULL, my_text text NOT NULL, is_enabled boolean NOT NULL...

How to insert and Update simultaneously to PostgreSQL with sqoop command

postgresql,hadoop,hive,sqoop
I am trying to insert into postgreSQL DB with sqoop command. sqoop export --connect jdbc:postgresql://10.11.12.13:1234/db --table table1 --username user1 --password pass1--export-dir /hivetables/table/ --fields-terminated-by '|' --lines-terminated-by '\n' -- --schema schema It is working fine if there is not primary key constrain. I want to insert new records and update old records...

How to use Rails #update_attribute with array field?

ruby-on-rails,ruby,postgresql,ruby-on-rails-4,activerecord
Each question has an array of tags. schema.rb: create_table "questions", force: true do |t| t.text "tags", default: [], array: true How to atomically append to tags? How to prevent dups within the array? I tried question.update_attribute tags: tags << :ruby, but this doesn't work. Rails 4.17 and Postgres. EDIT: This...

Redirect if ActiveRecord::RecordNotUnique error exists

ruby-on-rails,postgresql,activerecord,error-handling
I have the next code to save: Transaction.create(:status => params[:st], :transaction_id => params[:tx], :purchased_at => Time.now).save! But how can I redirect to main root page if this ActiveRecord::RecordNotUnique error appears? Can I catch this error?...

JDBC - PostgreSQL - batch insert + unique index

java,sql,database,postgresql,jdbc
I have a table with unique constraint on some field. I need to insert a large number of records in this table. To make it faster I'm using batch update with JDBC (driver version is 8.3-603). Is there a way to do the following: every batch execute I need to...

Prepared statements: Using unnamed and unnumbered question mark style positional placeholders

postgresql
In SQL systems other than Postgres, such as MySQL for instance, prepared statements can use question marks ? as a placeholder for data in prepared statements. INSERT INTO foo (id, name) VALUES (?, ?), (?, ?); However, in Postgres the only available placeholders seem to be the numbered placeholders, so...

need help specifying potentially reserved words as strings in postgres query

postgresql
I've been using mysql for close to 10 years, but have recently jumped into a project that's exposed me to postgres for the first time. Most of the differences aren't a bit deal, but I have been running into some small issues along the way that are throwing me off....

Column does not Exist in postgresql?

sql,postgresql,postgresql-9.1
I am trying case inside select query and want to use that data column is generated by that case in same query. My query is : select order_id , order_item_id , sku ,merchant_payable as "Value Get" , case when name like 'Rise%' then amount-(((amount*12.14)/100)+ ((amount*3.08)/100) + 51.30) when name like...

How do I make a query search in rails case insensitive?

ruby-on-rails,postgresql,ruby-on-rails-4,search
I have this search method in my user.rb model def self.search(query) where("description like ?", "%#{query}%") end but my search is case sensitive. I want to make it case insensitive. How do I do that? I'm hoping it's a quick fix. Thanks...

Using psql options in npgsql

vb.net,postgresql,postgis,psql,npgsql
I'm using npgsql in a small app used in the library I work for. Originally my app (vb.net) was using sendkeys.send and sendkeys.sendwait to type psql commands in cmd. This works but its slow and inefficient (ugly, buggy, etc.). I typically use the \o and \H options in psql to...

How to order SQL query result on condition?

sql,postgresql,order,condition
i have the this SQL query: SELECT DISTINCT category FROM merchant ORDER BY category ASC that gives this output: accommodation education food general health money shopping sport transport How to put the row that contains "general" at the start (or the end) of the result?...

Empty array as postgresql array column defult value

sql,postgresql
I have a defined an array field in postgresql 9.4 database: character varying(64)[] Can I have an empty array e.g. {} for default value of that field? What will be the syntax for setting so? I'm getting following error in case of setting just brackets {}: SQL error: ERROR: syntax...

How to check what constraint has been violated?

java,sql,postgresql,exception
I'm used to check if the user try to store a valid data with cathcing the ConsratinViolationException, like that: try { //persisitng to a db } catch (ConstraintViolationException e){ //Print message } I'm using PostgreSQL and now I'm under the isssue that the persisitng can violate more than one different...

flask-sqlalchemy, sql count records in a relationship and then join to a users table

python,postgresql,flask-sqlalchemy
Im querying my users this way with sql: select * from users i get all the users but now i want to query how many friends they have so if i do this i do this: select count(*) from users inner join friendship on users.id = friendship.user and i get...

Complex rails SQL query

sql,ruby-on-rails,ruby,postgresql,activerecord
First of all, a user has many age_demographics. An AgeDemographic object looks like this: #<AgeDemographic id: 4384, user_id: 799, range: "35 - 49", percentage: 3.2, created_at: "2015-05-22 04:17:10", updated_at: "2015-05-22 04:17:10"> I'm building a user search tool where someone will select multiple age ranges that they want to target ("12...

PostgreSQL How to find where function is being used

sql,postgresql
I have a function named func1. It's used somewhere in the database but I don't know where. I wrote this query to help me find where it's being used: select proname,prosrc from pg_proc where prosrc like '%func1%'; How can I modify this query to check if triggers use func1 as...

postgresql complex group by in query

sql,postgresql
I have a query that gives: itemid deadlineneeded delievrydate quantity 200 15/07/15 14/07/15 5 200 15/07/15 14/07/15 10 200 15/07/15 13/07/15 25 201 15/07/15 14/07/15 30 200 14/07/15 10/07/15 3 201 15/07/15 15/07/15 100 It gives the information from multiple tables. Basically it means When items arrive to warehouse (delievrydate)...

Error while trying to insert data using plpgsql

postgresql,timestamp,plpgsql
I have the following plpgsql function: CREATE OR REPLACE FUNCTION test_func(OUT pid bigint) RETURNS bigint AS $BODY$ DECLARE current_time timestamp with time zone = now(); BEGIN INSERT INTO "TEST"( created) VALUES (current_time) RETURNING id INTO pid; END $BODY$ LANGUAGE plpgsql; select * from test_func(); The above gives an error: column...

PostgreSQL can't find column

java,postgresql
I create a database that contains 4 column (ID_PRODUIT,NOM_PRODUIT,QUANTITE, PRIX).I tried to make a methods that allow me to serach Product(my class)using a string key,however, the query failed to identify the "NOM_PRODUIT" column and it turn "NOM_PRODUIT" to "nom_produit" in the messsage error. Connection conn = DriverManager.getConnection(url, user, passwd); PreparedStatement...

Postgresql Iterate over an array field and use the records for another query

sql,arrays,postgresql,many-to-many
I'm trying to iterate over an array field in order to use every record as a parameter to query and finally join all results, but I need help to get it. I have a table with an array field called fleets and it can have one or more values ie....

PostgreSQL: trigger to call function with parameters

postgresql
In PostgreSQL, I'm trying to create a trigger that passes an argument to a function, but it appears that this operation looks for a function whose signature has zero arguments: ERROR: function create_rec_if_needed() does not exist: CREATE TRIGGER after_update_winks AFTER UPDATE ON winks FOR EACH ROW WHEN (NEW.counter > 3)...

Postgres Index-only-scan: can we ignore the visibility map or avoid heap fetches?

postgresql,indexing
Sorry, lots of context before the actual question as we've throughly researched this and I wanted to give you full context. Some context: postgres index-only-scans rely on the visibility map (VM). If a page is not marked as not-fully-visible in the visibility map, postgres fetches that page to ensure the...

Ubuntu 14.04 - An error occurred while installing pg (0.18.2), and Bundler cannot continue

ruby-on-rails,postgresql,ubuntu,heroku,sqlite3
This issue doesn't let me go ahead and I don't know whether it's possible for me to deploy my Rails App ever on Heroku. When I try bundle install by having gem 'pg' in my Gemfile it gives following error. An error occurred while installing pg (0.18.2), and Bundler cannot...

JSONB: more than one row returned by a subquery used as an expression

sql,postgresql,postgresql-9.4,jsonb,set-returning-functions
I am (still) new to postgresql and jsonb. I am trying to select some records from a subquery and am stuck. My data column looks like this (jsonb): {"people": [{"age": "50", "name": "Bob"}], "another_key": "no"} {"people": [{"age": "73", "name": "Bob"}], "another_key": "yes"} And here is my query. I want to...

postgresql run multiple select statements with different columns in single query

sql,postgresql
I have 14 queries, such as select activities.type, sum(activity_fees.amount) from activity_fees inner join activities on activity_fees.activity_id = activities.id where DATE(activities.due_at) = current_date - INTERVAL '1 day' group by activities.type SELECT avg(activities.rating) FROM fellows inner join auth on a.a_id = f.id inner join activities on activities.fellow_id = fellows.id WHERE f.type in...

Syntax error while creating table in PostgreSQL 8.1

postgresql
I have a syntax error in this query: CREATE TABLE test (LIKE original_table INCLUDING INDEXES); ERROR : syntax error at or near "INDEXES" I am using PostgreSQL 8.1 version. Where is the issue in my query?...

Formatting dates in PostgreSQL

sql,postgresql,select,date-formatting
I have a field which its format is date with time as: YYYY-MM-DD HH-MM-SS for example: 2000-08-12 00:00:00 I want to get just the date part and change its format to DD/MMM/YYYY for example the expected result of the previous example will be: 12/Aug/2000 The field definition is: Ddate timestamp...

How to group following rows by not unique value

sql,postgresql,greatest-n-per-group,window-functions,gaps-and-islands
I have data like this: table1 _____________ id way time 1 1 00:01 2 1 00:02 3 2 00:03 4 2 00:04 5 2 00:05 6 3 00:06 7 3 00:07 8 1 00:08 9 1 00:09 I would like to know in which time interval I was on which...

when to use = and := in postgreSQL?

postgresql,triggers
Im using plpgsql to write triggers n Im wondering when to use = and when to use := in postgreSQL, what is the difference??? for example: CREATE OR REPLACE FUNCTION on_ai_myTable() RETURNS TRIGGER AS $$ DECLARE t_ix real; n int; BEGIN IF NEW.time_type = 'Start' THEN SELECT t.time_index FROM table_ebscb_spa_log02...

Update enum column in Laravel migration using PostgreSQL

postgresql,laravel,laravel-5,laravel-migrations
According to this answer, I have to run a raw query if I want to update an enum in MySQL. But with PostgreSQL, I can't use this query, and enum type for PostgreSQL in Laravel seems strange. Is there any way to update enum in a migration for postgreSQL ?...

Connecting from Spark/pyspark to PostgreSQL

postgresql,jdbc,jar,apache-spark,pyspark
I've installed Spark on a Windows machine and want to use it via Spyder. After some troubleshooting the basics seems to work: import os os.environ["SPARK_HOME"] = "D:\Analytics\Spark\spark-1.4.0-bin-hadoop2.6" from pyspark import SparkContext, SparkConf from pyspark.sql import SQLContext spark_config = SparkConf().setMaster("local[8]") sc = SparkContext(conf=spark_config) sqlContext = SQLContext(sc) textFile = sc.textFile("D:\\Analytics\\Spark\\spark-1.4.0-bin-hadoop2.6\\README.md") textFile.count() textFile.filter(lambda...

Is there a better solution to join the same dataset to get the distinct count of a dimension used for aggregation?

sql,postgresql
I have to do an aggregation on certain measures, but i also have to say how much distinct values i have for a dimension, which i use for aggregation. I can solve the problem using two sub-selects, but performance is not the best. Is there any way to solve this...

How to customize the configuration file of the official PostgreSQL docker image?

postgresql,docker
I'm using the the postgres official image https://registry.hub.docker.com/_/postgres/. And now I'm trying to customize its configuration. For this purpose the command sed is used, e.g. to change the max_connections: sed -i -e"s/^max_connections = 100.*$/max_connections = 1000/" /var/lib/postgresql/data/postgresql.conf I tried two methods to apply this configuration. The first is by adding...

PostgreSQL function execute query

sql,postgresql,plpgsql,postgresql-9.3
I want to run a SQL query if a condition is met, but I get the following error: ERROR: a separate $ chain is unfinished in or near «$func$ my SQL query is: CREATE OR REPLACE FUNCTION myfunc() RETURNS TABLE(dateticket date, timeticket time, userid integer, my_all bigint) AS $func$ BEGIN...

Sqoop Export with Missing Data

sql,postgresql,shell,hadoop,sqoop
I am trying to use Sqoop to export data from HDFS into Postgresql. However, I receive an error partially through the export that it can't parse the input. I manually went into the file I was exporting and saw that this row had two columns missing. I have tried a...

name of value returned from PostgreSQL function

postgresql,plpgsql
I'm working on some code that talks to a PostgreSQL server for the first time (my experience is with SQL Server) and I need help figuring something out. I've always tried to avoid "SELECT * ..." from code, but I can't figure out what the alternative is when calling a...

Return integer value of age(date) function in Postgres

postgresql
I would like to determine the number of days that an account has been open. Ideally, I would like to compare the return value to an integer(days). i.e I would like to see if age(open_date) > 14 If anyone has any better ideas... Thanks...

PostgreSQL - aggregating data by date from datetime field

postgresql,datetime
I have data over time for particular users and timestamp of the form 2013-11-23 16:00:00-05 - there's by minute by minute date for each user and corresponding usage value. So the table has values: user_ID, localminute, usage. I am trying to aggregate usage at a day wise level, what i...

Superuser Role Specific to certain Databases in PostgreSQL

database,postgresql,role,superuser
I have created a User Role with superuser privilege. I have around 30 Databases on my server. I want to assign this role to only only DB. The current role lets the user access all the DBs as super user. How can I restrict him from accessing other DBs as...

postgresql mathematical formula error

postgresql
Hi I am trying to use a mathematical function on each row in postgresql. But It gives me a error. My Query: Select stock_inventory_line.product_code AS Sku, COUNT(sale_order_line.name) AS Qty_Sold, stock_inventory_line.product_qty AS Current_Qty, (stock_inventory_line.product_qty / Qty_Sold) AS NOM From sale_order_line, product_product, product_template, product_category, stock_inventory_line WHERE sale_order_line.product_id = product_product.id AND product_product.product_tmpl_id =...

PostgreSQL How to add WHERE where is count() and GROUP

sql,postgresql
How can I add WHERE function into my query ? SELECT count(*), date_trunc('year', "createdAt") AS txn_year FROM tables WHERE active = 1 // not working I don't know why GROUP BY txn_year; Thanks for any opinion...

Trigger order of execution

postgresql
I prepared a dumb-proof rollback memu on order to give possibility to rollback wrong commands. It works such a Ctrl-Z. To do this I prepared the log tables that contains all the operations done. The rollback menu, group the operation for transaction, and shows all tables. Therefore there is so...

How to remove duplicate rows with foreign keys dependencies?

database,postgresql,exception-handling,foreign-keys,plpgsql
I'm sure this is common place, but Google is not helping. I am trying to write a simple stored procedure in PostgreSQL 9.1 that will remove duplicate entries from a parent cpt table. The parent table cpt is referenced by a child table lab defined as: CREATE TABLE lab (...

SQL CTE Syntax to DELETE / INSERT rows

sql,postgresql,common-table-expression,postgresql-9.2
What's the CTE syntax to delete from a table, then insert to the same table and return the values of the insert? Operating on 2 hours of sleep and something doesn't look right (besides the fact that this won't execute): WITH delete_rows AS ( DELETE FROM <some_table> WHERE id =...

Struggling with a simple JOIN

sql,postgresql
I feel like I'm overcomplicating the problem. I have a situation where I have 2 tables: orders: - order_id order_products: - order_id - status If each order product can have an independent status (e.g.: shipped, backordered, etc.) I want to be able to write a query to say "show me...

postgresql query json for value (instead of key)?

json,postgresql
Imagine this simple postgresql table ('json_table'): id | data(json) 1 | {"a": 1} 2 | {"a": 1, "b": 2} 3 | {"a": 2} I know it's possible to query for all records having a key 'a': SELECT * FROM json_table WHERE data ? 'a'; But I would like to query...

Alternatives to WITH .. AS .. clause in PostgreSQL

sql,postgresql,plpgsql,common-table-expression,postgresql-9.3
I have several big queries of the following type (simplified for clarity). create function myfunction() returns void as $$ begin ... with t as ( total as total, total * 100 / total as total_percent, total / people.count as total_per_person, part1 as part1, part1 * 100 / total as part1_percent,...