PostgreSQL

PostgreSQL is an object-relational database management system with an emphasis on extensibility and standards-compliance. As a database server, its primary function is to store data securely, supporting best practices, and to allow for retrieval at the request of other software applications. PostgreSQL implements the majority of the SQL2011 standard, is ACID-compliant and transactional using multiversion concurrency control (MVCC), and handles complex SQL queries using many indexing methods that are not available in other databases. It is cross-platform and runs on many operating systems including Linux, FreeBSD, OS X, Solaris, and Microsoft Windows.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hi
I'm trying to import some data into my_table using something  like this

create table my_table(
id SERIAL PRIMARY KEY,
title text,
my_date date,
-- plus other columns of types text, date and integer  
another_date date,
etc
);
COPY my_table (title,my_date,....)
FROM 'path/to/input.csv' WITH DELIMITER '|' CSV HEADER;   

Open in new window


the COPY is failing with

ERROR: date/time out of range: "10/07/2018"
SQL state: 2208
Hint: Perhaps you need a different "datestyle" setting.
Context: COPY my_table line 2 , column another_date:   "10/07/2018"

Open in new window

Is there a way of converting the date to "2018-07-10" on the way in?  I can't guarantee each date is in this format but from what  I've seen they are
if not can I set the dates to accept a broader format import the data then change it back


Opening it in openoffice calc and  setting the format of the column(s)   is first a bad idea, this could introduce errors,  and second some of the cells don't convert (I Tried a copy)


BTW:
I'm using PostgreSQL 8.4
0
Cloud Class® Course: SQL Server Core 2016
LVL 12
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Hi,
Any advice, to following issue, when accessing PostgreSQL pgAdmin?
0
Hi
I need to run the following update query on a PostgreSQL / PostGIS database

UPDATE geo_table
SET geom =ST_ConvexHull(
     ST_GeomFromText('MULTIPOINT(co_ord_string)', 4326)
);
WHERE 
Id =1234;

Open in new window


co_ord_string is a column name if I manually copy the row value into above the query runs otherwise I get

parse error – invalid geometry HINT: "MULTIPOINT(" <-- parse error at position 11 within geometry


I'm pretty sure this is because of the single quotes around  'MULTIPOINT(co_ord_string)' interprets co_ord_string as the literal string

The value of co_ord_string is a series of long lat points separated by a comma. These don’t create perfect polygons as they may contain internal points but using ST_ConvexHull a polygon is created


Could someone help with the syntax
0
Hi

I have 2 PostrgreSQL/ PostGIS databases I need to update a table in 1 DB with data in a table  in the second

geo_table in DB1

id,country_code,country_name,long,lat,geom  # point data

Open in new window


boundary.table in DB2

id,country_code,country_name,geom  # polygons

Open in new window



If both tables were in the same database I could run a select query something like (UNTESTED)

UPDATE geo.table
set g.country_code = b.country_code,g.country_name = b.country_name
FROM
geo_table g, boundary b
INNER JOIN on ST_intersect(geo_table.geom, b.geom) 

Open in new window



How can I update columns country_code,country_name in the geo_table with corresponding values in the boundary table where the 2 geometries intersect

If necessary I could copy the boundary table into DB1 but this would mean maintaining duplicate data
0
I have the Postgres SQL below in an API function. I am passing the $id and $ic variables. The $ic variable can be an array of values or a single value.
    WITH cte as (
        SELECT partnerslistori as a FROM sales.members WHERE id = '".$id."'
    )
    SELECT 
        u.id as actualid,
    	(SELECT m.company || ' (' || m.id ||')' FROM support.members m WHERE m.id = u.id) AS actualcompany,
    	u.itemname,
    	DATE_PART('day', CURRENT_TIMESTAMP - u.datein::timestamp) AS daysinstock, 
    	TRIM(u.grade)::character varying as condition, 
    	u.vstockno AS stock, 
    	u.hol AS ic, 
    	CASE WHEN u.rprice > 0 THEN 
    		u.rprice 
    	ELSE 
    		NULL 
    	END AS price, 
    	u.quantity, 
    	u.location
    FROM public.net u 
    WHERE u.holl in (".$ic.")
      AND visibledate <= now() 
      AND u.id = any(regexp_split_to_array('".$id.",'||(select a from cte), ',')); 

