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
1answer
10 views
Postgres could not determine data type of parameter $1 in Golang application
I am creating an application in Golang that uses Postgres using the pq driver. I want to make a function that can select a user-determined field from my database, but I get an error:
pq: could not ...
go
0
votes
0answers
8 views
SQL Natural Join
Okay. So the question that I got asked by the teacher was this:
5. (5 marks) Construct a SQL query on the dvdrental database that uses a natural join of two or more tables and an additional where ...
0
votes
0answers
8 views
Imported dump grow the table without rows
TL;DR
my_base=# SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (...
0
votes
0answers
5 views
What's the correct OID for an array of composite type in PostgreSQL?
Under PostgreSQL 9.5, in a C function I wrote, ARR_ELEMTYPE(PG_GETARG_ARRAYTYPE_P(0)) where (0) is an array of composite type (i.e. defined by CREATE TYPE) returned 28642010. This number doesn't ...
0
votes
1answer
10 views
how to compare two rows in different table and return miss- match column from both the table
I have 2 tables; 1st table containing original values and 2nd table contains modified values. Modification might happen only in few columns.
Query output should contain only columns (fields) where ...
0
votes
0answers
9 views
ActiveRecord::StatementInvalid in ContactController rails & postgresql
I have an error when i try to create/ edit or destroy a contact from my table.
When i try to create/ edit i have :
ActiveRecord::StatementInvalid in ContactController#create
PG::...
0
votes
1answer
23 views
Invalid cursor position SQL error when using .next()
SqlRowSet categorie = CO.getJdbcTemplate().queryForRowSet("SELECT pb1plnitm_unid, categories FROM pb1plnitm WHERE pb1plnitm_unid = ?", uirecord.getUniqueId());
int rowCount = 0;
while ...
0
votes
0answers
7 views
PostgreSQL - DDL Statement Function parameter
Is it possible to pass DDL statements as function parameters?
Something like this:
select MyAlterTable('Employees', add column CA name(250));
select MyAlterTable('Customers', add column CA name(250))...
0
votes
0answers
8 views
Postgres - Pass Function Parameters like C# params
In C# we can do something like this:
public static void UseParams(params int[] list)
{
for (int i = 0; i < list.Length; i++)
Console.Write(list[i] + " ");
}
and call it like this: ...
0
votes
1answer
23 views
How to update\delete column by serial index in PostgreSQL?
I have table which represents as python array.
id, string
0, "first"
1, "second"
2, "third"
3, "fourth"
I want delete value by some index:
del my_array[2]
(It's SQL query: DELETE FROM my_array ...
0
votes
1answer
9 views
SQL receive row and execute other query
I need a sql function to calculate data from different tables.
There are two tables,
tbl_moving
|-----------------------------|
| start_datetime | value |
|-----------------------------|
| 2016-...
0
votes
0answers
9 views
Laravel Postgres calculate time difference and get min/max
Hi my table structure is like this i am using laravel and postgres database
id | serving_start_time | serving_end_time
1 | 2016-10-05 00:24:08.894 | 2016-10-05 00:24:19.003
2 | 2016-10-...
0
votes
1answer
15 views
pgadmin4 command keep running and process watcher won't go away
I was trying to follow the instructions from postgresqltutorial to load a sample database into postgresql using pgadmin. But after the database was restored(since I can query data from the database), ...
0
votes
2answers
18 views
How to get rows where multiple parameters met
here's my problem:
I've got a table idtypedata made out of two columns:
IDdata
IDtype
Both of them aren't unique.
I want to output a table displaying IDdata for each time all occurrences a ...
1
vote
4answers
30 views
How to Plot Real time data chart using HTML5?
Currently I am dealing with Real time data charts using HTML5 as front end and My database is PostgreSQL. I am also using PHP as web service and java script as a scripting language.Now, I am able to ...
-1
votes
0answers
14 views
I want to get last 6 months records from the current date using squelize query in the node
In my case, I want to get last 6 months records from the current date using sequelize query in the node and want to group by number of records of each month.
0
votes
1answer
16 views
i can't make hibernate sql query
hi i am a noob programmer.
I am learning Hibernate now. And I am frustrated by
SELECT COUNT(*) FROM zipcode a
WHERE (ISNULL( :sido , '') = '' OR a.sido = :sido) AND
(ISNULL( :sigungu , '') = '' ...
0
votes
1answer
12 views
Trigger for update timestamp field
I'm trying to create trigger, that updates my lastmodified field on insert/update. Here's my trigger:
CREATE OR REPLACE TRIGGER timestamp_trigger BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW ...
0
votes
0answers
9 views
How I can saving in two database tables with Hibernate if the father table contain a serial?
I am working in a Java web application with Hibernate 4.3.x and postgres 9.4. I need to save two tables in the same transaction in a complex solution that required serial columns. The next example is ...
1
vote
2answers
25 views
PostgreSQL, unable to update row ( with row level security )
set role qstoolbox;
update contract set contract_reference = 'ok ok ok' where id = 2
The above works
Query returned successfully: one row affected, 41 msec execution time.
set role user_role;
...
0
votes
2answers
15 views
psycopg2 not all arguments converted during string formatting
I am trying to use psycopg2 to insert a row into a table from a python list, but having trouble with the string formatting.
The table has 4 columns of types (1043-varchar, 1114-timestamp, 1043-...
0
votes
1answer
12 views
Make sure that atleast one foreign key is not null
I have a situation in which I have to make sure that one of my foreign keys are not null. There are 2 foreign keys being referenced in my table and I want to make sure that both are not null. There ...
1
vote
3answers
31 views
What is the expected behaviour for multiple set-returning functions in select clause?
I'm trying to get a "cross join" with the result of two set-returning functions, but in some cases I don't get the "cross join", see example
Behaviour 1: When set lenghts are the same, it matches ...
0
votes
1answer
23 views
Configuring a Postgresql POSTGIS database
First off, I am new to django. I am trying to use GeoLite(GeoIP2) datasets in my POSTGIS database in Django 1.10. When I attempt to configure the myapp/settings.py file, i get error messages.There ...
0
votes
0answers
4 views
Loopback: Auto-discover database structures and auto-generate relations between tables
We are using Loopback as a standard RESTFul API.
I have a script that is used to auto-generate the model data based off of discovery of the database tables/structures.
I can generate the schema data ...
0
votes
0answers
15 views
Running project in console
so I have this kind of big grails project connected to my postgresql.I found the file which contains the main class and executed it as a main file,however I got around 100 warnings most of them ...
intellij-idea
0
votes
1answer
14 views
Postgresql extension Makefile returning error
I wrote a SQL extension following the instructions on PGXN, but I always get a make install error as following:
/bin/mkdir -p '/usr/share/postgresql/9.5/extension'
/bin/mkdir -p '/usr/share/...
0
votes
1answer
27 views
How send Datetime as parameter in SQL command on Python?
I have a sql command and just want to select some records with some conditions ( using python & db is Postres):
So, my query is:
current_date= datetime.now()
tt = yield self.db.execute(self....
-1
votes
1answer
16 views
Inspectdb error django
I have postgresql db. I want to make reverse engeneering from db by command
python3 manage.py inspectdb > models/models.py
All was ok, but I needed to customize auth. I extends from ...
1
vote
0answers
14 views
docker-compose postgresql implementation in python app
I am building a Python app and I am trying to run it inside Docker. (My works completely in virtualenv with no Docker.)
So, my app consists of config.py file and other files which make everything ...
0
votes
2answers
17 views
How to change update statements into insert statements while maintaining SET command from UPDATE command?
As the title suggests, I need to change my update and join statements into insert statements. How I would do this while incorporating SET from the UPDATE statements?
Update statement:
UPDATE tableA
...
0
votes
1answer
6 views
Within Group Error in Postgresql
I am getting a within group error in PostgreSQL 9.4:
ERROR: WITHIN GROUP is required for ordered-set aggregate rank
LINE 4: (select a1.rank as r1,
create view a as (select rank() over (order by ...
0
votes
1answer
18 views
How to print NEW value with trigger Postgres
I want to create trigger for Insert operation, and procedure that prints inserted value.
CREATE TRIGGER added_product_info_trigger
BEFORE INSERT
ON products
EXECUTE PROCEDURE added_product_info();
...
0
votes
0answers
15 views
How to do a second query with the results of a first query and aggregate those results?
I have the following set of data using the postgres ltree plugin to model a tree data structure:
This query allows me to cut the tree returning nodes below the cut:
with data(path) as (SELECT * ...
0
votes
0answers
5 views
Mapnik query parsing takes more time than actual execution
I'm using python-mapnik.
I noticed that, for each query/layer in my Mapnik project.xml file, PostGIS logs several parse/bind/execute query durations and that, even for 1 ms lasting queries, the parse ...
0
votes
0answers
15 views
Why are there many repetitive lines saying CACHE when I render a Rails view?
When I render a view, logs are generated that seem repetitive. The page I am rendering is a list of food. Each instance of food is a "quantity," which has a list_id and food_id.
Notice that the same ...
0
votes
1answer
17 views
Go with Postgres: LastInsertedId for non sequential identifiers
I´m writing a small web service in Go which uses Postgres through the pq driver package.
I´m using a uuid´s as identifier for my models so LastInsertId won´t work.
So I´m thinking I could ...
0
votes
1answer
26 views
PostgreSQL - Auto Cast for types?
I'm working on porting database from Firebird to PostgreSQL and have many errors related to type cast. For example let's take one simple function:
CREATE OR REPLACE FUNCTION f_Concat3 (
s1 varchar, ...
0
votes
0answers
31 views
How to get count of entries in column grouped by this column in knex
I'm trying to translate the following SQL query to knex.js:
select name, count(name)
from mytable
group by name;
It returns:
name | count
-----+-------
greg | 2
john | 1
I've tried:
...
0
votes
1answer
16 views
retrieve and combine chunks of information from string using regex in postgres
I need to retrieve information from structured text fragments which have the following format:
(AAB) Some name1 here 1234 (BB) More 12-text 99 (XY*) Hello world 12
What I want to get out is the ...
0
votes
0answers
4 views
Heroku, use one IP address. Maybe Postgres - Psycopg2
There is a point in my code where I do this:
conn = psycopg2.connect(database="****", host="***", user="***", password="****")
I connect to a remote database. The problem is that each time that ...
-2
votes
1answer
12 views
PostgreSQL: Effective group by any column with timestamp
I have one big table (about 1 million records) with 50 columns (one timestamp and other entity's parameters) and I want to make queries like:
select param_name, count(*)
from big_table
where ...
0
votes
1answer
14 views
JPA schema validation with PostgreSQL 9.5
I am using JPA (hibernate) with Postgres database, my hibernate database configuration are as below:
properties.setProperty("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
properties....
0
votes
1answer
8 views
Add column to SQL query
There is some select
SELECT column1,
COUNT(CASE column2 WHEN 'type1' THEN 1 ELSE NULL END) AS Type1Count,
COUNT(CASE column2 WHEN 'type2' THEN 1 ELSE NULL END) AS Type2Count,
COUNT(CASE column2 WHEN ...
0
votes
1answer
19 views
How to associate objects in Sequelize (Node js)?
I have User and File models in Sequlize. User can have several files.
I have association
db.User.hasMany(db.File, {as: 'Files', foreignKey: 'userId', constraints: false});
I want to init User object ...
-1
votes
0answers
10 views
Npgsql: INSERT command field name with space
How to solve a field name containing spaces like "code patient"?
This is my code:
pat.CommandText = "SELECT * FROM Patients";
NpgsqlConnection conn = new NpgsqlConnection("Server=" + POSTSERVER.Text ...
0
votes
0answers
6 views
ROWTYPE in CURSOR postgresql
i want to know if it is possible to get "Rowtype" for a cursor in postgresql (pl/pgsql).
..
my cursor has a select from multiple table and i don't know how to get the rowtype.
c_Data CURSOR FOR
...
0
votes
1answer
7 views
Copying a postgresql database through ssh
First off, this is based on
Copy a PostgreSQL database into another database. I would gladly expose my problem there, however I have not enough Karma yet to do so.
Here's my code :
sourceDB=$1
...
0
votes
1answer
18 views
How to incorporate an extract statment into mathematical operations?
I have to do simple subtraction of time but need help implementing two queries into one script.
Due to some internal restrictions on DB, I can not convert timestamp column too time using any of the ...
0
votes
0answers
6 views
Connect to postgres data base through corporate proxy
I'm trying to connect to postgresql database that locate in my home using pgAdmin or Qgis, but in company, where I work, admin create proxy-server http(s)//company.proxy.com:8080
How can I connect to ...
