Postgres Tip and Tricks All Versions

Examples

• 1

  Comma seperated values of a column
  1

  SELECT 
      string_agg(<TABLE_NAME>.<COLUMN_NAME>, ',') 
  FROM 
      <SCHEMA_NAME>.<TABLE_NAME> T
  

  edited Feb 8 at 16:16

  

  

  
  edit share view discussion request improvement

  edit move example delete example
• 1

  DATEADD alternative in Postgres
  1

  • SELECT CURRENT_DATE + '1 day'::INTERVAL
  • SELECT '1999-12-11'::TIMESTAMP + '19 days'::INTERVAL
  • SELECT '1 month'::INTERVAL + '1 month 3 days'::INTERVAL

  edited Feb 8 at 16:16

  

  

  
  edit share view discussion request improvement

  edit move example delete example
• 1

  Delete duplicate rcords from postgres table
  1

  DELETE 
      FROM <SCHEMA_NAME>.<Table_NAME> 
  WHERE 
      ctid NOT IN
          (
          SELECT 
              MAX(ctid) 
          FROM 
              <SCHEMA_NAME>.<TABLE_NAME> 
          GROUP BY 
              <SCHEMA_NAME>.<TABLE_NAME>.*
          )
  ;
  

  edited Feb 8 at 16:16

  

  

  
  edit share view discussion request improvement

  edit move example delete example
• 0

  Difference between two date timestamps month wise and year wise
  0

  Monthwise difference between two dates(timestamp)

  select 
      (
          (DATE_PART('year', AgeonDate) - DATE_PART('year', tmpdate)) * 12 
          +
          (DATE_PART('month', AgeonDate) - DATE_PART('month', tmpdate))
      ) 
  from dbo."Table1"
  

  Yearwise difference between two dates(timestamp)

  select (DATE_PART('year', AgeonDate) - DATE_PART('year', tmpdate)) from dbo."Table1"
  

  edited Feb 8 at 16:16

  

  
  edit share view discussion request improvement

  edit move example delete example
• 0

  Query to Copy/Move/Transafer table data from one database to other database table with same schema
  0

  First Execute

  CREATE EXTENSION DBLINK;
  

  Then

  INSERT INTO 
      <SCHEMA_NAME>.<TABLE_NAME_1> 
  SELECT * 
  FROM 
      DBLINK(
      'HOST=<IP-ADDRESS> USER=<USERNAME> PASSWORD=<PASSWORD> DBNAME=<DATABASE>',
      'SELECT * FROM <SCHEMA_NAME>.<TABLE_NAME_2>')
      AS <TABLE_NAME>
      (
      <COLUMN_1> <DATATYPE_1>, 
      <COLUMN_1> <DATATYPE_2>, 
      <COLUMN_1> <DATATYPE_3>
      );
  

  edited Feb 8 at 16:16

  

  
  edit share view discussion request improvement

  edit move example delete example
• 0

  Update query with join between two tables alternative since Postresql does not support join in update query.
  0

      update <SCHEMA_NAME>.<TABLE_NAME_1> AS A
      SET <COLUMN_1> = True       
      FROM <SCHEMA_NAME>.<TABLE_NAME_2> AS B 
      WHERE 
          A.<COLUMN_2> = B.<COLUMN_2> AND
          A.<COLUMN_3> = B.<COLUMN_3>
  

  edited Feb 8 at 16:16

  

  
  edit share view discussion request improvement

  edit move example delete example