Open in new window


Now, I need to "aggregate" the results for a single id and contactenate the vstockno values and sum the quantity values into a single record.

For instance, if I return CSV this using the sql above:
"G06","WAP (G06)","CALIPER",370,"C","CAE17366","536-01036",1.00,1,"CY31B05"
"G06","WAP (G06)","CALIPER",770,"C","CAD16821","536-01036",1.00,1,"CALIPERCO"
"G21","WNB (G21)","CALIPER",257,"C","NBI17724","536-01036",1.00,1,"R3415"
"G21","WNB (G21)","CALIPER",128,"C","NBA18095","536-01036",1.00,1,"R2804"
"K17","GAAAP (K17)","CALIPER",270,"C","7I1581","536-01036",,1,"B5-A04"
"K17","GAAAP (K17)","CALIPER",267,"C","7I1594","536-01036",,1,"B4-B04"
"K17","GAAAP (K17)","CALIPER",78,"C","8C1210","536-01036",,1,"[UNKNOWN]"
"N40","WAC (N40)","CALIPER",147,"C","MNA18002","536-01036",1.00,1,"[UNKNOWN]"
"N49","YUPIP (N49)","CALIPER",77,"K","DS180429","536-01036",6.00,1,"UPULL-E"
"V02","CBAP (V02)","CALIPER",1408,"A","CKA1301","536-01036",40.00,1,"[UNKNOWN]"
"V02","CBAP (V02)","CALIPER",350,"A","CPA1257","536-01036",12.00,1,"BIN.360.A"
"M16","BAP (M16)","CALIPER",196,"A","8A067","536-01036",,1,"LOT-23"
"M23","BA (M23)","CALIPER",115,"A","8A065","536-01036",15.00,1,"36AU"
"M21","BUPI (M21)","CALIPER",115,"A","8A065","536-01036",15.00,1,"36AU"
"M22","BSS(M22)","CALIPER",115,"A","8A065","536-01036",15.00,1,"36AU"

Open in new window

I want it to return this instead; the stock and location columns are concatenated and the quantity is summed when there is more than one result for a particular id:
"G06","WAP (G06)","CALIPER",370,"C","CAE17366, CAD16821","536-01036",1.00,2,"CY31B05, CALIPERCO"
"G21","WNB (G21)","CALIPER",257,"C","NBI17724, NBA18095","536-01036",1.00,2,"R3415, R2804"
"K17","GAAAP (K17)","CALIPER",270,"C","7I1581, 7I1594, 8C1210","536-01036",,3,"B5-A04, B4-B04, [UNKNOWN]"
"N40","WAC (N40)","CALIPER",147,"C","MNA18002","536-01036",1.00,1,"[UNKNOWN]"
"N49","YUPIP (N49)","CALIPER",77,"K","DS180429","536-01036",6.00,1,"UPULL-E"
"V02","CBAP (V02)","CALIPER",1408,"A","CKA1301, CPA1257","536-01036",40.00,2,"[UNKNOWN], BIN.360.A"
"M16","BAP (M16)","CALIPER",196,"A","8A067","536-01036",,1,"LOT-23"
"M23","BA (M23)","CALIPER",115,"A","8A065","536-01036",15.00,1,"36AU"
"M21","BUPI (M21)","CALIPER",115,"A","8A065","536-01036",15.00,1,"36AU"
"M22","BSS(M22)","CALIPER",115,"A","8A065","536-01036",15.00,1,"36AU"

Open in new window


