I'm having problems converting this from Oracle to PostgreSQL. I've tried using STRING_AGG, but i'm not having any success. I believe there's also an issue with REGEXP_REPLACE. Can someone help?  REGEXP_REPLACE(     LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_name),      '([^,]+)(,\1)*(,|$)',      '\1\3' )
13466
I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table:  ID   COMPANY_ID   EMPLOYEE 1    1            Anna 2    1            Bill 3    2            Carol 4    2            Dave   and I wanted to group by company_id to get something like:  COMPANY_ID   EMPLOYEE 1            Anna, Bill 2            Carol, Dave   There is a built-in function in mySQL to do this
6535
Using PostgreSQL v9.1.  I have the following tables:  CREATE TABLE foo (     id BIGSERIAL     NOT NULL UNIQUE PRIMARY KEY,     type VARCHAR(60) NOT NULL UNIQUE );  CREATE TABLE bar (     id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,     description VARCHAR(40) NOT NULL UNIQUE,     foo_id BIGINT NOT NULL REFERENCES foo ON DELETE RESTRICT );   Say the first table foo is populated like this:  INSERT INTO foo (type) VALUES     ( 'red' ),     ( 'green' ),     ( 'blue' );   Is there any way to insert rows into bar easily by referencing the foo table?  Or must I do it in two steps, first by looking up the foo type I want, and then inserting a new row into bar?  Here is an example of pseudo-code showing what I was hoping could be done:  INSERT INTO bar (description, foo_id) VALUES     ( 'testing',     SELECT id from foo WHERE type='blue' ),     ( 'another row', SELECT id from foo WHERE type='red'  );
53926
How do i create a linestring from points sort by date in PostgreSQL?  I tried:  SELECT observations.id,    ST_MakeLine(observations.geom ORDER BY observations.date) AS newgeom    FROM   observations     GROUP BY    observations.id;   This is going wrong. It returns me a linestring with same coordinates.  So, instead of a line from a to b, it gives me a to a.    What am I doing wrong?
118660
After trying apt-get install postgresql-9.4, updating, trying again etc etc. It seems I can't install it:   Couldn't find any package by regex 'postgresql-9.4'   I know there is a similar question in askubuntu but the answer there doesn't work either.  I need postgresql version 9.4 cause I'm doing a pg:backup from Heroku and I get  error if I don't have 9.4.  Thanks a lot for your help.
51428
I try to remove the artifacts which occur when running the ST_Buffer function in PostGIS PostGIS 2.5.1 (See picture). For this case I wrote a function that should loop trough every segment (row) and erase (ST_Difference) the Polygons with the smaller areas (I assume the "normal-shaped") from the bigger ones (With additional artifacts).   --CREATE TABLE line(name char(10), geom geometry); --INSERT INTO line VALUES('line','LINESTRING(0 0,30 10,40 30,70 40)');  WITH firstblocks AS(  SELECT  row_number() Over() AS id, ST_Buffer(ST_Line_Substring(the_geom, 8*n/length,   CASE     WHEN 8*(n+1) &lt; length THEN 8*(n+1)/length     ELSE 1   END),4, 'endcap=flat join=mitre mitre_limit=3.0' ) As the_geom  FROM   (SELECT    ST_LineMerge(geom) AS the_geom,   ST_Length(geom) As length   FROM line   ) AS t  CROSS JOIN generate_series(0,CEIL(length/8)::int) AS n WHERE n*8/length &lt; 1  )  --Erase the smaller geometry from the bigger one SELECT      CASE          WHEN ST_Area(the_geom) &lt;= ST_Area(lead(the_geom) OVER(PARTITION BY id ORDER BY id))         THEN ST_Difference(the_geom, lead(the_geom) OVER(PARTITION BY id ORDER BY id))         ELSE ST_Difference(lead(the_geom) OVER(PARTITION BY id ORDER BY id), the_geom)     END AS new_geom FROM firstblocks;   The code works but loading the data as a new layer in the QGIS-Database Manager throws out the error: There was an error creating the SQL layer ... Somehow I am struggling with the conditional statements, while using a moving window approach. How can I combine them successfully?
58172
This query works for me when I tried in PG admin query tool:    UPDATE bankdetails SET bank_details = 'mysore' ,"PAN"= 123,bank_acc = 456,                        "UAN"=789,tax = 'myy' WHERE  user_id = 79; INSERT INTO bankdetails (user_id,bank_details,"PAN",bank_acc,"UAN",tax)        SELECT 79,'mysore',123,456,789,'myy'        WHERE NOT EXISTS (SELECT 1 FROM bankdetails WHERE user_id=79);   Later I implemented in my code like this:  db.query('UPDATE bankdetails SET bank_details = $2 ,"PAN"= $3,bank_acc = $4,"UAN"=$5,tax = $6 WHERE user_id = $1; INSERT INTO bankdetails (user_id,bank_details,"PAN",bank_acc,"UAN",tax) SELECT $1,$2,$3,$4,$5,$6 WHERE NOT EXISTS (SELECT 1 FROM bankdetails WHERE user_id = $1);', [companyuser.rows[0]._id, data.bankDetails.bank_details, data.bankDetails.PAN, data.bankDetails.bank_acc, data.bankDetails.UAN, data.bankDetails.tax],function(err,bank) {                  if (err) return callback(new Error('error')); })   it says:     cannot insert multiple commands into a prepared statement   Can some one guide me here?
81162
In PostreSQL 8.3, I'm trying to create a view which will look just like an existing table but have different column names.  This works  CREATE OR REPLACE VIEW gfam.nice_builds AS   SELECT (family_tree.family_tree_id) as x,         family_tree.family_tree_name, family_tree.family_tree_description    FROM gfam.family_tree;   The above makes a duplicate of the family_tree table but the following attempt fails:  CREATE OR REPLACE VIEW gfam.nice_builds AS   SELECT (family_tree.family_tree_id) as x,         family_tree.family_tree_name, family_tree.family_tree_description    FROM gfam.family_tree;    ERROR:  cannot change name of view column "family_tree_id"   How can I rename columns?
123183
I have the following query that sort of works, but I find it to be kludgy and inefficient.  Is there a better way to write this query?  I know this is a bit of an odd query, else I would probably have found some advice when searching, but it is necessary for our business logic.  We've been doing this in Hibernate queries and using software to output the file but we're needing to improve performance drastically and that means using raw sql.  The output of this query is to be used to partially populate a helper table that we query to figure out which of our 40-odd product tables a particular item is contained in.  Could the following query be replicated using something like GROUP_CONCAT in reverse? Note that the second and third column must be repeated on each line.  The fields all contain the same type of data.  SELECT 'Pn', 'Mfg', 'TableId', 'SourceColumn', 'OtherData' SELECT PN1, Mfg, '26', 'PN1', OtherData FROM Table  UNION ALL SELECT PN2, Mfg, '26', 'PN2', OtherData FROM Table UNION ALL SELECT PN3, Mfg, '26', 'PN3', OtherData FROM Table UNION ALL SELECT PN4, Mfg, '26', 'PN4', OtherData  INTO OUTFILE 'XXX.dat' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' FROM Table d;   Input looks like the following table:       |  PN1  |  PN2  |  PN3    |  PN4    |  MFG  |  TableId  |  OtherData  |     -----------------------------------------------------------------------     |  asdf |  abde |  12354  |  asdfc  |  2    |  26       |  0.2456     |     |  nupo |  1354 |  null   |  null   |  2    |  26       |  1.53       |     |  ...  |  ...  |  ...    |  ...    |  ...  |  ...      |  ...        |     |  ...  |  ...  |  ...    |  ...    |  ...  |  ...      |  ...        |     |  ...  |  ...  |  ...    |  ...    |  ...  |  ...      |  ...        |   I want the output .dat file to look like this:       "Pn","Mfg","TableId","SourceColumn","OtherData"     "asdf",2,26,"PN1",0.2456     "abde",2,26,"PN2",0.2456     "12354",2,26,"PN3",0.2456     "asdfc",2,26,"PN4",0.2456     "nupo",2,26,"PN1",1.53     "1354",2,26,"PN2",1.53     ...     ...     ...   If I take the INTO OUTFILE clause out, I get an output that looks like this:       |  Pn      |  Mfg     |  TableId    |  SourceColumn  | OtherData  |     -----------------------------------------------------------------     |  "Pn"    |  {blob}  |  "TableId"  |  "PN"          |  {blob}    |     |  "asdf"  |  {blob}  |  "PN1"      |  "PN1"         |  {blob}    |     |  "abde"  |  {blob}  |  "PN2"      |  "PN2"         |  {blob}    |     |  "12354" |  {blob}  |  "PN3"      |  "PN3"         |  {blob}    |     |  "asdfc" |  {blob}  |  "PN4"      |  "PN4"         |  {blob}    |     |  "nupo"  |  {blob}  |  "PN1"      |  "PN1"         |  {blob}    |     |  "1354"  |  {blob}  |  "PN2"      |  "PN2"         |  {blob}    |     |  ...     |  ...     |  ...        |   ...          |   ...      |     |  ...     |  ...     |  ...        |   ...          |   ...      |     |  ...     |  ...     |  ...        |   ...          |   ...      |
9775
So I'm currently creating some SQL to read through the postgres (9.1) catalogs to build table definitions. However, I am encountering a problem with SERIAL/BIGSERIAL data types.  Example:  CREATE TABLE cruft.temp ( id BIGSERIAL PRIMARY KEY ); SELECT * FROM information_schema.columns WHERE table_schema='cruft' AND table_name='temp'; "db","cruft","temp","id",1,"nextval('cruft.temp_id_seq'::regclass)","NO","bigint",,,64,2,0,,,,,,,,,,,,,"db","pg_catalog","int8",,,,,"1","NO","NO",,,,,,,"NEVER",,"YES"   It gives me database name (db), schema name (cruft), table name (temp), column name (id), default value (nextval( ... )), and data type (bigint and int8 .. NOT bigserial) ... I realize that I could just check to see if the default value was a sequence - but I don't believe that would be 100% accurate since I could manually create a sequence and create a non serial column where the default value was that sequence.  Does anyone have a suggestion for how I might accomplish this? Anything other than checking the default value for a nextval(*_seq)?  Edited for SQL solution added here in case of TL;DR or new users unfamiliar with the pg_catalog:  with sequences as (   select oid, relname as sequencename from pg_class where relkind = 'S' ) select   sch.nspname as schemaname, tab.relname as tablename, col.attname as columnname, col.attnum as columnnumber, seqs.sequencename from pg_attribute col join pg_class tab on col.attrelid = tab.oid join pg_namespace sch on tab.relnamespace = sch.oid left join pg_attrdef def on tab.oid = def.adrelid and col.attnum = def.adnum left join pg_depend deps on def.oid = deps.objid and deps.deptype = 'n' left join sequences seqs on deps.refobjid = seqs.oid where sch.nspname != 'information_schema' and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_   and col.attnum &gt; 0   and seqs.sequencename is not null -- TO ONLY VIEW SERIAL/BIGSERIAL COLUMNS order by sch.nspname, tab.relname, col.attnum;
40280
I used pg_restore to load my postgres db with a dump file. I connected to my db with my user :  sudo -u arajguru psql dump  select current_user;  current_user  --------------  arajguru   Now I was able to see all the newly created tables:  dump=&gt; \dt                List of relations  Schema |       Name        | Type  |  Owner    --------+-------------------+-------+----------  public | Approvals         | table | arajguru  public | Approvers         | table | arajguru  public | Conditions        | table | arajguru  public | Entities          | table | arajguru  public | EntityDefinitions | table | arajguru  public | Projects          | table | arajguru  public | Rules             | table | arajguru  public | run_history       | table | arajguru (8 rows)   But when I try to fire a select * query, it gave me this error:  dump=&gt; select * from Approvals; ERROR:  relation "approvals" does not exist LINE 1: select * from Approvals;   What can be the reason for this error? Please help.
34447
I have two Postgres tables with different SRIDs: 4326 and 900913. I want to convert the latter to 4326. I first changed the SRID using:   Select UpdateGeometrySRID('table', 'geomcolumn', 4326).     I then transformed the reference data using:   update table set geomcolumn = ST_Transform (geomcolumn, 4326).    This seemed to work. But when I changed the SRS setting of the relevant layer in TileMill to WGS84, I get this error:      Detected out of bounds geographic extent.     Presumably the transformation didn't work but it's not clear why.
6092
How to list all constraints (Primary key, check, unique mutual exclusive, ..) of a table in PostgreSQL?
65698
What is the difference between postgres=# and postgres-#? I tried googling it and looking on the official site but could not get a hit for the symbols.
118382
I have used ogr2ogr to import various formats to PostgreSQL and have used GDAL utilities. Is there a complete Java port for these tools or are they simply wrappers calling JNI to C++ code?   I have seen some Java stuff by Googling but not sure how much are they current and kept up to date.
4667
Getting error with this code but I can't figure it out  trigger LeadConvert on Lead (after insert) {     list leadsToConvert = new list();      Database.LeadConvert lc;     string convertedStatus = '';     list convertStatusList = new list();     convertStatusList = [Select Id, MasterLabel                          from LeadStatus where IsConverted=true limit 1];     if(convertStatusList.size() &gt; 0){         convertedStatus = convertStatusList[0].MasterLabel;         }     else{              convertedStatus = 'Closed - Converted';          }      for(Lead l : trigger.New){         if(l.AutoConvert__c == true){             lc = new database.LeadConvert();             lc.setLeadId(l.ID);             lc.setDoNotCreateOpportunity(true);              lc.setConvertedStatus(convertedStatus);             leadsToConvert.add(lc);         }     }      list lcrList = new list();     lcrList = Database.convertLead(leadsToConvert); }
16262
I have a table (t0), in my Postgres DB, with data that looks something like this:  t1_id  t2_id 1      1       2      1 2      1 4      null 4      null 5      null   And I have a query to return my desired results of:  t1_id  t2_id 1      1 4      null 5      null   My query looks something like this:  (   SELECT DISTINCT ON (t2_id) t1_id, t2_id   FROM t0   WHERE t2_id IS NOT NULL ) UNION ALL (   SELECT DISTINCT ON (t1_id) t1_id, t2_id   FROM t0   WHERE t2_id IS NULL )   Is there a faster way to do an operation like this? It's not too bad, but I'm doing it in several places (with joins) and all these repeated queries seems to slow stuff down a bit. Seems like there must be a better way.  Here's the query in fiddle form:
79591
I'm quite new to querying databases, I've only been doing it for about 2 weeks in the current job that I'm in and have never touched SQL prior to this.   I'm working on trying to use alias names to pull two different sets of tables and display the fields in each table, all while ordering them by a specific field.  Here is the code I'm currently trying to use when to do that:    SELECT ars.facility_id, ars.service_code, ars.coding_system,    ars.result_label_seq, ars.display_seq, ats.facility_id,    ats.service_code, ats.coding_system, ats.display_name, ats.test_name    FROM anc_test_codes AS ats,   anc_results_seqs AS ars    WHERE ats.service_code = ars.service_code   AND ars.coding_system = ats.coding_system   AND ars.facility_id = ats.facility_id    AND ats.facility_id = 'C' AND ats.service_code = 'CBC'   ORDER BY ats.display_name   I'm sure my methods and style is totally hideous, so excuse this.  The error I'm getting is a generic Oracle SQL error: SQL Command Not Properly Ended - ORA-00933  Can anyone tell me what I'm doing wrong? Any help on this would be greatly appreciated.    Thanks!