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
Set value in select statement
I have two views A and B created by other views (cant use a rule ON UPDATE DO INSTEAD). A and B have also the same three columns text_1, text_2, count. Till now my SQL looks like:
select A.text_1, ...
0
votes
1answer
19 views
Substring in postgres
I have two tables in Postgres that I am trying to compare and a having trouble accessing a substring from one and comparing it to another one. I'm very new to Postgres (specifically SQL through ...
0
votes
0answers
1 view
importing postgres dump to cloud 9 app
I am running an app on cloud9 platform.
I have a database dump in postgres which I want to import to my app so I don't have to add data manually to my c9.
C9 comes with postgres installed.
Below ...
0
votes
0answers
8 views
Postgresql NpgSql connection handling extra query and multiple close connection
I am basically running a sql query through dapper but when I do some profiling on this on every query that i perform to npg sql I see an extra ExecuteScalar query that is sent on that connection. And ...
1
vote
0answers
10 views
Dynamically execute the create table in PostgreSQL
When I'm trying to Execute below code
Execute 'create table employee(Empid Serial Primary key, Empname text, Dob Date)'
Im getting error
ERROR: syntax error at or near "'create table ...
2
votes
3answers
86 views
Numeric value does not fit in a System.Decimal
I am using a Postgres database with Entity Framework.
In my database there is a table which has column price (numeric) and values like 12314.0464314894136514658489431486146548
When I get data using ...
-1
votes
1answer
25 views
Python to parse html data and store into the postgresql database
a
This is trouble me for two days, I am new one to python, I want to Parse the html data as the following link:http://movie.walkerplus.com/list/2015/12/
and then store the data into the postgresql ...
1
vote
3answers
23 views
Inserting Python arrays into Postgres queries with psycopg2 leads to Type Error
I have a list called people2 like so:
['xloS4ooQOT',
'3s4LyNyHs3',
'NRL6zNePCT',
'7hkLogfk8T',
'5JcUkJ8FLO',
'LZ6DMUfnEA',
'CmBaomzMXC',
'M5OPb0yf09',
'CqG2XYGPxk']
I am trying to use it as ...
0
votes
1answer
9 views
Deleting duplicates rows from redshift
I am trying to delete some duplicate data in my redshift table.
Below is my query:-
With duplicates
As
(Select *, ROW_NUMBER() Over (PARTITION by record_indicator Order by record_indicator) as ...
0
votes
0answers
6 views
Slow query due to planer invalid stats - even after analyze
I have 2 tables:
CREATE TABLE sf.dir_current (
id BIGINT primary key,
volume_id INTEGER NOT NULL,
path VARCHAR NOT NULL
);
CREATE index dir_volid_path_indx on dir_current (volume_id, ...
0
votes
1answer
26 views
Get records by hash value
I'm trying to get records from table by hash value.
Here is record example:
Activity:0x0000000709be18> {
:id => 1,
:trackable_id => 3,
:trackable_type => "User",
...
0
votes
2answers
11 views
Join tables on string value
I have two tables A and B. Each of them has a column "name" of type string. I want to get all records from joining operation where A.name is a sub string of B.name.
select * from A inner join B on ...
0
votes
0answers
12 views
Password encryption and check split across PostgreSQL and Java
We are developing a Spring MVC web application linked to a PostgreSQL database. We use Hibernate but also a number of database functions for complex operations.
One of those operations is password ...
0
votes
2answers
15 views
Errors with an easy PL/pgSQL Function
I'm trying to write my first PL/pgSQL function. For right now it is simply supposed to return the number of characters in a value that is passed to it.
CREATE OR REPLACE FUNCTION public.cents(money)
...
1
vote
1answer
27 views
How to properly define roles and privileges (Case study - PostgreSQL)?
A while ago I came here in the community to ask about the definition of a trigger for a system that I am developing. I still find myself developing the same system, and now I have doubts about roles. ...
0
votes
2answers
410 views
Gitlab can't start postgresql, redis and sidekiq after upgrade from 7.11 to 8.0
I updated from Gitlab 7.11 to 8.0. Due to lack of space on the root partition, I uninstalled Gitlab 7 via
sudo gitlab-ctl uninstall
and installed 8 via
sudo apt-get install gitlab-ce
I had a few ...
1
vote
1answer
24 views
+100
How to get weighted average grouped by a column
I have a model Company that have columns pbr, market_cap and category.
To get averages of pbr grouped by category, I can use group method.
Company.group(:category).average(:pbr)
But there is no ...
0
votes
2answers
13 views
PostgreSQL unable to parse string to date using to_date()
I have a pretty standard date as a string that I need to parse to a date type:
"2016-06-01T23:34:25+00:00"
I'm using 'YYYY-MM-DDTHH24:MI:SS' as the format mask and this query to try to get and ...
32
votes
4answers
16k views
PostgreSQL: Which Datatype should be used for Currency?
Seems like Money type is discouraged as described here
My application needs to store currency, which datatype shall I be using? Numeric, Money or FLOAT?
0
votes
3answers
23 views
How to insert first character of one column into another column?
I have a table with more than 30.000 entries and have to add a new column (zip_prefixes) containing the first digit of the a zip code (zcta).
I created the column successfully:
alter table zeta add ...
0
votes
0answers
6 views
How to use haproxy and pgbouncer together
I am using haproxy on top of two of my postgres database servers which have master slave configuration . And on top of haproxy I am using pgbouncer for connection pooling . My master and slave ...
1
vote
3answers
2k views
What is the sqlalchemy equivalent column type for 'money' and 'OID' in Postgres?
What is the sqlalchemy equivalent column type for 'money' and 'OID' column types in Postgres?
0
votes
1answer
39 views
+50
How to build area (or prevent error) from invalid linestrings
I am playing with OpenStreetMaps data. Loaded by Osmosis into Postgres 9.4 with Postgis 2.2., with linestrings geometries built for ways table.
I want to make polygons from certain relations (in ...
0
votes
0answers
6 views
Impala FDW for Postgres 9.5
I am looking for Impala Foreign Data Wrapper for Postgres 9.5. I have tried to figure out from the internet and can only have one reference to https://github.com/lapug/impala_fdw
But it seems the fdw ...
0
votes
1answer
11 views
How to exit PSQL CREATE FUNCTION screens
I'm new to writing functions in Postgres..
I don't want to exit PSQL entirely, (I know CTRL+D will do that,) but I seem to be stuck in the CREATE FUNCTION screen.
mattswheels=# CREATE OR REPLACE ...
0
votes
0answers
9 views
How to filter out Sequelize findAll results by values stored in a seperate table?
I'm working on a reservation application using node, postgresql, and sequelize. I have my properties in one model and their availability in a separate model. The availability model would be something ...
0
votes
1answer
17 views
Convert CAP_FIRST Function from MySQL to PostgreSQL
A couple of years ago, I found this CAP_FIRST function for MySQL:
CREATE FUNCTION CAP_FIRST (input VARCHAR(255))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
DECLARE len INT;
DECLARE i INT;
...
0
votes
0answers
6 views
How to test a function in PSQL?
I'm following this tutorial:
http://www.onlamp.com/pub/a/onlamp/2006/05/11/postgresql-plpgsql.html
But it fails when I get to the try command:
mattswheels=# try=% select fib(8);
ERROR: syntax ...
0
votes
0answers
17 views
PHP Get file object revision based on IDs?
I currently have an array of File objects. A File object has
fileid,
storage id,
parent (another fileobject),
name,
is_dir,
last_changed,
size and
revision.
Every time someone submits a directory, ...
23
votes
4answers
21k views
How can I move postgresql data to another directory on Ubuntu over Amazon EC2?
We've been running postgresql 8.4 for quite some time. As with any database, we are slowly reaching our threshold for space. I added another 8 GB EBS drive and mounted it to our instance and ...
0
votes
1answer
18 views
dropwizard + auto-reconnect + postgres + mybatis
I'm using latest dropwizard and Postgres database with the iBatis framework, and I noticed the application dies if the Postgres server is restarted. Is there a configuration setting or code I can add ...
0
votes
0answers
14 views
Run aggregate function in Postgresql stored procedure
I have a question about running built in aggregate functions within a stored procedure on Postgres.
I have a query where I run this function a lot:
SELECT
MAX(column_name) as max_val,
...
1
vote
1answer
23 views
+50
How do I change my column type to interval in my Rails migration?
I”m using Rails 4.2.3 with a PostGre db. I’m trying to change a column type in my table to an “interval” type, so I tried this migration
class ChangeTimeInMsInMyObjectTimes < ...
0
votes
1answer
26 views
Who is in charge of keep pg_timezone_names view updated?
In 2007 our president change it from UTC−04:00 to UTC−04:30 because children went to school to early. Now on 2016 decide the night arrive very early and revert to UTC−04:00 in an effort to save ...
3
votes
1answer
4k views
Updating a table using CASE and conditions (postgresql)
i have the tables ::
CREATE TABLE emp1
(
eid integer NOT NULL,
ename character varying(20),
sid integer,
ssid integer,
CONSTRAINT pk_eid PRIMARY KEY (eid)
);
CREATE TABLE leave_type
(
...
0
votes
1answer
15 views
PostgreSQL Finding ID's that dont exist in two other tables
I want to change the io_dl field from 1 to 0 in the io table, only if all three conditions hold true
io table pkey -> io_id
The pkey for io is not in the entire table m with the fkey named m_id
The ...
0
votes
1answer
15 views
Bitwise Operator Postgres
I have a table that contains binary values such as:
binaryid description
1 description1
2 description2
4 description3
8 description4
And I have another ...
0
votes
0answers
7 views
How do I configure Entity Framework to allow database-generate uuid for PostgreSQL (npgsql)?
I'm updating our dotnet core project to use PostgreSQL on the backend instead of Microsoft SQL Server and hit a situation with two tables that use a GUID as the data type.
ERROR
Unhandled ...
1
vote
1answer
38 views
how do I find what privileges a group has in redshift?
I'm having a peculiar problem with one group.
I have revoked all database access for this group
I have revoked all schema access for this group
I have revoked all table access for this group
I have ...
0
votes
1answer
15 views
Transferring Docker run script to compose file - postgres doesn't run?
I was previously running the Docker container for my web application below:
echo Starting postgres container...
docker run -d -p 5432:5432 --name db my_db
echo Postgres container started
echo ...
0
votes
2answers
16 views
Speed up Django & Postgres with simple JSON field
I have a very very complex model with lots of related models by FK and M2M which are also have lots of relations, etc.
So, rendering a list of such objects is a very expensive SQL operation, and i ...
1
vote
1answer
76 views
0
votes
0answers
15 views
Generate Series w/ 3 month running totals
I'm attempting to edit a query that displays month over month data based on the duration being hard coded. I'd like to create a query that provides the last three months based on the current date. ...
1
vote
0answers
41 views
Granting scripts access to postgres schema
My team has created a Postgres database and I'm trying to work on some security concerns.
Quick background: We are moving from an Oracle DB to Postgres DB. With Oracle we used multiple db to store ...
4
votes
2answers
2k views
Compiling libpq with iOS SDK 4.2
I'm trying to compile again the libpq for arm and i386 with the iOS SDK 4.2.
I did last year for SDK3.x without any problems.
Now, when I want to create the fat binary file with two files, I'm ...
0
votes
0answers
6 views
Using price contraints or a function in Postgres for “money” type?
I'm trying to make sure all the sales prices in my postgres database end in either .99 or .00.
If the price is 34.00, I'd like the database to leave it alone. If the price is something like 34.34 ...
0
votes
1answer
24 views
Postgresql equivalent of with(xlock, rowlock)
I had a query in sql server which looks something like this:
DECLARE @someIncrementalField int;
set @someIncrementalField = select max(SomeField) from SomeTable with (xlock, rowlock) where field1 = 1 ...
0
votes
0answers
4 views
postgresql8.3- Domain service account rather than local service account
I am very new to postgresql. I am installing Postgresql 8.3 as part of my test network on a windows machine which is a member of "test.abc"domain. I need to choose a domain account(Jack) rather than ...
0
votes
2answers
10 views
Enforce integrity in Postgresql when data is one join away
I am working on a db that receives data from users, regarding public transportation. The user can send many info, but what matters for enforcing integrity inside the db is:
The kind of vehicle ...
0
votes
0answers
15 views
not all arguments converted during string formatting- Python-postgresql
I'm having error code "not all arguments converted during string formatting" while running this code. it shows that the problem is in the last cur.execute() command line. Any ideas how to solve it?
...