Can this be done in a single SQL statement?
0
My shop currently uses both Oracle 11g Single-Instance, and Oracle 12c RAC databases.  A contingent of managers, co-workers and peers are very interested in pursuing a PostGress solution.  Can you point out some of the pros/cons of both database vendors and their solutions?  By the way my application has requirements for 7/24, high availability, load balancing, and the requirement to perform Point-In-Time (PITR) when and if required.  Also database needs to be hosted in an Linux environment.
0
Hi, i have an application running in a debian server, developed in delphi 7 for 32 bits, with postgresql as database, port 5433.
To get an easy way for updating the app, all the clients (windows platform, 32, 64 bits, xp, 7, and 10), reference the executable in the server, but execute it in a local folder, where an ini file configures the link to the database.
In a lot of customers (not all with a debian server, instead, there are windows servers and ubuntu server), and in this customer, this scheme works fine.
In one PC, with Win10, i have this strange problem:
1) if i use a link, that reference the exe in the server and execute the app, it doesn't work. I get an error (internal to the app), as if i can't reach postgresql server.
2) if i copy the exe, and execute in any folder, executing in the same folder as 1), the app works fine.
It seems to be something of the firewall... i disabled it.
It seems to be something of the antivirus... i disabled it (AVG).
pgAdmin, conects to the server.
I simply can't see any other posible solution...
Can anybody help?
Thanks!
0
when I insert in table A  then it automatic copy record that just create in view table B  in postgresql
0
I am attempting to use an HTML form contained in a modal to transmit a PDF and an image to a PostgreSQL database. I don't have the database set up yet, but I've already run into an issue. My file structure has a folder for 'models', 'routes', 'views', and a main 'app.js' file. I've included the app file below as well as the files containing the issue. The form is in the 'btb.html' file (contained within the views folder) and begins on line 211 (there is a second form below it, but I haven't begun to use that one yet). The form should be posting to the URL of the 'btbRoute.js' file (contained within the routes folder) and the "router.post" there should handle the response and create an entry in the database. I've also included two files from the 'models' folder-- the 'order.js' file that defines the schema model for my database entry and the 'index.js' file that will eventually connect all my models together.

I'll eventually want to render the information that I'm inputting elsewhere on the website, so if anything that I'm doing would prevent that I would need to find a different solution (I have no idea if that's even a thing, but just in case).

Thanks in advance.
index.js
order.js
btbRoute.js
btb.html
app.js
0
Postgres allows you to create a table using inheritance. We have a design where we have 1400 tables that inherit from one main table. These tables are for each of our vendor's inventory.

When I want to query stock for a vendor, I just query the main table. When running `Explain`, the explanation says that it is going through all 1400 indexes and quite a few of the inherited tables. This causes the query to run very slowly. If I query only the vendor's stock table, I cut the query time to less than 50% of the time by querying the main table.

We have a join on another table that pulls identifiers for the vendor's partner vendors and we also want to query their stock. Example:
(This query runs in about 2 seconds)
SELECT 
	(select m2.company from sup.members m2 where m2.id = u.id) as company,  
    u.id,
	u.item, 
	DATE_PART('day', CURRENT_TIMESTAMP - u.datein::timestamp) AS daysinstock, 
	u.grade as condition, 
	u.stockno AS stocknumber, 
	u.ic, 
	CASE WHEN u.rprice > 0 THEN 
		u.rprice 
	ELSE 
		NULL 
	END AS price, 
	u.qty
FROM pub.net u 
LEFT JOIN sup.members m1 
    ON m1.id = u.id OR u.id = any(regexp_split_to_array(m1.partnerslist,',')) 
WHERE u.ic in ('01036') -- part to query
  AND m1.id = 'N40'     -- vendor to query

Open in new window


Now, this refactor makes the code run in about 800ms.
WITH cte as (
    SELECT partnerslist as a FROM sup.members WHERE id = 'N40' 
) 
SELECT 
    (select m2.company from sup.members m2 where m2.id = u.id) as company,  
    u.id,
	u.item, 
	DATE_PART('day', CURRENT_TIMESTAMP - u.datein::timestamp) AS daysinstock, 
	u.grade as condition, 
	u.stockno AS stocknumber, 
	u.ic, 
	CASE WHEN u.rprice > 0 THEN 
		u.rprice 
	ELSE 
		NULL 
	END AS price, 
	u.qty
FROM pub.net u 
WHERE u.ic in ('01036')                                                    -- part to query
  AND u.id = any(regexp_split_to_array('N40,'||(select a from cte), ','))  -- vendor to query

Open in new window


However, is there away to modify them to get the data from ONLY the specific inherited tables?

The n40_stock table has stock for the vendor with id = N40 and N40's partner vendors (partnerslist) are G01, G06, G21, K17, N49, V02, M16  so I would also want
to query the g01_stock, g06_stock, g21_stock, k17_stock, n49_stock, v02_stock, and m16_stock tables.
0
Cloud Class® Course: Python 3 Fundamentals
LVL 12
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Hi,

  We work on Postgresql 9.6, installed on Linux.  
