logo
Tags down

shadow

Postgres SQL - copy data from one column to another and if null from another one


By : Īåm Büshrã
Date : August 01 2020, 06:00 AM
Any of those help I need a help with some challenge , Perhaps COALESCE is what you want here with an update:
code :
UPDATE yourTable
SET A = COALESCE(B, C)
WHERE A IS NULL;


Share : facebook icon twitter icon

Postgres copy null into timestamp column


By : user3107119
Date : March 29 2020, 07:55 AM
This might help you It may be a bug with the order of operations in COPY, but when using the CSV format, the NULL '' option has no effect on a quoted empty string ("").
You have a couple of options to mitigate that. Either modify the data to strip "" which will allow NULL '' to force those columns as NULL, or change the timestamp data types in your temporary table to text. Then the quoted empty strings will import without issue.
code :
INSERT INTO pricelist_price_groups 
        SELECT
            pricelist_id,
            price_group_id,
            uom_id,
            quantity,
            price,
            disc_dollar,
            disc_percent,
            price_flag,
            gross_or_net,
            NULLIF(start_at,'')::timestamp with time zone,
            NULLIF(end_at,'')::timestamp with time zone
        FROM temp_pricelist_price_groups

Copy null data to not null in a column


By : olf
Date : March 29 2020, 07:55 AM
it helps some times I want to copy the data in some old columns to some newly created columns, in the same table. Some columns are bit and some are text (old) to varchar (new). The difficulty I'm running into is that the old columns have some null data in them, whereas the new columns are not null. How can I make it so that if the old data is null, then the new data gets a not null value, either 0 or '', depending. , Use coalesce():
code :
update tblProject
    set
        NewBitColumn1 = coalesce(OldBitColumn1, 0),
        NewBitColumn2 = coalesce(OldBitColumn2, 0),
        NewBitColumn3 = coalesce(OldBitColumn3, 0),

        NewVarCharColumn1 = coalesce(OldTextColumn1, ''),
        NewVarCharColumn2 = coalesce(OldTextColumn2, ''),
        NewVarCharColumn3 = coalesce(OldTextColumn3, '');

Postgres 10.5: Create Materialized View where column data may be empty/null?


By : bigl
Date : March 29 2020, 07:55 AM
it should still fix some issue I am creating a Materialized View from two tables, using the array_agg function to join strings from table_b to table_a. Essentially, column_c on table_a is a nullable array of numbers corresponding to the id column of table_b (which has only two columns, id and description). However, the Materialized View fails to include a row for any row on table_a that is null / empty on column_c. , Use LEFT JOIN:
code :
CREATE MATERIALIZED VIEW my_materialized_view
AS
SELECT
  id,
  column_a,
  column_b,
  array_agg(description) as column_c
  -- or
  -- coalesce(array_agg(description), '{}') as column_c
FROM table_a
LEFT JOIN table_b on table_b.id = any(column_c)
GROUP BY table_a.id
ORDER BY table_a.id ASC
WITH DATA;

Postgres copy error extra data after last expected column from SQL Server BCP file


By : user2320723
Date : March 29 2020, 07:55 AM
To fix this issue I am migrating a database from SQL Server 2016 hosted on Windows to Postgres 11 hosted on Debian. , As it is stated by @Tometzky,

Why postgres table shows Null in every column after executing sql insert query with json data?


By : Christian Moscosa
Date : March 29 2020, 07:55 AM
it should still fix some issue You most probably create the table without using double quotes for the column names (which is a good thing). However, json_populate_recordset() matches case sensitive, and thus the lower case column names in the table are not matched with the mixed case names in the JSON.
This:
code :
create table rf_dsgns ("Tracking_ID" int, "Constr_Zone" text, "AF_Name" text, "Address" text, "Zip_Code" text, "Latitude" numeric, "Longitude" numeric);

SELECT * 
FROM json_populate_recordset(NULL::rf_dsgns,
'[
  {
    "Tracking_ID": 2377125,
    "Constr_Zone": "Cleveland",
    "AF_Name": "PbCleveland_10236716P",       
    "Address": "4755 1/2 Rose Avenue",
    "Zip_Code": 44867,
    "Latitude": 5.8923486,
    "Longitude": -71.71052258
  },{
    "Tracking_ID": 2377126,
    "Constr_Zone": "Cleveland",
    "AF_Name": "PggClevelandCLE_25236718P",       
    "Street_Address": "4413 1/3 Clain Avenue",  
    "Zip_Code": 44225,
    "Latitude": 40.88960254,
    "Longitude": -71.20898567        
  }]');
