Tagged Questions
PostgreSQL is an open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact version of Postgres when asking questions. Questions concerning administration or advanced features are best ...
0
votes
0answers
4 views
Queries run by users connected to database server through ssh tunnel
The database in question is Postgres hosted on Amazon. Everybody connects to the database as the same user. But the connection has to go through an intermediate server through an SSH tunnel. Every ...
0
votes
0answers
5 views
Postgres JSON array value contains query
I have a Postgres table named mytable with a JSON type column named jbag. Rows in the jbag column look like seen in the following query results.
select jbag from mydb.mytable where myid=20;
...
0
votes
0answers
9 views
.distinct not returning unique entires
I have a table of Projects, in that table, there may be the same project multiple times with the same name, however the created_at month will be different. I'm trying to select the most recent record ...
0
votes
1answer
9 views
How do i update a table column in postgre sql?
I am trying to update one of my tables but i get a syntax error. For example my query:
update micros set tabular = st_contains(b.the_geom,t.the_geom) from trees a, streets b;
The error message i ...
1
vote
0answers
9 views
R - RPostgreSQL Package - dbWriteTable to non-default schema where target table contains more fields than dataframe
The Issue
I am attempting to copy the contents of an R dataframe df to a PostgreSQL table table_name located in schema schema_name. By default, PostgreSQL will write tables to the public schema and I ...
0
votes
1answer
5 views
Merging two tables with one table having an extra attribute
I have two tables A and B that are identical. B has one extra attribute that holds boolean values. I want to insert non identical values from A into B and set the boolean attributes of all newly ...
0
votes
0answers
12 views
Unable to rake db:create with gem pg_jruby
I'm trying to setup a postgres db with jruby using pg_jruby.
When i run 'rake:db create' I get an error saying
C:/jruby-9.1.5.0/lib/ruby/gems/shared/gems/paperclip-4.2.4/lib/paperclip/storage/...
0
votes
0answers
8 views
Maintain row ordering in chained queries
This is all somewhat condensed to make it more understandable but I hope the gist is here. I'm really looking for some insight into how postgresql handles row sets.
I have a 'thing' table with a ...
0
votes
1answer
19 views
Delete record with nil id in rails console heroku ruby postgres
I used destroy to delete a record remotely in heroku rails console and now it does not show up if I write
MyModel.find_by(email: '[email protected]')
but it does show up if I write
MyModel....
0
votes
0answers
10 views
Hibernate 5 java.time issue on postgres
Using hibernate 5 with postgresql 8.2; on a column defined as timestamp(6) I am trying to map to
@Column(name = "LAST_UPDATED_DATE")
private Instant lastUpdatedDate;
But it seems hibernate doesn't ...
-1
votes
0answers
12 views
postgresql issue while trying to push to Heroku
I get this error while trying to push my code to Heroku
I do not get the error while running locally
I get it when trying to push to Heroku
I am thinking the issue is related to rake or postgresql
...
0
votes
0answers
5 views
Trying to connect postgresql heroku with play
I'm trying to use heroku postgresql with play framework (version 2.5.9) but it doesn't work...
build.sbt
libraryDependencies ++= Seq(
javaJdbc,
cache,
javaWs,
javaJpa,
"org.webjars" % "bootstrap" % "...
0
votes
0answers
18 views
Spring Boot spring.datasource.connectionProperties not working in 1.4.1
I was on Spring Boot 1.3.3. Upgraded to 1.4.1.
I was using
spring.datasource.connectionProperties:stringtype=unspecified
in 1.3.3 because I am using PostgreSQL and have a JSONB field in my ...
0
votes
1answer
18 views
column values as column names in thepsql query
Based on this I have a table which has values which will be the column names of the PostgreSQL query result.
id col1 col2
----------------------
0 name ax
0 name2 bx
0 ...
0
votes
0answers
22 views
Show objects based on selected week
I have an active relation Bar object with an attribute shift_date. shift_date represents each day between March and June. March and June comes from Foo which has attributes start_month and end_month:
...
0
votes
0answers
17 views
PostgreSQL commit on UPDATE increment
I don't know how to name the title of question right, but it be clear right below. I am new to PostgreSQL and the question can be a bit silly.
I have a table named "hash" what has an "id" (bigint) ...
1
vote
0answers
12 views
MacSierra broken the installation of psycopg2
I am unable to install psycopg2 using python2.7 or python 3.4. I tried installing these ways:
pip install psycopg2
python setup.py install on psycopg2 download file
The error:
ld: library not found ...
0
votes
0answers
4 views
Character encoding SQL ASCII to UTF-8 corruption
I am working with a Postgres database which is encoded in SQL ASCII.
With node.js, using pg package, I am requesting some rows, which may contains accents.
When I display raw data from request ...
0
votes
1answer
13 views
How to drop column if and only if it exists in postgres in 9+ version
i am trying to drop a column from a table.
what could be the best practices to check if column exists or not.
i went through the documentation https://www.postgresql.org/docs/9.2/static/sql-...
0
votes
1answer
17 views
designing database for storing unavailable dates in a room booking app
I am working on a room booking application. I am using rails 5 and postgres. The model goes like this.
A Hotel has_many room_types
RoomTypes table will look like this
RoomTypes
id: 1
name: "a ...
0
votes
1answer
14 views
How to create images containing numbers stored in database?
I would like to create images that would represent my data according to this rules: if number 1 exists in database (column), I would like to create image that would represent number 1 and such image ...
0
votes
2answers
29 views
Join a table in SQL based off two columns?
I have two tables:
Employees (columns: ID, Name)
and
employee partners (EmployeeID1, EmployeeID2, Time)
I want to output EmployeName1, EmployeeName2, Time instead of imployee ids.
(In ...
1
vote
1answer
15 views
INSERTING a single field into a table, referenced from another table
I need to insert a field in a that references an id field in another table.
The id field it is to going is next to the field 'test' (column - codedescription, table typecategory) and coming from an id ...
0
votes
0answers
5 views
mybatis invoke fail only first time
After I start the server, the first time I enter the homepage, it shows the Error. When I refresh it,it backs to correct homepage,And error message never show again.
0
votes
0answers
15 views
How to annotate weight column from a python list when filtering/sorting a django queryset?
Let's say I have a model Product and I need to sort the queryset results by highest recommended, then by price.
I get the recommendations from an external service as a list of integers and represent ...
0
votes
1answer
28 views
Empty GROUP BY in PostgreSQL
I have a table of association rules (say antecedent, succedent, and support). I would like to draw them in GraphViz. I have became so lazy that I don't want to code any imperative script for that, I ...
0
votes
2answers
23 views
Incrementally importing data to a PostgreSQL database
Situation:
I have a PostgreSQL-database that is logging data from sensors in a field-deployed unit (let's call this the source database). The unit has a very limited hard-disk space, meaning that if ...
2
votes
2answers
24 views
Remove # characters from arrays in PostgreSQL table?
I have a field (of type character varying) called 'directedlink_href' in a table which contains arrays that have values that all start with a '#' character.
How am I able to remove the '#' character ...
2
votes
2answers
31 views
SQL SUM expression and Lock
I have a problem with right SQL solution.
Current situation:
My database contains table with bank transactions (credit and debit).
Credit transactions are signed as posivitive amount (+), and
debit ...
0
votes
1answer
29 views
sql index same column two directions for traversing window functions
I'm trying use windowing functions to group records close to each other (within the same partition) into sequential groups. There's probably a better way to solve the problem, but right now what I ...
0
votes
1answer
22 views
Migrating data between databases not increasing a performance
I used to have a PostgreSQL 9.2 database with 3 tables:
A - contains 12 millions records
B - contains 24 millions records
C - contains 20 millions records
Tables are connected like:
A (one to many) ...
0
votes
2answers
15 views
Postgres Datebase multi foreign keys
I have a table described as following:
Table "public.lead"
Column | Type | Modifiers ...
0
votes
0answers
9 views
Knex error on joining multiple tables
My code for joining tables.
Requirement: table1 should be left join to (self join of table 2).
knex.select('p1.eid', 'pack.amt',
knex.raw("case when p1.et < " + time + " ...
1
vote
3answers
30 views
Postgres Left Join with where condition
I need to left join two tables with a where condition:
time_table
id rid start_date end_date
1 2 2017-07-01 00:00:00 2018-11-01 00:00:00
2 5 2017-01-...
1
vote
4answers
25 views
Combine results of two queries postgres
I have two queries like this:
SELECT project_id, user_ip, count(*) AS Count
FROM "user"
WHERE user_ip IS NOT NULL
GROUP BY user_ip, project_id;
SELECT project_id, user_id, count(*) AS Count
FROM ...
0
votes
0answers
14 views
Jhipster and Postgres connection
I'm trying to run the project in Jhipster based on this tutorial by the creator himself :https://www.youtube.com/watch?v=d1MEM8PdAzQ but it can't connect to Postgres
See errors below:
Caused by: ...
0
votes
1answer
12 views
How to periodically update a table in Postgresql via data retrieved from a php API using cronjob?
I have a database in PostgreSQL in which few tables are supposed to be regularly updated. The data is retrieved from an external API written in PHP.
Basically the idea is to update a table related to ...
0
votes
1answer
21 views
recursive query in postgresql
I have the following table:
and I have the following query:
WITH RECURSIVE users_r AS (
SELECT user_id, parent_id, 0 as level
FROM users
WHERE parent_id is null
...
0
votes
2answers
14 views
Counting distinct elements from strings PostgreSQL
I am trying to count the elements contained in a string in the following way:
row features
1 'a | b | c'
2 'a | c'
3 'b | c | d'
4 'a'
Result:
feature count
a 3
b 2
c ...
1
vote
0answers
20 views
Rails 5 multiple joins showing same output data for both tables
This is my query:
@cities = City
.left_outer_joins(:city_subs, :city_tags)
.select(:name, 'cities.id, COUNT(city_subs.city_id) AS scount, COUNT(city_tags.city_id) AS pcount')...
0
votes
1answer
12 views
How to use postgresql copy command with ANSI encoding?
I use PostgreSQL copy command to make a csv file.
COPY (select * from table) from '/tmp/123456.csv' with csv header encoding 'UTF8';
this command is ok
But I want to make file with ANSI encoding, ...
0
votes
1answer
12 views
Postgresql partitioned table is using space despite no entries
We have recently partitioned a master table with couple of millions of rows. This table is partitioned by "id range". There are 7 child tables created under this master table and entries are going ...
0
votes
0answers
17 views
How to select case query in sequelize?
i have sql query :
SELECT field1, field2,
CASE
WHEN field1=1 THEN 'a'
ELSE 'b'
END
AS field3
FROM test
and i want implement it on sequelizejs ,
const params = {
attributes: //DO ...
-3
votes
3answers
69 views
SQL Query to Retrieve Non-Duplicate-Row [duplicate]
I have a table documents (id, user_id, unique_document_id, version, date).
Example of entries are:
(1, 1, 1001, 1, null),
(1, 1, 1001, 2, null),
(1, 2, 1002, 1, null)
Currently I have a query ...
0
votes
1answer
17 views
copy LONGTEXT from MySQL to CITEXT in PostgreSQL using Python
I have data in MySQL table which I want to copy to a PostgreSQL table.
Everything works except when the MySQL contains a string with " and/or '
For example:
The data in MySQL:
When I run my code I ...
0
votes
1answer
18 views
how to get last value of any attribute in postgresql pivot table
I have a table like this
id u_id attr_key attr_value process_id insert_time
------|-------|----------|------------|--------------|--------------
1 1 name john ...
-1
votes
0answers
26 views
Aggregator functions not allowed in functions in FROM
LEFT JOIN unnest(ARRAY(SELECT name FROM event_categories having id = ANY (ARRAY(SELECT event_category_id FROM business_objective_event_categories having business_objective_id = ANY ( ARRAY(SELECT ...
0
votes
2answers
26 views
how to convert 12 hours timestamp format to 24 hours timestamp format in postgres?
how to convert 12 hours timestamp format to 24 hours timestamp format in postgres? like '2016-07-01 01:12:22 PM' to '2016-07-01 13:12:22'
3
votes
1answer
30 views
What is the Significance of timestamps with/without time zone in PostgreSQL and when to use them?
I need to save a current location of a car in an app which operates only in USA and I'm confused with datatype of psql between TIMESTAMP WITH TIMEZONE and TIMESTAMP WITHOUT TIME ZONE. Can the ...
0
votes
0answers
13 views