We get SQL code requests coming from different teams, such as DDLs, DMLs, TCLs etc.
We have various environments such as DEV01, DEv02, ST01, ST02 and UAT.

My task is to deploy the code snippets coming from individual code requests, into all the above environments.
Currently i'm copying and pasting these scripts manually into each of the aboce environments.

Is there a way to automate this effort so that, i can change the db in a file and quickly execute the code?
0
Hi

I have a PostGreSQL/PostGIS database
Something like this

ID, Group_ code , point_number, date, geom
1, 1234, 1 , 2018-05-10, geom
2, 1234, 2 , 2018-05-10, geom
3, 1234, 3 , 2018-05-10, geom
4, 1234, 4 , 2018-05-10, geom
5, 1234, 5 , 2018-05-10, geom
6, 1235, 1 , 2017-05-10, geom
7, 1235, 2 , 2017-05-10, geom
8, 1235, 3 , 2017-05-10, geom
9, 1235, 4 , 2017-05-10, geom
10, 1234, 1 , 2017-05-10, geom  // WRONG

Open in new window


I can have multiple Group_ code  the same but the should have the same date

I need to find all rows where the same Group_ code has 2 or more dates
0
I want to convert column type from  to integer

ALTER TABLE billdetail ALTER COLUMN  masterid TYPE integer;

Open in new window


[Err] ERROR:  column "masterid" cannot be cast automatically to type integer
HINT:  You might need to specify "USING masterid::integer".

when I try
 ALTER TABLE billdetail USING masterid::integer; 

Open in new window


[Err] ERROR:  syntax error at or near "USING"
LINE 1: ALTER TABLE billdetail USING masterid::integer;
0
PostgreSQL  Vacuum:

•SELECT version();   --PostgreSQL 9.4.9
•Currently we have vacuum enabled with conservative setting of %20.

1.   The language on the support sites talks about the tables of the Postgress DB being held in individual files ?
 If we were to issue a 'vacuum full' command, would the recovery process re-write each table 1 at a time and 0 out the previous table ?
 How much extra space do we need to have available to issue 'vacuum full' and not run out of disk space ? (At least as much as the largest table ? or the whole D.B. ?)

2.  Can we call 'Vacuum Full' and specify a particular table, we only that table get locked ?

3.    Can 'vacuum full' be cancelled once called if it is taking too long and we need to get production back up and running on that table ?
0
PostgreSQL vacuum: if our administrator runs vacuum manually or a terabyte database to recover about %10 of the space,  how much of performance hit can we expect to see from the database while the vacuum process is running ?

Will all other processes slow to where the DB is unusable until the vacuum completes ?
No impact at all ?   Just want no surprises !

Thanks
0
Hi Experts

Could you point the vantages (disadvantages) of using PostgreSQL instead of MySQL?

Preferencialy based on your own conclusions.

Thanks in advance!
0
Hi Experts

Could you point a way to migrate a PostgreSQL database to a MySQL database?

I'm currently using Win7 environment.

Thanks in advance!
0
Hi Experts

Could you point what's  needed to use PostgreSQL from PHP?

In terms of configurations on PHP.ini f.e. and dll(s)  that must be copied in PHP folders f.e.

Thanks in advance!
0
is there any document for backup and backup plan for postgresql database on ubuntu server 16.04 using pgadmin4.
0
Cloud Class® Course: Microsoft Exchange Server
LVL 12
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

DB = Postgress

I want to select only values that are prefixed with  "d_mc". example of field values are  "d_mc###", "d_mm###", "d_ma###"

How do I do that form an a Select statement ?  All of the following just seem to return everything.

select relname, substring(relname, '\d+$') as my_Number 
     from pg_stat_user_tables
     -- where substring(relname, 'd_mc\d+$')
     -- where relname like "d_mc\D%"
    where relname like 'd_mc[0-9]+'

Open in new window


Thanks
0
Hi,
   We are on Postgres 9.6.
  I have a table called tbl_client. This has many fields and one of the fields is called end_dt.