Tracking_ID | Constr_Zone | AF_Name                   | Address              | Zip_Code | Latitude    | Longitude   
------------+-------------+---------------------------+----------------------+----------+-------------+-------------
    2377125 | Cleveland   | PbCleveland_10236716P     | 4755 1/2 Rose Avenue |    44867 |   5.8923486 | -71.71052258
    2377126 | Cleveland   | PggClevelandCLE_25236718P |                      |    44225 | 40.88960254 | -71.20898567
create table rf_dsgns (tracking_id int, constr_zone text, af_name text, address text, zip_code text, latitude numeric, longitude numeric);
insert into rf_dsgns
SELECT (j ->> 'Tracking_ID')::int, 
       j ->> 'Constr_Zone',
       j ->> 'AF_Name',
       j ->> 'Addres', 
       j ->> 'Zip_Code',
       (j ->> 'Latitude')::numeric,
       (j ->> 'longitude')::numeric
FROM json_array_elements('.... your json here ') a t(j);
Related Posts Related Posts :
  • LINQ Expression for remove duplicates
  • Operation hours redesign
  • Selecting all data related to longest period between 2 dates
  • Convert number into HH24:Mi:SS where Mi:SS is a concatenated string
  • SQL Error [8152] [22001]: String or binary data would be truncated while insertion
  • PL/SQL Loop through a list and find values which not exists in database table
  • SQLite calculate maximum number of attempts
  • How to guarantee uniqueness of field's value?
  • How to list tables/views that depend on a specific table in Redshift?
  • Reshape data in a new form so that creating new columns for all possible cases
  • How to write a PostgreSQL query that shows a breakdown based on a TimeStamp
  • Checking multiple conditions in CASE expression SQL
  • Filtering for Month for timestamp column
  • Is there any possible way to do UpperCase only "Vowel" form Column name = "last_name"?
  • Single SQL query to find null values in all columns in a data base
  • Checking to see if one or more ids is in a json string
  • Concatenating with <br/> in STUFF doesn't work
  • Split url string with multiple delimiters and into multiple rows
  • SQL subquery error, returned more than 1 value
  • Is there any rule or documentation available for the output name of an Oracle database select statement's column name?
  • Remove quantities from several rows with a certain order
  • Inserting space between the string (SQL Server)
  • In SQL, how can I select a row from a table with a particular frequency e.g. frequency = 2 or, frequency = 4?
  • Adding column with minimum date oracle sql
  • How to resolve 'ORA-00933' with 'Insert...On Duplicate Key Update' SQL Query in Pandas?
  • sorting data in oracle
  • convert a string to oracle sql timestamp Mon Aug 19 2019 08:21:48 GMT-0700 (PDT)
  • The specified field could refer to more than one table in the FROM clause of your SQL statement
  • Count data per day between two dates
  • Fetch all records from #tbl1 which are not in #tbl2 without use of 'NOT' keyword
  • sql: select query for values separated by commas in different orders
  • What does AVG(x) - 1 give us?
  • PostgreSQL comparing null values in case statement
  • Need to group records based on matching reversal in sql
  • Multi-part identifier error when adding a function to table- SQL Server
  • Sum columns from two relatable tables and group by another column (postgresql)
  • Best Practices when Choosing SQL Keys Types
  • How to allow sql athentication to run ssis packages in asp.net mvc
  • Date causing an error, not sure where to proceed
  • Field having unique and not null
  • SQL for new distinct users pers day
  • SQL Server performance on concat method
  • Query Where & Group by
  • Full outer join like functionality but merging columns
  • Display By month using select statement
  • Using case when clause in insert values
  • Transpose data in SQL with new column
  • FTS doesn't work as expected with emails with dots
  • Updating multiple rows in single column
  • Any way to cast a table with one value as a scalar type? (Integer, String, etc.)
  • Return column name if result is true
  • Display XML Schema in SQL Query Result
  • With Oracle SQL how can I find 3 days where total sum >= 150
  • Is there a way to prevent a query from setting the serial primary key?
  • how to create project id incrementally in sql based on identity column
  • SAS YYMMDD10. works but YYMMDDn10 doesn't
  • Index for join within a SQL ETL batch process
  • SQL GROUP BY clause and getting floating point 2 precision on a DATESPAN calculation with AVG
  • Count IF Function Equivalent
  • Concatenation of 2 values from same column using oracle SQL
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org