My requirement is to insert a row into another table called tbl_audit, for any changes in tbl_client.
So, i created a trigger to capture the same.

Now, the requirement is that i don't insert a row if the change is only on the end_Dt.

Can someone give me an idea how this can be added to the condition?
0
Hi Experts,

       I am working on an web application (wagtail cms) like django-cms, which is in aws ubuntu.  Its backend is postgres, with elastic search few modules reactjs and nodejs.
The application is deployed in docker containers.  uwsgi is the upstream server for nginx.

      I get lots of errors with npm build, i am not able to run the application.  sometimes ports are not accessible. postgres is using 5432, elasticsearch is 9200. react is rendering on 9009. I get lots of dependencies error.    At what situation I have to use dockers?  sometimes I get errors in nginx configuration. could please tell what is good practice for npm build, run deploy.  

    What is the best way to architect the development and test environment?

    Will I be able access the postgresql, elastic search and nginx in linux ubuntu on aws with cms source code and contents on windows local pc?   or having the copy of postgresql, elastic search, cms contents and nginx all on a windows pc as development environment environment.   Elastic search has huge data.  and how to deploy the application on dockers in linux environment.

    What is the best way to have a development environment?  what is best test environment

   could you please tell me on how elastic search data is stored on postgres and what is network host setting in elastic search how it accessed from outside application.

    Could you please throw light on how to create development and test environment.

with …
0
Hi Experts,

How to connect to postgres, elastic search server in linux, ubuntu on AWS to  with Dango-cms or Wagtail cms from dev machine on windows

Please tell me how elastic search details are stored in database and how to connect to do a elastic search which is in postgres database.

All indexes of elastic search is stored in postgres sql.  How will i connect to postgres from windows dev machine.

And How can I access the postgres database in ubuntu aws to development machine have django-cms or wagtail cms on windows.

 ES_CONNECTION=172.17.0.1:9200,

database connection string is created with dj_database_url  the connection string is DATABASE=postgres://user:password@server/database.

Please help me,

I get the following error when I build the wagatail cms - based on python
python manage.py makemigrations

throws the below error.

django.db.utils.OperationalError: could not connect to server: Connection timed out (0x0000274C/10060)
        Is the server running on host "172.17.0.1" and accepting
        TCP/IP connections on port 5432?

I connect string sample postgres sample is postgres://test1:test2@172.17.0.1/database

When I go inside the aws machine through putty (ssh) 54.*.*.*:22

then

telnet 172.17.0.1 5432

It is connection is success full.

but not able to connect from outside aws machine from a windows pc (development machine).

With Many Thanks,
Bharath AK
0
I want to perform a text search on my Postgress table and I want the search to be case -Insensitive.

I have seen some examples of case-insensitive searches using the UPPER or LOWER command, but this would consume a lot of resources.
Doesn't Postgress have option for the SELECT cmd to ignore text case ?

Thank you.
0
Postgress SQL via VBA.

What should my connection sting look like ?

I have:
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection

    Dim strConn As String
    'strConn = "Driver={SQL Server};Server=; Database=; UID=; PWD="
    'strConn = "Driver={PostgreSQL Unicode(x64)};Server=" & srv & "; Database=" & sDB & "; UID=" & sID & "; PWD=" & sPW
    'strConn = "Driver={PostgreSQL35W};Server=" & srv & "; Database=" & sDB & "; UID=" & sID & "; PWD=" & sPW
    strConn = "Driver={PostgreSQL};Server=" & srv & "; Database=" & sDB & "; UID=" & sID & "; PWD=" & sPW

    cn.Open strConn

Open in new window

But all those options always produce the same error:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

System DNSUser DNS
0

PostgreSQL

PostgreSQL is an object-relational database management system with an emphasis on extensibility and standards-compliance. As a database server, its primary function is to store data securely, supporting best practices, and to allow for retrieval at the request of other software applications. PostgreSQL implements the majority of the SQL2011 standard, is ACID-compliant and transactional using multiversion concurrency control (MVCC), and handles complex SQL queries using many indexing methods that are not available in other databases. It is cross-platform and runs on many operating systems including Linux, FreeBSD, OS X, Solaris, and Microsoft Windows.

Top Experts In
PostgreSQL
<
Monthly
>